Post reply

Passing XML to Stored Procedure via Text - is this bad?

  • March 30, 2004 at 2:06 pm

    #84859

    Our stored procedures use OPENXML to do inserts, updates and deletes.  We build xml packets and pass them as input parameters to the stored procedures.  Originally we were using VARCHAR, but the 8000 character limit hit us.  I changed the procedures to use TEXT, which works beautifully.  Is this going to be a hog on the server's resources, or is this relatively OK?

  • March 31, 2004 at 2:15 am

    #501242

    Hi Charles,

    If your XML content is around 12MB and above, it will really eat up the memory in your SQL server. Maybe you will encounter OUT OF MEMORY problem.

    You can use MS SQLXML Bulk Load as an alternative solution.

     

  • March 31, 2004 at 8:18 am

    #501297

    The XML will probably run at around 8-15k.  Hopefully that won't be a problem.

  • March 31, 2004 at 9:14 am

    #501314

    Glenn & Charls

    I am using one of such stored procedure which takes XML doc as input and insert records in various tables. Till yesterday it was working fine with response of about 17 seconds for about 4MB of xml doc size. But today when we started running it at higher frequency with lesser

    data size 18-50KB per 10 seconds interval SQL server memory usage went up.

    Question: why does SQL server memory image goes high? it should go back to orginal after the call to stored procedure is completed.

    Please note there is no other process running on this SQL server.

    I would like to brainstorm this concept.

    Thanks

    GOPAL

     

  • March 31, 2004 at 10:51 am

    #501334

    Charles, would you mind posting a sample snippet of code that shows how you're passing a text field in and then accessing it via XML? I've been doing this using varchar() also and couldn't figure out how to make it work with text.

    TIA.

  • April 1, 2004 at 5:29 pm

    #501540

    Hi guys,

    Sorry for the delay...

    Charles,  if your XML ranges from 8K - 15K then there will be no problem.

    GOPAL, With regards to your concern, to be honest with you I really dont know. But I have my own speculation.  SQL Server doesn't free up the resources immediately. It takes some time to decrease the image size.

    Try DBCC DROPCLEANBUFFERS if it can help.

    If I where you use MS SQLXML bulk load its fast and you dont have problems with regards to sqlserver image

    Slider, I think charles uses the text as a parameter in stored procedure.

     

     

     

Viewing 6 posts - 1 through 5 (of 5 total)

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