SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Speeding up database access - part 4 Fixing expensive queries


Speeding up database access - part 4 Fixing expensive queries

Author
Message
mperdeck
mperdeck
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 37
Comments posted to this topic are about the item Speeding up database access - part 4 Fixing expensive queries
SQL Adventurar
SQL Adventurar
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 111
Hi

In another blog post by Gail Shaw (a Trio Of Table Variable) I read that table variables are actually created in tempDB. You mention that SQL is less likely to do so. Are there then circumstances where SQL would/not create it in tempDB?

Thanks
brendanc
brendanc
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 224
Table variables and Temp tables are both stored in tempDB there is not difference. You can use the following DMV sys.dm_db_session_space_usage to show you what pages are allocated in TEMPDB.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (178K reputation)SSC Guru (178K reputation)SSC Guru (178K reputation)SSC Guru (178K reputation)SSC Guru (178K reputation)SSC Guru (178K reputation)SSC Guru (178K reputation)SSC Guru (178K reputation)

Group: Administrators
Points: 178272 Visits: 19497
While both are created in tempdb, it is possible that both table variables and temp tables are memory resident as well

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
christian.stahl
christian.stahl
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 27
I agree with what you wrote regarding the use of EXISTS being more efficient than using COUNT.

To take it one notch higher, I always use

EXISTS (select TOP 1 PrimaryKeyField FROM myTable )

to use as little data as necessary for the test.
heb1014
heb1014
SSC Eights!
SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)

Group: General Forum Members
Points: 964 Visits: 486
I just tried the "select top 1" vs "select count(*)" and indeed "select top 1" produced poorer stats.


if exists (select count(*) from DimCandidates )
print 'here'

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
here

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.


if exists (select top 1 CandidateKey from DimCandidates )
print 'here'

SQL Server parse and compile time:
CPU time = 3 ms, elapsed time = 3 ms.
Table 'DimCandidates'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
here

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.



brendanc
brendanc
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 224
Hi

Everything resides in memory for SQL to work with it. Data is read from disk into memory and then SQL works with it. The point I was making is that there is a misconception between developers that temporay tables are purely memory residant, which is not the case. So yes you are correct thet they both do reside in memory and they both reside in TEMPDB there is no disctinction as to what was made in the article
TheSQLGuru
TheSQLGuru
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40346 Visits: 8713
You are absolutely wrong in your recommendation to use table vars instead of temp tables. I recommend the exact opposite in almost all cases except VERY high-volume execution environments where recompilation time is shown to be an issue. I can show you a table var query where a SINGLE row gets you a suboptimal plan but the temp table gets you the correct plan.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
christian.stahl
christian.stahl
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 27
@heb1014:

When I ran your test against a table with 18,399 rows I got these results:
if exists (select count(*) from A )
print 'here' :
Elapsed time: 00:00:00.2496004

if exists (select top 1 ID from A )
print 'here':
Elapsed time: 00:00:00.0312001
heb1014
heb1014
SSC Eights!
SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)

Group: General Forum Members
Points: 964 Visits: 486
Interesting...Can you show the IO stats?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search