Home Forums SQL Server 2008 SQL Server 2008 Administration Query taking over 50 mins; Stuff with XML Path RE: Query taking over 50 mins; Stuff with XML Path

  • October 9, 2013 at 11:36 am

    #1657245

    Eshika (10/9/2013)

    Hi I am using Stuff with XML Path for concatenating the data in multiple columns like city, town etc by Zip code. ie., unique column is Zip and all the cities will be concatenated to one cell, similarly town and so on. The query runs over 1 million data, table has 180 columns.

    I tried -

    Adding indexes to the table, it took 50 minutes to run the query.

    Added a temporary table to get only required 20 columns and added indexes to the temporary table and it helped reduce about 3 minutes.

    Checked the execution plan the issue was with the table scan. There are no filters required to generate this report.

    Can you please suggest what else could be done to improve.

    Thanks

    Esha

    Please see the second link in my signature line below under "Helpful Links".

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)