none
Reducing the Execution time of the stored procedure. RRS feed

  • Question

  • Dear All,

     

    I am trying to parse a huge text files(size: 50kb to 1MB). 

    Currently I have developed a mechanism using regular expressions which

    can group the values in each and every line, finally the values are formatted into a XML string

    which is passed into a stored procedure where the XML is loaded and again by using

    OPENXML statement I read all the values and records are inserted into the tables.

    My problem here is with the execution time of the Stored Procedure which is taking nearly

    45 secs to execute the XML string.

     

    Row Count ranges from 100 to 2000.

    Column count is 40.

     

    Can anyone please suggest me an alternate method or the necessary step which I can

    do with the front end and back end.

     

    Thanks and Regards,

    Valan.

    Monday, August 6, 2007 6:24 AM

All replies

  • I posted here about something similar I did a while back.

    Is it definitely the SP that's taking most of the time? If generating the Xml is slow you could try writing an XSLT transformation to convert the input files straight into XML. However I haven't used XSLT in years and I don't really know if XSLT would be faster.

    I'm guessing that once you've called OpenXML you're using XPath queries to extract the data to insert.

    I never got to the stage of trying to optimise what I did; however a possible option did occur to me. Once you've called OpenXML you could make asynchronous calls to sub-SPs that perform the XPath queries for a specific table and then perform the inserts. If nothing else this would help you take a bigger chunk of the server's resources, and if it's multi-core then you could see a larger benefit.

    This is just an idea; I can't say whether it'll give you what you want.

    In the end you may have to accept that 45 seconds is just how long a job of that size takes.

    Regards,

    Sean
    Monday, August 6, 2007 9:02 PM
  • Thank you Sean,

     

    I all tried to know is that the limitation in the size of a XML that can be used

    in SQL Server 2000. As like you an another programmer suggested me that the maximum

    size of a XML can be 2GB and further he suggested me to use the conventional mechanism

    of generating the insert queries and by using the system stored procedure

    ""sp_executesql"" we can do it in a better way. Let me try.....

     

    Regards,

    Valan.

    Wednesday, August 8, 2007 8:49 AM
  • Do more profiling.  For instance, if it's straightforward to modify your SP so that it takes the XML input and inserts it into a temp table, (and to modify the rest of the SP so that it uses the temp table as input), do so.  Write timestamped trace messages to a trace table after every query in the SP.   This will tell you if there's something about enormous XML documents that's slowing things down, or if the hot spot is somewhere else in your SP.

    Also, it never hurts to look at the execution plan.  It's surprising how often that uncovers things that you weren't thinking about.
    Wednesday, August 8, 2007 9:12 AM