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-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 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 Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99817 Visits: 33014
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
SSC Eights!
SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)

Group: General Forum Members
Points: 916 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
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1374 Visits: 617
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
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

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

You surely have a lot of contention on tempdb!


* Noel
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99817 Visits: 33014
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
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23144 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-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 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 Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99817 Visits: 33014
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
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1374 Visits: 617
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