How can I optimize this SQL query?

Question

Right now I have this SQL query which is valid but always times out:

SELECT 
(
SELECT (MAX(WP_ODOMETER) - MIN(WP_ODOMETER)) / DATEDIFF(DAY, MIN(WP_DATETIME), MAX(WP_DATETIME))
FROM WAYPOINTS
WHERE WP_DATETIME BETWEEN DATEADD(DAY,-14,GETDATE()) AND GETDATE()
AND WP_VTDID = 'L088'
)
AS MAXD, 
(
SELECT MAX(WP_ODOMETER) 
FROM WAYPOINTS
WHERE WP_DATETIME BETWEEN DATEADD(DAY,-14,GETDATE()) AND GETDATE()
AND WP_VTDID = 'L088'
)
AS MD

I want to create a view based on the above SQL query. Something like this:

SELECT L_VTDID
(
SELECT (MAX(WP_ODOMETER) - MIN(WP_ODOMETER)) / DATEDIFF(DAY, MIN(WP_DATETIME), MAX(WP_DATETIME))
FROM WAYPOINTS
WHERE WP_DATETIME BETWEEN DATEADD(DAY,-14,GETDATE()) AND GETDATE()
AND WP_VTDID = LOCOMOTIVES.L_VTDID
)
AS MAXD, 
(
SELECT MAX(WP_ODOMETER) 
FROM WAYPOINTS
WHERE WP_DATETIME BETWEEN DATEADD(DAY,-14,GETDATE()) AND GETDATE()
AND WP_VTDID = LOCOMOTIVES.L_VTDID
)
AS MD
FROM LOCOMOTIVES

Answer 1

Is WAYPOINTS indexed on WP_DATETIME and WP_VTDID?

Answer 2

Since they have the same where clause, you could combine them:

SELECT 
MAX(WP_ODOMETER),
(MAX(WP_ODOMETER) - MIN(WP_ODOMETER)) / 
DATEDIFF(DAY, MIN(WP_DATETIME), MAX(WP_DATETIME))
FROM WAYPOINTS
WHERE WP_DATETIME BETWEEN DATEADD(DAY,-14,GETDATE()) AND GETDATE()
AND WP_VTDID = 'L088'

An index on WP_VTDID, WP_DATETIME can speed this up. You could also include WP_ODOMETER in the index, to save the bookmark lookup from the index to the table itself.

If the timeout occurs because someone else is locking the table, try to change the from statement to:

FROM WAYPOINTS (NOLOCK)

If the query runs fine with NOLOCK, another process is using the table and preventing your query from locking rows.

Answer 3

Believe it or not, client side languages are actually quite capable of doing subtraction and division, etc. So, were it up to me, I would simplify the query (especially since this version gives trouble):

SELECT MAX(WP_ODOMETER) AS MAX_ODO,
       MIN(WP_ODOMETER) AS MIN_ODO, 
       MIN(WP_DATETIME) AS MIN_DATE,
       MAX(WP_DATETIME) AS MAX_DARE
  FROM WAYPOINTS
 WHERE WP_DATETIME BETWEEN DATEADD(DAY,-14,GETDATE()) AND GETDATE()
   AND WP_VTDID = 'L088'

If there is a major problem with handling date computations on the client side, then I'd concede that you might need to generate the difference between MAX_DATE and MIN_DATE on the server, but it might be better to get a host language that allows you to do date computations.