CODEWITHSUNDEEP
sqlsql-server-2005t-sql
Dels
Dels

Reputation: 2425

How can I optimize this SQL query?

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

Upvotes: 0

Views: 471

Answers (3)

Jonathan Leffler
Jonathan Leffler

Reputation: 753475

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.

Upvotes: 3

Andomar
Andomar

Reputation: 238048

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.

Upvotes: 1

lc.
lc.

Reputation: 116448

Is WAYPOINTS indexed on WP_DATETIME and WP_VTDID?

Upvotes: 0

Related Questions