Speeding up database access - part 4 Fixing expensive queries

  • mperdeck

    SSC Enthusiast

    Points: 144

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

  • SQL Adventurar

    SSC Eights!

    Points: 913

    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

    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: 716659

    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

    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: 3782

    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

    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

    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

    @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: 3782

    Interesting...Can you show the IO stats?

  • christian.stahl

    SSC Enthusiast

    Points: 103

    @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:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 2 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    count

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 2 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    top

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

  • Andy DBA

    SSCommitted

    Points: 1776

    @ HEB1014 and christian.stahl

    The author's example query is different than the query you're discussing.

    The author is suggesting:

    IF EXISTS(SELECT * FROM dbo.Book) print 'Records found'

    There is no count(*) in the author's EXISTS subquery. In fact, if the ASSIGNMENT table exists,if exists (select count(*) from ASSIGNMENT a ) will always be TRUE even if the table is empty!

    Personally I've always used:

    IF EXISTS(SELECT 1 from ...

    instead of:

    IF EXISTS(SELECT * from ...

    because I don't want to retrieve any data, I just want to know if the record exists. But, I just compared execution plans and to my surprise, they're identical! I'm guessing the optimizer is smart enough to recognize I'm not doing anything with the retrieved data so it doesn't retrieve it.

    I was thinking that christian.stahl's

    IF EXISTS(SELECT TOP 1 ID from ...

    might be slower because SQL Server would have to do a sort in order to determine what the TOP 1 record is, but the execution plan is the same as the plan for the other two queries! As a last resort 😉 I referenced BOL and found:

    If the query includes an ORDER BY clause, the first expression rows, or expression percent of rows, ordered by the ORDER BY clause are returned. If the query has no ORDER BY clause, the order of the rows is arbitrary.

    So, I'm guessing the execution plan is the same because no sort is being done.

    Great series BTW! I'm looking forward to the rest of the articles.

  • Nicole Bowman

    SSCommitted

    Points: 1555

    Thanks for a great series Matt. I am getting a lot out of it, so much so that I have bought your book. So far it is very easy to read and understand.

    Cheers,

    Nicole Bowman

    Nothing is forever.

  • Andre Guerreiro

    SSCertifiable

    Points: 7319

    christian.stahl (8/30/2011)


    @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

    COUNT will always return a number and never be NULL so it will always exist in your result above.

    I remember reading in the T-SQL Programming book that any columns declared inside the EXISTS block will be ignored so it doesn't matter if you do this:

    IF EXISTS(SELECT * FROM A)

    or this:

    IF EXISTS(SELECT ColumnA, ColumnB FROM A)

    they will be the same.

    I'm not sure about the TOP 1 having any differences though.

    Best regards,

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • sankalpa

    SSC Enthusiast

    Points: 122

    SmallDateTime data type also help when optimizing the datatype

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

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