March 10, 2024 at 4:24 am
Hi everyone
I have 2 versions of the same query -
Version 1 - there are 10 physical temp tables that the query writes to store intermediate results. I created actual tables and store temp results in them. After the query is done then the query truncates the tables. The query does not create the tables.
Version 2 - there are 10 actual temp tables that are created by the query when needed to store intermediate results. I use the 'into' command to insert the temp results into #temp.
The run time for version 1 is quite consistent. It is close to to 21 to 24 seconds.
The run time for version 2 varies quite a bit. It has been low as 15 seconds to high as 30 seconds.
I have been running the queries one after another (ie run V1 and record the time then run V2 and record the time and keep repeating this a number of times).
Is the run time for version 2 expected? I am a bit surprised by the variance. I know the computer has loads on it that can impact run time but I am running it on the same computer with max 1 second gap between runs.
The query itself is close to 1000 lines so I don't want to post it here. Instead, I am looking for feedback on things I should investigate into to see why the run time varies so much for Version 2.
Thank you
March 11, 2024 at 1:44 am
I'm thinking that the answer is somewhere in the Actual Execution Plans.
p.s. That's a major hint for what you might want to provide.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 11, 2024 at 3:32 pm
Without seeing the queries or the plans, I'm just guessing.
Possible differences between using a table and a temp table that could result in performance issues are, of course, the indexes, if any, statistics, stuff like that. But the big one is going to be contention in tempdb versus contention in your local database. Tempdb could be much more problematic. I'd monitor for blocking and waits to see what is slowing things down when you run the query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 11, 2024 at 3:32 pm
Without seeing the queries or the plans, I'm just guessing.
Possible differences between using a table and a temp table that could result in performance issues are, of course, the indexes, if any, statistics, stuff like that. But the big one is going to be contention in tempdb versus contention in your local database. Tempdb could be much more problematic. I'd monitor for blocking and waits to see what is slowing things down when you run the query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 11, 2024 at 3:59 pm
This reminds a little of a similar topic from a few weeks ago
https://www.sqlservercentral.com/forums/topic/inconsistent-query-execution-times
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply