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-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

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

Group: General Forum Members
Points: 635 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 Veteran
SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)

Group: General Forum Members
Points: 272 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 (471K reputation)SSC Guru (471K reputation)SSC Guru (471K reputation)SSC Guru (471K reputation)SSC Guru (471K reputation)SSC Guru (471K reputation)SSC Guru (471K reputation)SSC Guru (471K reputation)

Group: Administrators
Points: 471872 Visits: 20601
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 Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 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
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2334 Visits: 490
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 Veteran
SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)

Group: General Forum Members
Points: 272 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 Guru
SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)

Group: General Forum Members
Points: 104651 Visits: 8966
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 Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 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
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2334 Visits: 490
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