Saturday, June 11, 2011

My SQL Query Questions

Last Friday I posted a SQL query question to SO. It was about get records between boundaries. Quickly I got several good answers.

I noticed one comment about performance issue by Piotr Auguscik:

U can use max/min to get value u need. Order by +top 1 isnt best way to get max value, what i can see in ur querys. To sort n items its O(n to power 2), getting max should be only O(n)


This is very interesting comment. I think about it, and I realize it is true. To sort a list of n items, it will take about n power of 2 by using bubble sort algorithm; while just to find out the max, it will do one loop of n at most. This is a very simple computer science concept. However, if it is an index column in the query table, I think that it should have no difference between TOP 1 + ORDER BY and MAX + GROUP BY.

Today I raised another question related to this issue again to SO. I got a very nice explanation and a tip to optimize the query and tip on how to turn on statistics on IO and TIME.

Here are queries:

SELECT
dv1.timestamp, dv1.value
FROM
myTable AS dv1
WHERE
dv1.value = @value
dv1.timestamp
BETWEEN (
SELECT TOP 1 dv2.timestamp
FROM myTable AS dv2
WHERE dv2.value = @value AND
dv2.timestamp < '@START_DATE'
ORDER BY dv2.timestamp DESC
)
AND (
SELECT TOP 1 dv3.timestamp
FROM myTable AS dv3
WHERE dv3.value = @value AND
dv3.timestamp < '@END_DATE'
ORDER BY dv3.timestamp ASC
)
ORDER BY dv1.timestamp

-- alternative way

SELECT
dv1.timestamp, dv1.value
FROM
myTable AS dv1
WHERE
dv1.value = @value
dv1.timestamp
BETWEEN (
SELECT MAX(dv2.timestamp)
FROM myTable AS dv2
WHERE dv2.value = @value
GROUP BY dv2.value
)
AND (
SELECT MIN(dv3.timestamp)
FROM myTable AS dv3
WHERE dv3.value = @value
GROUP BY dv3.value
)
ORDER BY dv1.timestamp

0 comments: