Statements that involve temp tables (#) are not properly reusing plans

Question

Statements that involve temp tables (#) are not properly reusing plans

jfong-786685

Valued Member

Points: 51
More actions
March 12, 2011 at 9:46 pm

#233235

We are submitting multiple dynamic sqls that are using temp tables. For example, let say we submit the 3 below statements which are identical and parameterized:
INSERT INTO #DATAMART_DBO_PERF_TOTAL_EXTENDED ( DMART_FUND_ID, DMART_CURRENCY, UPD_USER, UPD_DATETIME, ITDA_BM1_RETURN, ITDA_RETURN, ITD_BM1_RETURN, ITD_RETURN, ITD_NET_RETURN, ITDA_NET_RETURN ) VALUES ( @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10 )
INSERT INTO #DATAMART_DBO_PERF_TOTAL_EXTENDED ( DMART_FUND_ID, DMART_CURRENCY, UPD_USER, UPD_DATETIME, ITDA_BM1_RETURN, ITDA_RETURN, ITD_BM1_RETURN, ITD_RETURN, ITD_NET_RETURN, ITDA_NET_RETURN ) VALUES ( @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10 )
INSERT INTO #DATAMART_DBO_PERF_TOTAL_EXTENDED ( DMART_FUND_ID, DMART_CURRENCY, UPD_USER, UPD_DATETIME, ITDA_BM1_RETURN, ITDA_RETURN, ITD_BM1_RETURN, ITD_RETURN, ITD_NET_RETURN, ITDA_NET_RETURN ) VALUES ( @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10 )
However, SQL Server will create individual cache plans for each statement. Would anyone know how to get these type of statements to re-use?

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 1

jfong-786685 (3/12/2011)
We are submitting multiple dynamic sqls that are using temp tables. For example, let say we submit the 3 below statements which are identical and parameterized:
INSERT INTO #DATAMART_DBO_PERF_TOTAL_EXTENDED ( DMART_FUND_ID, DMART_CURRENCY, UPD_USER, UPD_DATETIME, ITDA_BM1_RETURN, ITDA_RETURN, ITD_BM1_RETURN, ITD_RETURN, ITD_NET_RETURN, ITDA_NET_RETURN ) VALUES ( @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10 )
INSERT INTO #DATAMART_DBO_PERF_TOTAL_EXTENDED ( DMART_FUND_ID, DMART_CURRENCY, UPD_USER, UPD_DATETIME, ITDA_BM1_RETURN, ITDA_RETURN, ITD_BM1_RETURN, ITD_RETURN, ITD_NET_RETURN, ITDA_NET_RETURN ) VALUES ( @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10 )
INSERT INTO #DATAMART_DBO_PERF_TOTAL_EXTENDED ( DMART_FUND_ID, DMART_CURRENCY, UPD_USER, UPD_DATETIME, ITDA_BM1_RETURN, ITDA_RETURN, ITD_BM1_RETURN, ITD_RETURN, ITD_NET_RETURN, ITDA_NET_RETURN ) VALUES ( @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10 )
However, SQL Server will create individual cache plans for each statement. Would anyone know how to get these type of statements to re-use?

Even if you used "regular" tables, an execution plan must be chosen and executed for every statement even if it's a "reused" statement.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jfong-786685 Valued Member Points: 51 More actions · Answer 2

If the below examples were regular tables:

INSERT INTO DATAMART_DBO_PERF_TOTAL_EXTENDED ( DMART_FUND_ID, DMART_CURRENCY, UPD_USER, UPD_DATETIME, ITDA_BM1_RETURN, ITDA_RETURN, ITD_BM1_RETURN, ITD_RETURN, ITD_NET_RETURN, ITDA_NET_RETURN ) VALUES ( @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10 )

SQL Server would create a plan for the first statement and the next incoming statements would reuse the first plan, not creating another "unique" plan.

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 3

Did you test it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)