Query Performance - Table Temporary

Question

Query Performance - Table Temporary

ERIC CRUDELI

SSCommitted

Points: 1786
More actions
April 5, 2012 at 10:53 am

#259492

Hello,
I created a temporary table in a SP :
create table #dirtyDocInterventions (ptInterventionIduniqueidentifier,
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

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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.

Charles Hearn SSCrazy Points: 2680 More actions · Answer 2

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.

Jeff Moden SSC Guru Points: 1004425 More actions · Answer 3

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)

LutzM SSC Guru Points: 107049 More actions · Answer 4

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]

Preetham Gowda Right there with Babe Points: 792 More actions · Answer 5

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.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004425 More actions · Answer 7

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)