SQL MAX() function is very slow, is there a better method?

I have a SQL database and ASP.NET web application and most of my queries involve the SQL max function.

For example, the below query takes between approx. 36 seconds to execute (when using the profiler) on both the ASP.NET website and the SSMS.

SELECT MAX(CONVERT(FLOAT,ISNULL(Runhrs,Runho))) - 
       MIN(CONVERT(FLOAT,ISNULL(Runhrs,Runho))) AS ACTUALHOURSRUN 
FROM REPORTINGSYSTEM.DBO.HL_LOGS 
WHERE ID_LOCATION = @ID_LOCATION AND 
       CONVERT(VARCHAR,TIME_STAMP,102) 
       BETWEEN @STARTDATE AND @ENDDATE

The table in question has approx. 5,000,000 records and 45 columns.

What is the best/fastest/most efficient way of executing the query to reduce the execution time?

Thanks in advance...

Solution

There are several things that you need to do:

Make sure that the columns that you search are indexed - You need an index on ID_LOCATION and TIME_STAMP (if you have other queries that query by date across locations or by locations across dates, you may consider defining separate indexes; otherwise, a single combined index will work).
Stop converting the timestamp to a string - use the native data type for @STARTDATE and @ENDDATE in your query, or replace the condition with TIME_STAMP between CONVERT(datetime,@STARTDATE) and CONVERT(datetime,@ENDDATE)

These two changes should make your query faster, especially the second one: currently, CONVERT(VARCHAR,TIME_STAMP,102) forces the query optimizer into a full scan on everything that matches your location, or even a full table scan if there is no index on ID_LOCATION. An indexed search should bring the number of records down to acceptable levels.

The final item to take on is CONVERT(FLOAT,ISNULL(Runhrs, Runho)): if the query speed after the first two modifications remains insufficient, change MAX(CONVERT(FLOAT,ISNULL(Runhrs,Runho))) to CONVERT(FLOAT, MAX(ISNULL(Runhrs,Runho))), and do the same change for the MIN. This may or may not work, depending on the types of Runhrs and Runho.