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