T-SQL: Why is my query faster, if I use a table variable?

Question

Hello,

can anybody explain me why this query takes 13 seconds:

SELECT   Table1.Location, Table2.SID, Table2.CID, Table1.VID, COUNT(*)
FROM     Table1 INNER JOIN
         Table2 AS ON Table1.TID = Table2.TID
WHERE    Table1.Last = Table2.Last 
GROUP BY Table1.Location, Table2.SID, Table2.CID, Table1.VID

And this one only 1 second:

DECLARE @Test TABLE (Location INT, SID INT, CID INT, VID INT)

INSERT INTO @Test 
SELECT   Table1.Location, Table2.SID, Table2.CID, Table1.VID
FROM     Table1 INNER JOIN
         Table2 AS ON Table1.TID = Table2.TID
WHERE    Table1.Last = Table2.Last 

SELECT   Location, SID, CID, VID, COUNT(*)
FROM     @Test
GROUP BY Location, SID, CID, VID

When I remove the GROUP BY from the first query it needs only 1 second too. I also try to write a subselect and group the result, but it takes 13 seconds too. I don't unterstand this.

Answer 1

The GROUP BY may perform better if you have an INDEX on each of the columns in the GROUP BY (either one each, or a combined single index of all the columns)

The reason your temporary version works better is probably because the GROUP BY is being performed on a much smaller subset of the data and therefore it is fast even without the index.

Your temp table method is by no means the wrong way of doing it. It is one of those situations where you weigh up the pro's and con's of each method. An index on the main table may slow up your inserts / updates and increase your database size. The temp table however may not perform adequately once the data size increases over time.

Answer 2

Compare the execution plans for the two queries.

Answer 3

Could be the that in first query you group and count on larger query result than in second query where you work with already smaller dataset. Could be indexing problem. Once you fix it don't forget to re-check with larger result set because "worser" query can perform better with larger datasets.