0

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!!

1
  • 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, 2013 at 9:55

2 Answers 2

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.

5
  • 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, 2013 at 11:36
  • You could where metadata in (select metadata from metadata where (metadata % 1000) = 100) Mar 7, 2013 at 11:51
  • Thank you, I tried that but, for some reason, the real execution plan and the performance numbers are similar to which I showed for the first (bad) query. It looks like SQL Server is firstly looking into "twg" table and querying afterwards "metadata" table in order to apply the filter. If it was done the other way around, it could take profit from the "metadata" clustered index....right?
    – Hauri
    Mar 7, 2013 at 12:17
  • That is correct. You could try using a CTE... WITH meta_data_vals AS (select metadata from metadata where (metadata % 1000) = 100) Mar 7, 2013 at 12:23
  • It works but numbers are exactly the same, as well as the real execution plan. I only want the SQL engine to compute first the metadata filter in its table (which results in 0 seconds of time and 4 logical reads) and, afterwards, use those results as filter in "twg" table (which is done in 20 logical reads, as said above). Anyway, thank you for your suggestion....
    – Hauri
    Mar 8, 2013 at 11:43
1

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.

1
  • 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, 2013 at 11:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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