Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Global vs Local Temporary Tables Expand / Collapse
Author
Message
Posted Wednesday, February 18, 2009 9:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 17, 2012 7:40 AM
Points: 48, Visits: 1,014
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?
Post #660040
Posted Thursday, February 19, 2009 6:48 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 7:32 PM
Points: 15,517, Visits: 27,895
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #660250
Posted Thursday, February 19, 2009 8:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:10 PM
Points: 202, Visits: 1,087
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..).
Post #660364
Posted Thursday, February 19, 2009 8:35 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 6:00 AM
Points: 806, Visits: 413
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.
Post #660425
Posted Thursday, February 19, 2009 8:45 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:51 AM
Points: 6,266, Visits: 2,028
Why so many DBs?

You surely have a lot of contention on tempdb!



* Noel
Post #660448
Posted Thursday, February 19, 2009 9:25 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 7:32 PM
Points: 15,517, Visits: 27,895
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #660499
Posted Thursday, February 19, 2009 11:54 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:51 AM
Points: 6,266, Visits: 2,028
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! :D



* Noel
Post #660664
Posted Monday, February 23, 2009 9:45 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 17, 2012 7:40 AM
Points: 48, Visits: 1,014
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.
Post #663205
Posted Tuesday, February 24, 2009 5:33 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 7:32 PM
Points: 15,517, Visits: 27,895
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #663367
Posted Tuesday, February 24, 2009 8:01 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 6:00 AM
Points: 806, Visits: 413
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.
Post #663515
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse