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

Pablo Andres Ibarra Duprat 21 Reputation points
2021-03-15T18:30:45.86+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 106K Reputation points MVP
    2021-03-16T07:35:13.373+00:00

    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.

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Pablo Andres Ibarra Duprat 21 Reputation points
    2021-03-15T22:50:56.907+00:00

    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

    0 comments No comments

  2. MelissaMa-MSFT 24,181 Reputation points
    2021-03-16T01:44:31.807+00:00

    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.

    0 comments No comments