Any way to optimize this query?

Code:

---

SELECT  LineActivityLog.*
FROM  LineActivityLog
WHERE  LineActivityLog.LineActivityLogID =
(SELECT DISTINCT MAX(LineActivityLog.LineActivityLogID)
FROM LineActivityLog     
WHERE LineActivityLog.LineID = @LineID AND LineActivityLog.EndTime = 
(SELECT DISTINCT MAX(LineActivityLog.EndTime)
FROM LineActivityLog
WHERE LineActivityLog.LineID = @LineID))

---

It eats a lot of CPU according to our SQL Server logs, and it is run quite frequent. I was thinking maybe we can use CTE instead of distinct max?

we use sql server 2005

/S

Did you try something like this:

sql Code:

Select LAL.* 
From LineActivityLog LAL 
INNER JOIN ( SELECT MAX(LAL1.LineActivityLogID) 
                       FROM LineActivityLog LAL1     
                       WHERE LAL1.LineID = @LineID 
                        AND LAL1.EndTime = ( SELECT MAX (LAL2.EndTime)
                                                       FROM LineActivityLog LAL2  
                                                       WHERE LAL2.LineID = @LineID)
                  ) x
    on LAL.LineActivityLogID = x.LineActivityLogID

Also have you looked at the query plan? What does it show? Have you traced the query? Where is the time being spent?

Here you go:

Code:

---

; WITH
aa AS (
        SELECT DISTINCT MAX(lal.EndTime) AS MAX_EndTime
        FROM LineActivityLog AS lal
        WHERE lal.LineID = @LineID
)
, bb AS (
        SELECT DISTINCT MAX(lal.LineActivityLogID) AS MAX_LineActivityLogID
        FROM LineActivityLog AS lal
                INNER JOIN aa ON lal.EndTime = aa.MAX_EndTime
        WHERE lal.LineID = @LineID
)
SELECT lal.*
FROM LineActivityLog AS lal
        INNER JOIN bb ON lal.LineActivityLogID = bb.MAX_LineActivityLogID

---

If you don't have an index on "EndTime", try to put one, and see if there is any speed improvement.

Quote:

---

Originally Posted by CVMichael

Here you go:

Code:

---

; WITH
aa AS (
        SELECT DISTINCT MAX(lal.EndTime) AS MAX_EndTime
        FROM LineActivityLog AS lal
        WHERE lal.LineID = @LineID
)
, bb AS (
        SELECT DISTINCT MAX(lal.LineActivityLogID) AS MAX_LineActivityLogID
        FROM LineActivityLog AS lal
                INNER JOIN aa ON lal.EndTime = aa.MAX_EndTime
        WHERE lal.LineID = @LineID
)
SELECT lal.*
FROM LineActivityLog AS lal
        INNER JOIN bb ON lal.LineActivityLogID = bb.MAX_LineActivityLogID

---

If you don't have an index on "EndTime", try to put one, and see if there is any speed improvement.

---

Would an index on "endTime" really help since it is in a function. I'm thinking that makes it non-sargable. Just curious what others think.

EndTime is a field in the table from the looks of it...

[edit] Or you mean a "Computed Column" ?

You can create an index on those too: http://msdn.microsoft.com/en-us/library/ms189292.aspx

Quote:

---

Originally Posted by CVMichael

EndTime is a field in the table from the looks of it...

[edit] Or you mean a "Computed Column" ?

You can create an index on those too: http://msdn.microsoft.com/en-us/library/ms189292.aspx

---

I not an expert but I thought using an index column in a function made it ignore the index or the look up non-sargable.

http://stackoverflow.com/questions/7...ement-sargable

I didn't look at your link because I didn't mean a computed column.

I understand that functions are not sargable, and I know that... but the only function I see in the OP's code is the MAX, and that does not interfere with the index since the index is only used in the JOIN "INNER JOIN aa ON lal.EndTime = aa.MAX_EndTime"

Quote:

---

Originally Posted by CVMichael

I understand that functions are not sargable, and I know that... but the only function I see in the OP's code is the MAX, and that does not interfere with the index since the index is only used in the JOIN "INNER JOIN aa ON lal.EndTime = aa.MAX_EndTime"

---

Gotcha...Thanks!