Any way to optimize this query?

[MrNorth]

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

[GaryMazzone]

Did you try something like this:

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?

[CVMichael]

Here you go:

; 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.

[TysonLPrice]

Here you go:

; 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.

[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

[TysonLPrice]

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.

[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"

[TysonLPrice]

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!