Post reply

Performance issues when BLOB-shredding....

  • November 1, 2016 at 8:46 am

    #331701

    Hi all,

    I have tried to get an insert query to execute faster.

    I have a table with an xml-column in it. This xml is quite big and there are about 6.00 rows in the table. Looks something like in the attached query but with much larger xml and many more rows to import.

    The attached query executes in about 9 seconds and the main Performance query is the last query that shredds the xml.

    I have tweaked it with some indexes on the temp-table but in want it to execute faster.

    Is there another way to go about this?

    Thanks !

  • November 1, 2016 at 9:28 am

    #1909907

    mickegohle (11/1/2016)

    Hi all,

    I have tried to get an insert query to execute faster.

    I have a table with an xml-column in it. This xml is quite big and there are about 6.00 rows in the table. Looks something like in the attached query but with much larger xml and many more rows to import.

    The attached query executes in about 9 seconds and the main Performance query is the last query that shredds the xml.

    I have tweaked it with some indexes on the temp-table but in want it to execute faster.

    Is there another way to go about this?

    Thanks !

    There really isn't any way to speed this up significantly; extracting data from XML is an expensive operation. You are pulling several columns. The only way to make this any faster would be to eliminate columns you are returning.

    The best way to approach this is to store that XML as relational data. If it's possible you want to use your query's logic to populate and maintain your data in SQL format.

    Edit: minor typo

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply