SQL Server procedure with STUFF taking too much time

Question

I have a simple query in which I am selecting through STUFF and FOR XML PATH and inserting into a temp table.

After that, I am joinig this temp table to the main table to get the desired result based on some parameters, but it is taking too long.

I added a clustered index on the table by which I could get the result of STUFF part of the query little faster than earlier but the overall result is still slow.

Here is my code sample:

DECLARE @TEMP_ACAusVertarg TABLE
                           (ACAusVertag NVARCHAR(MAX),
                            IKLeistungserbringer INT,
                            Rechnungsnummer NVARCHAR(MAX) 
                           )

INSERT INTO @TEMP_ACAusVertarg (ACAusVertag, IKLeistungserbringer, Rechnungsnummer)
    SELECT 
        STUFF((SELECT ',' + T1.ACAusVertrag
               FROM   DB_DMA.REPORTING.Report_3_1_2_3_RechnungsuebersichtPapier_DTA_Ebene_2_TEMP T1
               WHERE
                   --    Erfassungsart = '1'
                   T1.IKLeistungserbringer = T2.IKLeistungserbringer
                   AND T1.Rechnungsnummer = T2.Rechnungsnummer
               ORDER BY 
                   T2.IKLeistungserbringer, T1.ACAusVertrag
               FOR XML path('')), 1, 1, '') AS ACAusVertrag,
        IKLeistungserbringer, Rechnungsnummer
    FROM 
        DB_DMA.REPORTING.Report_3_1_2_3_RechnungsuebersichtPapier_DTA_Ebene_2_TEMP T2
    WHERE 
        T2.Erfassungsart = 1

    SELECT 
        REPORT.Bundesland,
        REPORT.Regionalkennzeichen,
        REPORT.SGS,
        REPORT.AC,
        ACAUSVERTRAG.ACAusVertag,      --NEW
        REPORT.PNR,
        REPORT.Rechnungsnummer,
        REPORT.Zahldatum,
        REPORT.IKRechnungssteller,
        REPORT.NameRechnungssteller,
        REPORT.StrasseRechnungssteller,
        REPORT.PLZRechnungssteller,
        REPORT.OrtRechnungssteller,
        REPORT.IKLeistungserbringer,
        REPORT.NameLeistungserbringer,
        REPORT.StrasseLeistungserbringer,
        REPORT.PLZLeistungserbringer,
        REPORT.OrtLeistungserbringer,
        SUM(REPORT.AnzahlVerordnungen) AS AnzahlVerordnungen
    FROM 
        DB_DMA.REPORTING.Report_3_1_2_3_RechnungsuebersichtPapier_DTA_Ebene_2_TEMP REPORT WITH (NOLOCK) 
    INNER JOIN 
        @TEMP_ACAusVertarg  AS ACAUSVERTRAG ON ACAUSVERTRAG.IKLeistungserbringer = REPORT.IKLeistungserbringer
    WHERE   
        SGS = @SGS      
        AND AC = @AC
        AND Bundesland = @Bundesland
        AND Regionalkennzeichen = @Regionalkennzeichen
        AND Jahr = @Jahr
        AND Erfassungsart = '1'
    GROUP BY 
        REPORT.Bundesland, REPORT.Regionalkennzeichen,
        REPORT.SGS, REPORT.AC, REPORT.PNR,
        REPORT.Rechnungsnummer, REPORT.Zahldatum,
        REPORT.IKRechnungssteller, REPORT.NameRechnungssteller,
        REPORT.StrasseRechnungssteller, REPORT.PLZRechnungssteller,
        REPORT.OrtRechnungssteller,
        REPORT.IKLeistungserbringer, REPORT.NameLeistungserbringer,
        REPORT.StrasseLeistungserbringer,
        REPORT.PLZLeistungserbringer, REPORT.OrtLeistungserbringer,
        REPORT.Berichtigungsgrund,
        ACAUSVERTRAG.ACAusVertag 
    ORDER BY 
        REPORT.IKLeistungserbringer

Parameter values are coming from SSRS Report. Any suggestions please?

You have two queries there. Have you established which one takes the longest? — Nick.Mc, Jul 12 '17 at 14:06
Hey @Nick.McDermaid thanks for quick response. first part takes only 20 secondes and the second part takes long with join with the TEMP table if i remove the join than it takes only 10 seconds — swat, Jul 12 '17 at 14:10
First perhaps try a temp table, suitably indexed and see if that helps. https://stackoverflow.com/questions/27894/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server — Nick.Mc, Jul 12 '17 at 14:15
i already have an index on the main table . do you mean temp table for the main table or what? — swat, Jul 12 '17 at 14:17
Sorry, try a temp table (#MyTable) instead of a table variable (@MyTable), and concentrate on getting the indexing right. Temp tables can perform better than variables for large datasets. — Nick.Mc, Jul 12 '17 at 22:38
Hey @Nick.McDermaid Thanks a lottt for help . it really worked . :) sorry had Vacations so cpuld not reply early. — swat, Jul 17 '17 at 13:21
Awesome glad to help. If you have time please explain more detail in an answer for anyone else with the same problem. — Nick.Mc, Jul 17 '17 at 13:29

score 1 · Answer 1 · edited Apr 08 '18 at 15:30

I got the Solution by using the temp Table rather than table variable. And the difference in performance is amazing i got the result in less than 5 seconds instead of 2 minutes.

Here is my code for temp Table:

IF OBJECT_ID('DB_DMA.REPORTING.Report_3_1_2_3_RechnungsuebersichtPapier_DTA_Ebene_2_TEMPtest')  


IS NOT NULL DROP TABLE DB_DMA.REPORTING.Report_3_1_2_3_RechnungsuebersichtPapier_DTA_Ebene_2_TEMPtest



        SELECT DISTINCT
  STUFF((
          SELECT ',' + T1.ACAusVertrag
          FROM DB_DMA.REPORTING.Report_3_1_2_3_RechnungsuebersichtPapier_DTA_Ebene_2_TEMP T1
          WHERE
           --    Erfassungsart = '1'
             T1.IKLeistungserbringer = T2.IKLeistungserbringer
             AND T1.Rechnungsnummer = T2.Rechnungsnummer

             ORDER BY T2.IKLeistungserbringer,T1.ACAusVertrag
             FOR XML path('')
       ),1,1,'') AS ACAusVertrag,
       IKLeistungserbringer,Rechnungsnummer

       INTO DB_DMA.REPORTING.Report_3_1_2_3_RechnungsuebersichtPapier_DTA_Ebene_2_TEMPtest 

       FROM DB_DMA.REPORTING.Report_3_1_2_3_RechnungsuebersichtPapier_DTA_Ebene_2_TEMP T2
WHERE T2.Erfassungsart = 1

SQL Server procedure with STUFF taking too much time

1 Answers1