SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Global vs Local Temporary Tables


Global vs Local Temporary Tables

Author
Message
Ak-763166
Ak-763166
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 1014
Hi All,

I have a server with 16 processors, 32 Gb of RAM and about 3500 databases. I believe this is huge number of db.

I have a store procedure which has to drop 30 global temporary table. The db size is about 55MB. Only the dropping of the global temporary tables take about 6-7 secs to execute.

Can this be optimised? Will local temporary tables go faster.... (i believe not)

I have written a test procedure which is as follows:

--CREATE TABLES
declare @i int
declare @sql varchar(200)
declare @name varchar(20)
set @name = '##table'
select @i= 0
while( @i < 50)
begin
set @sql = ('create table '+ @name + convert(varchar(10), @i) + ' (col1 varchar(10) )')
-- set @sql = 'Drop table '+ @name + convert(varchar(10), @i)

print @sql
exec (@sql)
set @i = @i +1
end


I have tried to create global temp tables and drop it (takes 18 sec). However, while creating the local temp table itself, it takes long (18 sec).


Can someone please help/guide me with how can i optimise?
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39261 Visits: 32616
What is it that you're actually trying to do?

Just so you know, the code you posted ran in 89ms on my desktop (not the fastest machine on earth either).

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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Mike - CI
Mike - CI
Old Hand
Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)

Group: General Forum Members
Points: 386 Visits: 1155
This runs incredibly fast on my local machine as well (not remotely fast desktop).

I am guessing with 3500 DB's you may very well be having some contention issues on tempdb (especially if all 3500 DB's are sharing the same tempdb).

How many instances do you have running these databases and how are the tempdb files split out on those instances (all on the same logical drive, all on different drives, etc..).
Atul Kumar
Atul Kumar
SSC Eights!
SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)

Group: General Forum Members
Points: 934 Visits: 579
Your question is not very clear. Did you check the size of Tempdb and free space on Disk ? Still I don't see any big issue such.
noeld
noeld
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9520 Visits: 2048
Why so many DBs?

You surely have a lot of contention on tempdb!


* Noel
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39261 Visits: 32616
noeld (2/19/2009)
Why so many DBs?

You surely have a lot of contention on tempdb!


And everywhere else.

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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
noeld
noeld
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9520 Visits: 2048
Grant Fritchey (2/19/2009)
noeld (2/19/2009)
Why so many DBs?

You surely have a lot of contention on tempdb!


And everywhere else.


You got that right! BigGrin


* Noel
Ak-763166
Ak-763166
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 1014
Truly said. The create statement run within a few milliseconds but the dropping of the global tables takes a lot of time. (If you uncomment the line with the drop statement, and comment the create statement).


I am simply trying to understand why dropping global temporary tables takes more time than creating them.

Thanks for your replies.
A.
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39261 Visits: 32616
Temporary tables can be dropped automatically as the process that created them leaves scope (the user logs off or breaks the connection). Global temp tables are dropped automatically when all processes that have referenced them leave scope. I don't have hard evidence to back this up because I've never done quite what you're doing, but I suspect that when you issue the drop statement on the global temp table, it's doing some kind of check against existing sessions(and with that many databases I'll bet you have an equally high number of sessions going) to see if anyone is referencing prior to dropping it. If you're not referencing the temp table by multiple sessions, there's no reason to make it a global temp table.

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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Atul Kumar
Atul Kumar
SSC Eights!
SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)

Group: General Forum Members
Points: 934 Visits: 579
I agree with the view. If the Global Temp table us not used in multiple sessions, we should use the local temp tables only. Even it is advisable to use the local temp table or derived table "Table" variable instead of Global temp tables.

It will always take time when you want to drop a table as it will check the validy of your statement and any reference made to the object. In case of Global temp table, the scope of validate increases and hence the time taken to validate.

Try to avoid the usage of Global Temp table whever possible.

Refer to http://www.sqlservercentral.com/articles/Basic+Querying/temptablesinsqlserver/1279/ for more information.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search