Best Practices (performance) to use temporary tables inside of store procedure

Question

Hi. My Question is related to about the best performance advice to use temporary tables inside of store procedure, to take benefits of Query plan generated for store procedure at executing phase, and get a good access method for data inside of temporary table. Let Show an example .. this SQL code is executed inside of Store procedure, but I think the query plan generated for any select that query the #paso_ctacte will be bad. CREATE PROCEDURE [dbo].[pr_CC_SCORE_CLI_PN] --@FECHA_REPROCESO DATETIME AS BEGIN BEGIN TRY ......................................... ........ ........ select distinct Rut=Rut_Cliente, Dv=Dv_Cliente into #paso_ctacte FROM ODS.dbo.ODS_RESUMEN_PASIVO_MENSUAL WHERE CONVERT(nvarchar(6), Fecha_Carga, 112) = CONVERT(nvarchar(6), @FECHA_PROCESO, 112) -- Cambiar Periodo (M-2) and Rut_Cliente<50000000 and Cod_Producto='0000000001' and Estado_Cuenta='CUENTA ACTIVA' and COD_TIPO_BANCA=1 order by Rut_Cliente --59183 CREATE NONCLUSTERED INDEX [IDX_DIS] ON #paso_ctacte ( [RUT] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] Thanks in advance

Answer 1

When it comes to the query plans, there is no reason to assume that they will be bad. When the procedure is first invoked, there will be no plans at all for the statements that refer to the temp table, because the temp table does not exist at the time. Thus, compilation of these statements are deferred.

Furthermore, if there are sufficiently many updates to a table, this will trigger autostats, which in its turn trigger recompilation. This applies both to temp tables and permanent tables.

However, as Melissa also suggests, it is generally best practice to create the table explicitly with CREATE TABLE and include in the index definition in this statement and then insert the data to the table. This is related to that SQL Server caches the definitions of temp tables. This matters when there are many concurrent users. However, caching is not possible if the schema changes during the procedure, and by adding an index you change the schema.

Answer 2

CREATE PROCEDURE [dbo].[pr_CC_SCORE_CLI_PN]

--@FECHA_REPROCESO DATETIME AS BEGIN BEGIN TRY ..............<more lines, definitions>

select distinct Rut=Rut_Cliente,
Dv=Dv_Cliente
into #paso_ctacte
FROM ODS.dbo.ODS_RESUMEN_PASIVO_MENSUAL
WHERE CONVERT(nvarchar(6), Fecha_Carga, 112) = CONVERT(nvarchar(6), @FECHA_PROCESO, 112) -- Cambiar Periodo (M-2)
and Rut_Cliente<50000000
and Cod_Producto='0000000001'
and Estado_Cuenta='CUENTA ACTIVA'
and COD_TIPO_BANCA=1 order by Rut_Cliente --59183

CREATE NONCLUSTERED INDEX [IDX_DIS] ON #paso_ctacte ( [RUT] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

-- until to the end of store procedure

Sorry and Thanks

Answer 3

Hi @Pablo Andres Ibarra Duprat ，

Welcome to Microsoft Q&A!

You could have a try to create one temple table , create index on it and insert data into this temple table inside of store procedure.

Please refer below and check whether it is a little helpful:

CREATE PROCEDURE [dbo].[pr_CC_SCORE_CLI_PN]  
@FECHA_REPROCESO DATETIME   
AS   
BEGIN   
--BEGIN TRY ..............<more lines, definitions>  
  
--DDL of #paso_ctacte  
CREATE TABLE #paso_ctacte  
(Rut VARCHAR(100),  
Dv VARCHAR(100))  
  
--create index on #paso_ctacte  
CREATE CLUSTERED INDEX [IDX_DIS]   
ON #paso_ctacte   
( [RUT] ASC )  
  
--insert data into #paso_ctacte  
INSERT INTO #paso_ctacte   
select distinct Rut=Rut_Cliente,  
Dv=Dv_Cliente  
FROM ODS.dbo.ODS_RESUMEN_PASIVO_MENSUAL  
WHERE CONVERT(nvarchar(6), Fecha_Carga, 112) = CONVERT(nvarchar(6), @FECHA_PROCESO, 112) -- Cambiar Periodo (M-2)  
and Rut_Cliente<50000000  
and Cod_Producto='0000000001'  
and Estado_Cuenta='CUENTA ACTIVA'  
and COD_TIPO_BANCA=1 order by Rut_Cliente --59183  
  
--until to the end of store procedure  
  
--drop table #paso_ctacte  
DROP TABLE #paso_ctacte  
  
END  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.