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:
declare @i int
declare @sql varchar(200)
declare @name varchar(20)
set @name = '##table'
select @i= 0
while( @i < 50)
set @sql = ('create table '+ @name + convert(varchar(10), @i) + ' (col1 varchar(10) )')
-- set @sql = 'Drop table '+ @name + convert(varchar(10), @i)
set @i = @i +1
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?