Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to understand why is there so much difference in execution time and CPU usage between two simple queries that only differ in a computation. The queries are the following:

SELECT m.Metadata, COUNT(*) As Count
FROM TWG10MinData twg, Metadata m
WHERE twg.metadata = m.Metadata and (m.Metadata % 1000) = 100
GROUP BY m.Metadata
ORDER BY m.Metadata

Some statistics for this query are: 67000 logical reads, 3000 ms CPU time, 800 ms time elapsed.

SELECT Metadata, COUNT(*) As Count 
FROM TWG10MinData twg 
WHERE Metadata IN (1100,2100,3100,4100) 
GROUP BY Metadata 
ORDER BY Metadata

Some statistics for this query are: 20 logical reads, 0 ms CPU time, 0 ms time elapsed.

Table "twg" has a nonclustered index on "Metadata" field (its primary key fields don't appear on the query). Table "Metadata", has its field "Metadata" as primary key and therefore it has a clustered index on it. As you can see, the only difference is specifying the concrete values that, at the moment, result from this computation "(Metadata % 1000) = 100". Shouldn't SQL engine compute first the valid values for Metadata and then apply the filter? Is this so much time consuming to cause such a big difference in their performance numbers?

Thanks in advance!!

share|improve this question
1  
"Shouldn't SQL engine compute first the valid values for Metadata and then apply the filter?" -- How would it do that? There are an infinite number of numbers that can match - it has to evaluate every single row –  Phil Mar 7 '13 at 9:55

2 Answers 2

I am willing to bet the issue is that the first does a scan of the table, while the second can look things up from an index. This happens all the time. The reason, as Phil has said, is that there are an infinite number of possibilities.

If you want to make the first approach work better with more flexibility you need to use a recursive query structure. Something like:

WITH RECURSIVE sparse_scan AS (
    SELECT m.Metadata, COUNT(*) As Count
      FROM TWG10MinData twg, Metadata m
     WHERE twg.metadata = m.Metadata and m.Metadata = 1100
  GROUP BY m.Metadata
 UNION ALL
    SELECT m.Metadata, COUNT(*) As Count
      FROM TWG10MinData twg, Metadata m
      JOIN sparse_scan s ON (m.Metadata = s.Metadata + 1000)
     WHERE twg.metadata = m.Metadata 
  GROUP BY m.Metadata
)
SELECT * FROM sparse_scan order by m.Metadata;

This works differently, running the query for 1000, 2100, 3100, 4100, etc, until a row is not returned. You can be a little fancier to skip cases where a row does not exist but the next one does, but the basic approach is something I use frequently, albeit on PostgreSQL.

share|improve this answer
    
Does Postgres need/allow the ORDER BY inside the cte? –  ypercube Mar 7 '13 at 10:19
    
ypercube, it is supported, though the ordering will not be guaranteed after any joins. –  Chris Travers Mar 7 '13 at 10:31
    
SQL Server tells me that GROUP BY clause is not accepted within a recursive query... Anyway, isn't there a cleaner way of doing this? What I thought before but I don't know how to do it is the following: getting the Metadata values is quite quick and efficient if I do select Metadata from Metadata where (Metadata % 1000) = 100, so it should be pretty easy for the SQL engine if I could inject the result from this query into the Metadata IN (query_result) filter in the second query. Is it possible to do this? –  Hauri Mar 7 '13 at 11:36
    
You could where metadata in (select metadata from metadata where (metadata % 1000) = 100) –  Chris Travers Mar 7 '13 at 11:51
    
Performance-wise the subselect will be faster where the metadata table is small. If it gets very large, a recursive CTE is necessary. –  Chris Travers Mar 7 '13 at 11:52

These queries differ significantly and it is no wonder they have a time difference. The first has a join (please stop using the SQL antipattern of an implicit join) which is certainly going to be more time consuming than just querying one table. And then you havea calcluastion that you do not have inteh second query. Further if the indexing is bad on the join fields (FKS do not automatically get indexed) then the query will be slower still.

share|improve this answer
    
Yes, I know that performing a join is more time consuming than just a simple filter. I just try to apply common sense: I think (and I'm probably misunderstanding something...) that there should be a way (as explained in answer above) to perform the query in two different phases, so both of them are efficient globally. –  Hauri Mar 8 '13 at 11:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.