question

PabloAndresIbarraDuprat-5295 avatar image
0 Votes"
PabloAndresIbarraDuprat-5295 asked ·

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

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

sql-server-transact-sql
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The code in your post is not readable; you need to edit it.

You can use the button with 101010 on to insert code.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PabloAndresIbarraDuprat-5295 avatar image
0 Votes"
PabloAndresIbarraDuprat-5295 answered ·

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

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ·

Hi @PabloAndresIbarraDuprat-5295

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.