How can I optimize this SQL query?
Right now I have this SQL query which is valid but always fails:
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 that:
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
a source to share
Since they have the same where clause, you can 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'
WP_VTDID index, WP_DATETIME can speed this up. You can also include WP_ODOMETER in the index to store bookmark searches from the index to the table.
If a timeout occurs because someone is locking the table, try changing the from statement to:
FROM WAYPOINTS (NOLOCK)
If the query runs fine with NOLOCK, another process uses the table and prevents your query from locking rows.
a source to share
Believe it or not, client-side languages are actually quite capable of doing subtraction and division, etc. So, if this was the case for me, I would simplify the query (especially since this version gives problems):
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 big problem with computing the processing date on the client side, I would agree 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 the computation by date.
a source to share