Temporary tables & execution time!!

Question

Temporary tables & execution time!!

Nisha-475382

Hall of Fame

Points: 3535
More actions
March 5, 2008 at 7:00 am

#179558

Is using too many temporary tables (#Temp..) in an SP, a bad practice by any chance? Or does it hamper performance?
I have an SP that imports records from over 5 Access tables through a linked server.
I use several temporary tables to store these records prior to insertion in SQL as I need to perform data validation and also various calculations for number of eligible, imported and not imported records.
Seems like my SP takes over 1.5 mins to execute.
But then again there is this table in Access with a million records, and simply reading and retrieving required records from it in a single query in SSMS takes a while.

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

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

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 1

It's not hard & fast or black & white. In general terms, using a temp table is not bad. It depends on what you're doing with it and how it's used. For example, one of the classic problematic uses of temp tables is to run a select statement against a table to populate a temp table then to run a series of updates against that temp table from various other tables. What this is doing is using the temp table in place of a join or joins. That's bad. An example of, what I think anyway, of a good use of temp tables... Back in 2000, before we had XPath queries, OPENXML was how we had to get XML data into the database. We had a series of batch inserts we wanted to do. We would load the XML into a temp tables and then close the XML document as fast as possible, then do the data loads from the temp tables (no updates to the temp tables after loading them). It worked well because it closed out the memory allocation for the XML faster at the cost of some I/O to load the temp tables.

So it can go either way. How are you using yours?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 2

Also the configuration of tempdb database may have impact on performance of temp tables. If you have 1 million records, you should create indexes on these temp tables on fields you use to identify records.

Width of the table is another question - maybe you could shorten or drop some columns you don't use.

Piotr

...and your only reply is slàinte mhath

Nisha-475382 Hall of Fame Points: 3535 More actions · Answer 3

Grant Fritchey (3/5/2008)
What this is doing is using the temp table in place of a join or joins. That's bad.

True, I couldn't agree more.

Grant Fritchey (3/5/2008)
It depends on what you're doing with it and how it's used.
How are you using yours?

I'm simply using my temp tables to hold data until I'm done validating, calculating and creating new records in new temp tables. All temp tables load data into different SQL tables.

There's just one place where I have to perform an update on a temp table, but that isn't in place of a join.. I have to generate a WorkOrderNumber for each record based on certain calculations (depending on the type of task and already existing nos. in Access and SQL), so this is something I do after loading the temp table.

What do you think?

Nisha-475382 Hall of Fame Points: 3535 More actions · Answer 4

Piotr Rodak (3/5/2008)
Also the configuration of tempdb database may have impact on performance of temp tables.

How do I check the configuration of tempdb? What should I be looking at, Piotr?

Piotr Rodak (3/5/2008)
If you have 1 million records, you should create indexes on these temp tables on fields you use to identify records.

Now, I'm simply doing a SELECT * INTO #Temp FROM...

Are you suggesting I first create these temp tables with indexes prior to loading them?

Could you tell me some more about how I should create my indexes? I'm not too familiar with all this! :ermm:

Piotr Rodak (3/5/2008)
Width of the table is another question - maybe you could shorten or drop some columns you don't use.

I've been thinking about this..

What I'm wondering is... if I say

--HERE I'M SELECTING ALL COLUMNS

SELECT * FROM LinkedServer...Locations

--HERE I'M SELECTING ALL 51 COLUMNS, SAME AS ABOVE

SELECT Column1,

,Column2

,Column3

, ......

.

,Column51

FROM LinkedServer...Locations

--HERE I'M SELECTING ONLY REQUIRED 31 COLUMNS

Select Column1

,Column2

,Column8

,Column15

,Collumn22

.

,Column39

.

,Column51

FROM LinkedServer...Locations

Will there be a significant difference in execution time in the above queries? There are 5 memo fields I'm selecting here from the Access table.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

Grant Fritchey (3/5/2008)
What this is doing is using the temp table in place of a join or joins. That's bad.

Ummm... I gotta say, "Not always". I've used temp tables in the exact fashion you described to break up monsterous single SELECTs with multiple "haywire" joins in a "Divide and Conquer" fashion. The end result was that I was able to convert, for example, a 30 minute run into a 6 second run... and, no, that's not a type-o. 🙂

--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)

Jacob Luebbers SSCarpal Tunnel Points: 4834 More actions · Answer 6

Nisha (3/6/2008)
Now, I'm simply doing a SELECT * INTO #Temp FROM...

Whoa! Don't do that - SELECT... INTO... on a temp table needs a schema lock to create the temp table and holds it for the duration of the insert. Best practice is to manually CREATE your temp table first then INSERT into it seperately. Otherwise you may find you're blocking out other TempDB users (eg most SPIDs) while you insert...

Nisha (3/6/2008)
I've been thinking about this..
What I'm wondering is... if I say
--HERE I'M SELECTING ALL COLUMNS
SELECT * FROM LinkedServer...Locations
--HERE I'M SELECTING ALL 51 COLUMNS, SAME AS ABOVE
...
-snip-
...
Will there be a significant difference in execution time in the above queries? There are 5 memo fields I'm selecting here from the Access table.

Can't say whether there will be a significant difference, however there will be a difference. If you don't need the extra columns don't select them. You will be pulling less data from the Access DB and causing less pages to be used on the SQL side.

Regards,

Jacob

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

