3

Inside a frequently executed stored procedure (sometimes up to 10-15 times per second), there are two particular statements that show up in Top 10 heaviest queries by cumulative CPU impact

These are DDL statements to create #Temp tables:

1.

SELECT cast(0 as int) as rowId
     , Column1 as tColumn1
     , Column2 as tColumn2
     , ...
     , Column14 as tColumn14
     , cast(0 as datetime) as tUTC
     , -1 as tRefId
INTO #TempTable1
FROM Table1 WITH(NOLOCK)
WHERE 0=1
SELECT    tColumn1, ..., tColumn14, tUtc, tRefId
INTO    #TempTable2
FROM    #TempTable1
WHERE    0=1

Any of above DDL, take on average 10-15 ms CPU time, simply to create a #temp table
Changing stored procedure's logic, to make it NOT create temp tables, is not an option

Question: How can temp table creation time be sped up, in terms of CPU time ?

2
  • 3
    Are they in stored procedures? If they were you would get temp table caching, see sqlperformance.com/2017/05/sql-performance/…. I would seriously question the use of NOLOCK, especially given you aren't readin any rows with where 1=0 so the only locks you get are Sch-S locks which NOLOCK does not prevent. I agree that CREATE TABLE makes more sense. Jan 5 at 15:08
  • @Charlieface yes both inside one stored procedure. I'll try to use the CREATE TABLE and see if it helps Jan 6 at 14:42

2 Answers 2

9
SELECT  ...  INTO  ... WHERE 0=1

might be a convenient way to create an empty table but I would expect a simple CREATE TABLE DDL statement to be more efficient.

There is no need for the CREATE TABLE to go through the plan compilation process (to compile an execution plan that gets simplified down to a select from a constant scan) and then execute it.

Potentially more importantly (for this discussion) the CREATE TABLE version inside a stored procedure may also allow better use of SQL Server Temporary Object Caching to avoid much of the work required for creation at all.

For this to happen you need to avoid certain constructs that prevent caching. See the linked post for more details. One of those is 'Perform "DDL" after object creation'.

CREATE TABLE allows indexes and constraints to be declaratively defined at creation time.

SELECT ... INTO would require them to be added post create so this route is more likely to end up in a state where the table does not meet the requirements for caching.

1
  • thanks @MartinSmith for reply, I will check and get back soon Jan 6 at 14:43
0

Unless dealing with small datasets, I have always found it faster to create the temp table prior to inserting data using the CREATE TABLE #TableName syntax. If you are indexing the table I've always had the best performance by adding it after the data has been inserted.

3
  • Adding the index afterwards disables temp table caching, which is probably the opposite of what OP wants. Still might be faster if the inserted data is not pre-sorted, but as usual it depends Jan 5 at 15:14
  • Good thoughts but probably not directly relevant to OP's question, since he's trying to improve DDL performance on queries that don't involve any data yet.
    – J.D.
    Jan 6 at 3:11
  • @Charlieface - Thanks for the link. I was not aware of that!
    – c00pdetat
    Jan 6 at 18:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.