SELECT MAX() too slow - any alternatives?

Question

I've inherited a SQL Server based application and it has a stored procedure that contains the following, but it hits timeout. I believe I've isolated the issue to the SELECT MAX() part, but I can't figure out how to use alternatives, such as ROW_NUMBER() OVER( PARTITION BY...

Anyone got any ideas?

Here's the "offending" code:

SELECT BData.*, B.* 
FROM BData
INNER JOIN
(
    SELECT MAX( BData.StatusTime ) AS MaxDate, BData.BID
    FROM BData
    GROUP BY BData.BID
) qryMaxDates
ON ( BData.BID = qryMaxDates.BID ) AND ( BData.StatusTime = qryMaxDates.MaxDate )
INNER JOIN BItems B ON B.InternalID = qryMaxDates.BID
WHERE  B.ICID = 2
ORDER BY BData.StatusTime DESC;

Thanks in advance.

Answer 1

SQL performance problems are seldom addressed by rewriting the query. The compiler already know how to rewrite it anyway. The problem is always indexing. To get MAX(StatusTime ) ... GROUP BY BID efficiently, you need an index on BData(BID, StatusTime). For efficient seek of WHERE B.ICID = 2 you need an index on BItems.ICID.

The query could also be, probably, expressed as a correlated APPLY, because it seems that what is what's really desired:

SELECT D.*, B.* 
FROM BItems B
CROSS APPLY
(
    SELECT TOP(1) *
    FROM BData
    WHERE B.InternalID = BData.BID
    ORDER BY StatusTime DESC
) AS D
WHERE  B.ICID = 2
ORDER BY D.StatusTime DESC;

SQL Fiddle.

This is not semantically the same query as OP, the OP would return multiple rows on StatusTime collision, I just have a guess though that this is what is desired ('the most recent BData for this BItem').