Post reply

XML data import with poor stored procedure performance (needs optimization)

  • April 17, 2025 at 2:43 pm

    Go to Answer

    #4580536

    Hello SSC!

    I have been tasked with optimizing a stored procedure the imports data from XML to SQL tables. This SP is executing many child stored procedures and is using dynamic SQL. So, as you can imagine, performance is awful. All of our data is imported through XML files and there are a lot of them.  Below are the options that I found, but if someone has a better/quicker solution I would love to hear it. Of course, this needs to be done ASAP, so I am at least looking for a solution to get approved and then can implement this later.

    Solution possibilities:

    1. Re-write the procedure in Powershell. My Powershell knowledge is limited, so this could take a very long time.
    2. SQLXML 4.0 - I am in the process of reading up on this, but I am not sure if this is just a driver or if the stored procedure needs to be altered in some way.
    3. Zappysys - I found this online, but you have to pay for it. Although my company will pay, I would imagine there is a learning curve.

    Any assistance would be greatly appreciated!

    Dave

    • This topic was modified 4 weeks, 1 day ago by  Lord Slaagh.

    The are no problems, only solutions. --John Lennon

  • April 17, 2025 at 4:25 pm

    #4580567

    Try google with these search terms sql shredding xml to tables

    I haven't done this for quite some time, but that's how I found a performant method the last time I needed to do this

  • April 18, 2025 at 11:43 am

    Answer

    #4580903

    You can try the following approaches to optimize the stored procedure:

    Optimize XML Parsing: Use FOR XML PATH or OPENXML for better XML parsing, or use SQL Server’s xml data type for efficiency.

    Bulk Insert: Instead of row-by-row inserts, break XML files into chunks and use bulk insert for faster data loading.

    SQLXML 4.0: This driver could improve XML handling without major changes to your procedure.

    ZappySys: If the company approves the cost, this tool can help speed up the process with less effort, despite the learning curve.

    Focus on optimizing XML parsing first, then consider SQLXML 4.0 or a tool like ZappySys for a more efficient solution.

  • April 21, 2025 at 8:47 pm

    #4582138

    We are going to try SQLXML 4.0. It seems to be what we need. We have one master stored procedure that is calling 15 child procs that all have dynamic SQL. Querying the XML directly without DS should improve performance by a lot and all I will have to do is remove the ticks and adjust the variables... Small potatoes.

    As always thank you all for your assistance, and I will update this ticket with my test results to help other users.

    Have a great week!

    Dave

    The are no problems, only solutions. --John Lennon

Viewing 4 posts - 1 through 3 (of 3 total)

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