Speeding up database access - part 4 Fixing expensive queries

Question

Speeding up database access - part 4 Fixing expensive queries

mperdeck

SSC Enthusiast

Points: 144
More actions
August 29, 2011 at 9:39 pm

#242443

Comments posted to this topic are about the item Speeding up database access - part 4 Fixing expensive queries

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

SQL Adventurar SSC Eights! Points: 973 More actions · Answer 1

Hi

In another blog post by Gail Shaw (a Trio Of Table Variable[/url]) 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 Old Hand Points: 346 More actions · Answer 2

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 - SSC Editor SSC Guru Points: 734418 More actions · Answer 3

While both are created in tempdb, it is possible that both table variables and temp tables are memory resident as well

christian.stahl SSC Enthusiast Points: 103 More actions · Answer 4

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 Hall of Fame Points: 3790 More actions · Answer 5

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 Old Hand Points: 346 More actions · Answer 6

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 SSC Guru Points: 134017 More actions · Answer 7

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 SSC Enthusiast Points: 103 More actions · Answer 8

@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 Hall of Fame Points: 3790 More actions · Answer 9

heb1014

Hall of Fame

Points: 3790

August 30, 2011 at 8:16 am

#1375027

Interesting...Can you show the IO stats?

christian.stahl SSC Enthusiast Points: 103 More actions · Answer 10

@heb1014 (Great Reference btw!) 🙂

I haven't used the SET STATISTICS much, so I hope I did it correctly. Running it with TIME made sense, but I don't understand what happened when I ran this with IO:

[font="Courier New"]SET STATISTICS IO ON

go

if exists (select count(*) from ASSIGNMENT a )

PRINT 'count'

GO

if exists (select top 1 ID from ASSIGNMENT a)

print 'top'

GO

SET STATISTICS IO OFF[/font]

Results in:

count

Table 'ASSIGNMENT'. 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.

top

Nothing displayed for "count", even when I run it on its own?

Here are the results for TIME:

SQL Server parse and compile time: