SELECT MAX() too slow - any alternatives?

max, sql, sql-server, timeout

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.

Problem

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.

Original source