T-SQL: Why is my query faster if I use a table variable?
can someone explain to me why this request 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 is 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 only takes 1 second. I am also trying to write a subplot and group the result, but it takes 13 seconds. I do not understand this.
source to share
GROUP BY may work better if you have an INDEX on each of the columns in the GROUP BY (each one by one or a combined single index of all columns)
The reason your temporary version performs better is probably because the GROUP BY is performed on a much smaller subset of the data, and therefore it is fast even without an index.
Your temp table method is by no means the wrong way to do it. This is one of those situations where you weigh the pros and cons of each method. The main table index can slow down your inserts / updates and increase the size of your database. However, the temporary table may not work as expected after increasing the size of the data over time.
source to share
It may be that in the first query you are grouping and expecting a larger query result than in the second query, where you are working with a smaller dataset. Could be an indexing issue. After you fix this, remember to recheck with a larger result set, because a "harder" query may perform better on larger data sets.
source to share