Search code examples
sqlsql-servertimeoutmax

SELECT MAX() too slow - any alternatives?

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.

Solution

  • [UNSOLVED] But I've moved on!

    Thanks to everyone who provided answers / suggestions. Unfortunately I couldn't get any further with this, so have given-up trying for now.

    It looks like the best solution is to re-write the application to UPDATE the latest data into into a different table, that way it's a really quick and simple SELECT to latest readings.

    Thanks again for the suggestions.