Jacob Luebbers (3/6/2008)
Nisha (3/6/2008)
Now, I'm simply doing a SELECT * INTO #Temp FROM...
Whoa! Don't do that - SELECT... INTO... on a temp table needs a schema lock to create the temp table and holds it for the duration of the insert. Best practice is to manually CREATE your temp table first then INSERT into it seperately. Otherwise you may find you're blocking out other TempDB users (eg most SPIDs) while you insert...
Nisha (3/6/2008)
I've been thinking about this..
What I'm wondering is... if I say
--HERE I'M SELECTING ALL COLUMNS
SELECT * FROM LinkedServer...Locations
--HERE I'M SELECTING ALL 51 COLUMNS, SAME AS ABOVE
...
-snip-
...
Will there be a significant difference in execution time in the above queries? There are 5 memo fields I'm selecting here from the Access table.
Can't say whether there will be a significant difference, however there will be a difference. If you don't need the extra columns don't select them. You will be pulling less data from the Access DB and causing less pages to be used on the SQL side.
Regards,
Jacob

Actually - select...into locks the schema so very little time - I honestly doubt you'd notice. On the other hand - select...INTO does seem to have a SUBSTANTIAL performance improvement over INSERT (like 25-30%). Quite honestly - well worth it in my book.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jacob Luebbers SSCarpal Tunnel Points: 4834 More actions · Answer 8

To be honest I've never actually measured it myself, just heard it quoted in a number of sources as a bad practice and I've always studiously avoided it. Do you know if you get the same 25-30% performance boost on a plain INSERT with a schema lock hint?

I'd argue that you'd only be better off using a SELECT... INTO... onto a temp table if it happens very infrequently. Even if you only hold the schema lock for a short duration it still is effectively a critical section on a very "hot" resource (TempDB). Or if you really need that extra speed from SELECT... INTO... do it into a permanent table in a different DB.

Regards,

Jacob

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 9

Also, on the SELECT...INTO vs. CREATE TABLE & SELECT question. That one depends too. It depends on whether or not you are going to create indexes or in any other way perform a DDL operation on the temporary table. If you do, you may get recompiles on the procedure. That will kill performance for sure (depending on the size of the query, etc.).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 10

SELECT/INTO hauls butt in any DB but it runs like an ape that sat on a hot coal if you use it on a database that is setup for anything other than FULL recovery. And, guess what... TempDB is setup for SIMPLE recovery.

Here's something for you to test with. For tables in TempDB, it'll do a million rows in just over 5 seconds... 10,000 rows almost doesn't show up on the radar. Should you do that many rows for high hit ratio GUI code? Probably not... but, for batch code, you can't beat SELECT/INTO and it runs so fast, you really don't have to worry that much about it taking a lock on Sys.Objects in TempDB...

[font="Courier New"]SET&nbspSTATISTICS&nbspTIME&nbspON

&nbspSELECT&nbspTOP&nbsp10000&nbsp--Change&nbspthis&nbspnumber&nbspto&nbspchange&nbspthe&nbsptest

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspIDENTITY(INT,1,1)&nbspAS&nbspN,

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspsc1.ID&nbspAS&nbspsc1ID,&nbspsc1.Xtype&nbspAS&nbspXType1,

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspsc2.ID&nbspAS&nbspsc2ID,&nbspsc2.Xtype&nbspAS&nbspXtype2

&nbsp&nbsp&nbspINTO&nbsp#MyHead

&nbsp&nbsp&nbspFROM&nbspMaster.dbo.SysColumns&nbspsc1,

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspMaster.dbo.SysColumns&nbspsc2

DROP&nbspTABLE&nbsp#MyHead[/font]

--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)

Nisha-475382 Hall of Fame Points: 3535 More actions · Answer 11

Jacob
Whoa! Don't do that - SELECT... INTO... on a temp table needs a schema lock to create the temp table and holds it for the duration of the insert. Best practice is to manually CREATE your temp table first then INSERT into it seperately. Otherwise you may find you're blocking out other TempDB users (eg most SPIDs) while you insert...

Matt
Actually - select...into locks the schema so very little time - I honestly doubt you'd notice. On the other hand - select...INTO does seem to have a SUBSTANTIAL performance improvement over INSERT (like 25-30%). Quite honestly - well worth it in my book.

Ok guys... I'm confused! What should I be doing now? Again.. I have PLENTY of temp tables in my SP.. THIRTY in number! And various operations performed on these throughout the 1600 lines of code.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 12

Nisha, it depends... is the code for high hit ratio GUI code or is it for batch code that runs every once in a while?

--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)

Nisha-475382 Hall of Fame Points: 3535 More actions · Answer 13

Jeff Moden (3/6/2008)
Nisha, it depends... is the code for high hit ratio GUI code or is it for batch code that runs every once in a while?

Ooh.. sorry.. seems like we posted replies around the same time so I missed out reading your previous reply.

Ok.. I'm not entirely sure I understand what you mean by 'high hit ratio GUI code, Jeff :ermm:

There's an Import process triggered by 'ExportToSQL' Button on Legacy system (Access) that does a batch import of all WorkOrders for selected Contract, along with corresponding Locations, Providers, Installers, and so on.... the work orders could be in the range of 0-2000 in number during the import.

Does that make sense? :blink:

EDIT:

Ok, the import could happen over and over again in the same day or maybe once every few days... depending on the client's need. Like, whether he has new WorkOrders to push into SQL or not.

Jacob Luebbers SSCarpal Tunnel Points: 4834 More actions · Answer 14

Interesting Jeff - just compared your test query (1m rows) with slightly modified version that CREATEs #MyHead first and INSERTs into it (both running in TempDB). This is on a busy server so times are inaccurate, however:

SELECT... INTO... < 5 s

CREATE... INSERT... > 10 s

Adding a TABLOCK hint on the second version didn't sem to help the runtime measurably. Can you (or anyone else) comment on the reasons for this difference?

To your last comment - 100% agree given these results. The answer to "should I use SELECT... INTO... or CREATE... INSERT... will depending on the frequency of execution rather than a hard and fast rule.

Regards,

Jacob