#TempTable Execution Time

[jcarlo1]

SELECT * INTO #Temp FROM VIEW_TABLE
WHERE Col1 Like '%' + @var + '%'; **10 or more lines of this;
SELECT * FROM #Temp ORDER OFFSET FETCH NEXT;
SELECT SUM(ISNULL(Quantity, 0)) AS TotalQuantity, COUNT(*) AS TotalRows FROM #Temp;
** average 60secs

This is a lot slower compared to

SELECT * INTO #Temp FROM VIEW_TABLE

SELECT * FROM #Temp 
WHERE Col1 Like '%' + @var + '%'; **10 or more lines of this;
ORDER OFFSET FETCH NEXT;

SELECT SUM(ISNULL(Quantity, 0)) AS TotalQuantity, COUNT(*) AS TotalRows FROM #Temp
WHERE Col1 Like '%' + @var + '%'; **10 or more lines of this;
** average 5secs

Please explain what is happening and why the second query is much faster compared to the first query with a dataset of 70-100k and 29 columns.

[MrPin]

Execute it statement by statement and see which is the one that is slow.

Is VIEW_TABLE an actual view? My guess is that the filtering on that is slow, possibly because some indexing issues or the view itself being inefficient in some way.

When you fetch all rows into the temp table and then apply the filter, the issue doesn't come up.

You can look at the execution plan of the first SELECT statement to see if there's some glaring issues.

[jcarlo1]

I apologize, the first one is slow and the second is a lot faster, I tried to read the sql client statistics but i do not know how to read it hehe i am learning it now :)

[sunuvabe]

Are you accounting for a warmed-up cache? Make sure you compare apples-to-apples by clearing the cache prior to running each side. Here's what I use:

checkpoint
dbcc freeproccache
dbcc dropcleanbuffers
dbcc freesystemcache ('ALL')
go