t-sqlcommon-table-expression

SQL insert into using CTE


I am facing a performance issue due to "Insert into" statement in sql. I am using a CTE to select data from multiple tables and insert into other table. It was working just fine until yesterday. Select takes less than a minute to retrieve the data where as insert into taking forever. Can some one please help me in understanding what i am doing wrong. Any help is highly appreciated. Thanks.

Here is my code:

I am using this query in an SP. I am trying to load 220K records to 1.5M records table.

;with CTE_A
AS
(
      SELECT A1, A2,...           
      FROM dbo.A  with (nolock)
      WHERE A1 = <some condition>  
      GROUP BY a.A1,a.A2 , a.A3    
), CTE_C as
   (
       SELECT C1, C2,....                
       FROM dbo.B with (nolock)       
       WHERE a.C1 = <some condition>   
       GROUP BY a.c1,a.C2 , a.C3      
)
INSERT INTO [dbo].MainTable
    SELECT 
        A1, A2, A3 , C1, C2, C3       
    FROM 
        CTE_A ta with (nolock)
    LEFT OUTER JOIN 
        CTE_C tc with (nolock) ON ta.a1 = tc.a1 and ta.b1 = tc.b1 and ta.c1 = tc.c1 
    LEFT OUTER JOIN 
        othertable bs with (nolock) ON usd_bs.c = s.c   
                                        AND (A1 BETWEEN bs.a1 AND bs.a1)
                                        AND bs.c1 = 1                     

Solution

  • try this method (temp table instead cte), perfomance must be much higher for your task

    IF OBJECT_ID('Tempdb..#CTE_A') IS NOT NULL 
        DROP TABLE #CTE_A
    IF OBJECT_ID('Tempdb..#CTE_C') IS NOT NULL 
        DROP TABLE #CTE_C
    -------------------------------------------------------------
    SELECT  A1 ,
            A2 ,...   
    INTO    #CTE_A --data set into temp table
    FROM    dbo.A WITH ( NOLOCK )
    WHERE A1 = <some condition>  
    GROUP BY a.A1 ,
            a.A2 ,
            a.A3
    -------------------------------------------------------------
    SELECT  C1 ,
            C2 ,....                
    FROM    dbo.B WITH ( NOLOCK )       
    INTO  #CTE_C --data set into temp table
    WHERE a.C1 = <some condition>   
    GROUP BY a.c1 ,
            a.C2 ,
            a.C3
    -------------------------------------------------------------
    INSERT  INTO [dbo].MainTable
            SELECT  A1 ,
                    A2 ,
                    A3 ,
                    C1 ,
                    C2 ,
                    C3
            FROM    #CTE_A AS ta
                    LEFT JOIN #CTE_C AS tc ON ta.a1 = tc.a1
                                              AND ta.b1 = tc.b1
                                              AND ta.c1 = tc.c1
                    LEFT JOIN othertable AS bs ON usd_bs.c = s.c
                                                  AND ( A1 BETWEEN bs.a1 AND bs.a1 )
                                                  AND bs.c1 = 1