June 3, 2008 at 1:50 pm
Today I stumbled on an really great feature while reading "INSIDE MICROSOFT SQL SERVER 2005 - QUERY TUNING AND OPTIMIZATION" by Kalen Delaney et al., Chapter 1, pgs44-46
SQL Server caches temporary objects so that dropping and recreating them in code is very fast.
There are conditions though.
Among them:
- temp tables with named constraints or subsequent DDL statements (CREATE INDEX etc.) do not get cached.
- temp object needs to be created inside another object, eg. stored procedure
Take for example the following creation of a temp table, done inside a stored procedure:
CREATE TABLE #t1 (c1 int, c2 int, c3 char(5000));
CREATE UNIQUE CLUSTERED INDEX ci_t1 ON #t1(c1);
If this stored procedure is executed multiple times within a loop, the following equivalent temp table creation will yield much better performance!! :w00t:
CREATE TABLE #t1 (c1 int UNIQUE, c2 int, c3 char(5000));
I did the following test and the performance results are striking:
stored procedures:
CREATE PROCEDURE test_temptable_caching_explicit_idx
AS
CREATE TABLE #t1 (c1 int, c2 int, c3 char(5000));
CREATE UNIQUE CLUSTERED INDEX ci_t1 ON #t1(c1);
DECLARE @i int;
SET @i = 0;
WHILE ( @i < 10 )
BEGIN
INSERT INTO #t1 VALUES ( @i, @i + 1000, 'hello' );
SET @i = @i + 1;
END
DROP TABLE #t1;
GO
CREATE PROCEDURE test_temptable_caching_unnamed_constraint
AS
CREATE TABLE #t1 (c1 int UNIQUE, c2 int, c3 char(5000));
DECLARE @i int;
SET @i = 0;
WHILE ( @i < 10 )
BEGIN
INSERT INTO #t1 VALUES ( @i, @i + 1000, 'hello' );
SET @i = @i + 1;
END
DROP TABLE #t1;
GO
test script:
SET NOCOUNT ON;
DECLARE @table_cnt_before BIGINT;
DECLARE @table_cnt_after BIGINT;
DECLARE @i int;
select @table_cnt_before = cntr_value
from sys.dm_os_performance_counters
where counter_name = 'Temp Tables Creation Rate';
SET @i = 0;
WHILE ( @i < 10000 )
BEGIN
EXEC test_temptable_caching_explicit_idx; -- 1st test
--EXEC test_temptable_caching_unnamed_constraint; -- 2nd test
SELECT @i = @i + 1;
END
SELECT @table_cnt_after = cntr_value
from sys.dm_os_performance_counters
where counter_name = 'Temp Tables Creation Rate';
PRINT 'Temp tables created during the test: ' +
CONVERT( VARCHAR(100), @table_cnt_after - @table_cnt_before );
Run the test script first executing sproc test_temptable_caching_explicit_idx and then a second time executing sproc test_temptable_caching_unnamed_constraint. The sprocs are executed 10,000 times in a loop.
On my machine, the first run took about a minute, with the temp table created and dropped 10,000 times.
The 2nd run took 13 sec(!), and the temp object was created only once and re-used in the remaining iterations!
The morale of the story is, if you can emulate a temp table index in SQL 2005 through an unnamed constraint, ie. without explicitly creating an index, you can reap great performance benefits.
Apologies to those of you who were already familiar with this, but I'm still spellbound! :w00t:
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 3, 2008 at 2:07 pm
Hmm, the results are a lot less striking if instead of a UNIQUE constraint, we specify a CLUSTERED PRIMARY KEY:
CREATE TABLE #t1 (c1 int, c2 int, c3 char(5000));
CREATE CLUSTERED INDEX ci_t1 ON #t1(c1);
On my machine: 1 min
CREATE TABLE #t1 (c1 int PRIMARY KEY CLUSTERED, c2 int, c3 char(5000));
On my machine: 50 sec
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 3, 2008 at 2:42 pm
Marios, shouldn't your first test have created a unique clustered index, not just a clustered index, for purposes of this test? Or did I miss something?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 3, 2008 at 3:29 pm
GSquared (6/3/2008)
Marios, shouldn't your first test have created a unique clustered index, not just a clustered index, for purposes of this test? Or did I miss something?
Good catch, thanks! 🙂
Using:
CREATE TABLE #t1 (c1 int PRIMARY KEY CLUSTERED, c2 int, c3 char(5000));
Elapsed time (ran twice): 52 sec
Using:
CREATE TABLE #t1 (c1 int, c2 int, c3 char(5000));
CREATE UNIQUE CLUSTERED INDEX ci_t1 ON #t1(c1);
Elapsed time (ran twice): 62 sec
Using:
CREATE TABLE #t1 (c1 int UNIQUE, c2 int, c3 char(5000));
Elapsed time (ran twice): 14 sec (!!!)
The named index (2nd test) gives consistently the worst performance.
The unnamed UNIQUE constraint - inline - gives by far the best results!! :w00t:
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 4, 2008 at 12:00 pm
With regard to this, "SQL Server caches temporary objects so that dropping and recreating them in code is very fast."
I just ran these three tests:
set nocount on
declare @Tests int, @Start datetime
select @tests = 1000, @start = getdate()
while @tests > 0
begin
create table #t (
ID tinyint)
create unique clustered index UCI_T on #t(id)
insert into #t (id)
select 1
drop table #t
select @tests = @tests - 1
end
select datediff(ms, @start, getdate())
Then I modified the table creation to:
create table #t (
ID tinyint unique)
Then to:
create table #t (
ID tinyint primary key)
(In the last two tests, there was no separate index creation.)
Results were:
ID tinyint primary key: 2906 milliseconds
ID tinyint unique: 3720
create unique clustered index UCI_T on #t(id): 3220
It looks to me like what your tests were doing was testing insertion time, not create/drop time.
So, I also did this test:
create table #T (
ID int)
create unique clustered index UCI_T on #t(id)
--ID int primary key: 47
--ID int unique: 125
--create unique clustered index UCI_T on #t(id): 47
insert into #t (id)
select number
from dbo.numbers
drop table #t
The commented lines are the variations on the test, and the numbers are milliseconds for inserts.
As always, your mileage may vary.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 4, 2008 at 3:19 pm
Thank you for the feedback.
However, to get the fantastic performance gain associated with unnamed constraints, the temp table needs to be inside another object, such as a stored procedure. Otherwise, the temp object is not cached (see my OP).
Pls try the following.
Create 3 stored procedures, corresponding to the 3 temp-table constraint types we are looking at here:
- named index
- unique unnamed constraint
- primary key unnamed constraint
CREATE PROC test_caching_NAMED_IDX
AS
set nocount on
declare @tests int
select @tests = 10
while @tests > 0
begin
create table #t ( ID tinyint)
create unique clustered index UCI_T on #t(id)
insert into #t (id)
select 1
drop table #t
select @tests = @tests - 1
end
GO
--------------
CREATE PROC test_caching_UNIQUE_unnamed
AS
set nocount on
declare @tests int
select @tests = 10
while @tests > 0
begin
create table #t ( ID tinyint UNIQUE CLUSTERED )
insert into #t (id)
select 1
drop table #t
select @tests = @tests - 1
end
GO
----------------
CREATE PROC test_caching_PK_unnamed
AS
set nocount on
declare @tests int
select @tests = 10
while @tests > 0
begin
create table #t ( ID tinyint PRIMARY KEY CLUSTERED )
insert into #t (id)
select 1
drop table #t
select @tests = @tests - 1
end
GO
Run the following test for each of the 3 sprocs (comment out 2 out of the 3 sproc calls in the WHILE loop each time):
SET NOCOUNT ON;
DECLARE @start datetime;
SET @start = getdate();
DECLARE @table_cnt_before BIGINT;
DECLARE @table_cnt_after BIGINT;
DECLARE @i int;
select @table_cnt_before = cntr_value
from sys.dm_os_performance_counters
where counter_name = 'Temp Tables Creation Rate';
SET @i = 0;
WHILE ( @i < 100 )
BEGIN
-- EXEC test_caching_NAMED_IDX; -- 1st test -- 2263 ms
--EXEC test_caching_UNIQUE_unnamed; -- 2nd test -- 406 ms
EXEC test_caching_PK_unnamed; -- 3rd test -- 390 ms
SELECT @i = @i + 1;
END
SELECT @table_cnt_after = cntr_value
from sys.dm_os_performance_counters
where counter_name = 'Temp Tables Creation Rate';
PRINT 'Temp tables created during the test: ' +
CONVERT( VARCHAR(100), @table_cnt_after - @table_cnt_before );
select datediff(ms, @start, getdate());
Here are my results (after running each scenario multiple times):
EXEC test_caching_NAMED_IDX; -- 2263 ms
EXEC test_caching_UNIQUE_unnamed; -- 406 ms
EXEC test_caching_PK_unnamed; -- 390 ms
The unnamed constraints far outperform the named index when applied to a temp table created and dropped inside a stored procedure multiple times.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 5, 2008 at 2:53 pm
Okay. I tested it in procs, and got basically the same results you did. The named index, separately created, took 2453, the unique constraint took 453, and the primary key took 420 (all milliseconds).
Unless I really need an index on a temp table, other than the PK, I don't usually create one, but I can't say I really had a solid reason for that before now. Now, I do.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 5, 2008 at 4:23 pm
Are you sure that what you are seeing isn't just the extra IO and overhead to maintain the clustered index?? Don't have time to run the tests at the moment.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 5, 2008 at 4:33 pm
TheSQLGuru (6/5/2008)
Are you sure that what you are seeing isn't just the extra IO and overhead to maintain the clustered index?? Don't have time to run the tests at the moment.
The clustered index is created behind the scenes in all three cases, ie. named index or not.
What we are seeing is the difference in performance temp table caching makes when the temp table is created inside a stored procedure and only unnamed constraints are applied to it at the time of its creation.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply