Post reply

Query Performance - Table Temporary

  • April 5, 2012 at 10:53 am

    #259492

    Hello,

    I created a temporary table in a SP :

    create table #dirtyDocInterventions (ptInterventionId uniqueidentifier,

    conceptId uniqueidentifier,

    dataFocusId uniqueidentifier,

    chartTime dateTime,

    storeTime dateTime,

    ptDocumentId uniqueidentifier,

    isDemographic bit)

    CREATE NONCLUSTERED INDEX Tind on #dirtyDocInterventions (ptInterventionId)

    I checked the execution plan (see the following picture) and it losted a lot of time on "Sorting Input" (cost 94 %) by making a order by on ptIntervention (ASC order).

    The part of the code having issue is :

    INSERT INTO #dirtyDocInterventions

    SELECT

    p.ptInterventionId

    ,p.conceptId

    ,p.dataFocusId

    ,p.chartTime

    ,p.storeTime

    ,d.ptDocumentId

    ,p.isDemographic

    FROM @MyPtIntervention p

    INNER JOIN @ptNormData d ON p.ptInterventionId = d.ptInterventionId

    I don't understand because I don't specify a Order By. Could you explain me and could you give me a solution to avoid this lost time ?

    Thanks for your help,

    Eric

  • April 5, 2012 at 11:00 am

    #1469707

    Couple of things. One the picture doesn't do us any good, it's just a picture. We need the actual execution plan. Save it as a .sqlplan file and upload that.

    Second, we also need to see the code, the whole code, and nothing put the code. Well, not totally true, we also need to the DDL for all the tables that the procedure uses along with any indexes definded on those tables.

  • April 5, 2012 at 11:51 am

    #1469731

    Since you have indexes on your temp table, the results from your select need to be sorted according to the index. That sort operation is the "cost of the index", so to speak.

  • April 5, 2012 at 6:51 pm

    #1469934

    Charles Hearn (4/5/2012)

    Since you have indexes on your temp table, the results from your select need to be sorted according to the index. That sort operation is the "cost of the index", so to speak.

    I'll second that explanation. In fact, having the wrong indexes can be the primary source of time-outs according to front-end code.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • April 6, 2012 at 3:20 am

    #1469995

    As a side note:

    Usually I vote against table variables used in a join due to the risk of bad performance caused by missing statistics. (SQL Server will assume there's only one row in each table.)


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • April 10, 2012 at 9:05 pm

    #1471651

    Hey, What's the size data inserted into the table? I recommend to use create clustered index for better performance. Also create index on the table after insertion of records.

  • April 12, 2012 at 2:22 pm

    #1472943

    I can probably count on my fingers and toes how many times (in 15+ years of consulting on SQL Server) how many times an index on a temporary table provided BETTER overall performance than not having said index (or some other refactor). I cannot count the number of times I have REMOVED indexes from client's temporary tables to make things more efficient! So the first thing I would try is taking the index off.

    You are paying the price for creating/populating the index whether it gets used or not. And often it does not, and often when it DOES it still isn't more efficient overall.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • April 12, 2012 at 5:04 pm

    #1472996

    TheSQLGuru (4/12/2012)

    I can probably count on my fingers and toes how many times (in 15+ years of consulting on SQL Server) how many times an index on a temporary table provided BETTER overall performance than not having said index (or some other refactor). I cannot count the number of times I have REMOVED indexes from client's temporary tables to make things more efficient! So the first thing I would try is taking the index off.

    You are paying the price for creating/populating the index whether it gets used or not. And often it does not, and often when it DOES it still isn't more efficient overall.

    Agreed again. Normally such Temp Tables contain only the data necessary and you need to look at the whole table anyway. Unless the table width is a problem, most indexes don't help because you have to read the whole table.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 8 posts - 1 through 7 (of 7 total)

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