Wednesday, March 28, 2012

how to determine the best timeout value

Hi,
I am trying to insert 75000+ records into a table via a stored procedure
(the table is empty) - the information for all those records is contained in
an xml document that is passed to the stored procedure in a string. I am
using openxml to read the xml data and to insert the records into the table:
The statement is really simple and along the lines of the example below
INSERT INTO TableA
{
SELECT CustomerId,
CustomerName
FROM
OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
WITH
(
CustomerId int 'CustomerId',
CustomerName varchar(100) 'CustomerName'
)
}
The stored procedure is executed by an application using ADO.
Sometimes the execution of this stored procedure exceeds the connection time
out (30s) and a Timeout expired exception is thrown. This happens
intermittently - so I cannot re-produce this problem at will.
It would probably best to insert the records in batches but this cannot be
done for various reasons. The only other option that I can see is to increase
the timeout value - however how do I determine the best value for the
timeout?
If I run the code that executes the stored procedure it executes fine within
the given timeout period (and then sometimes it doesn't and I cannot find the
determinant that would cause it to happen! .v.) ...also I cannot execute
the stored procedure from query analyser etc. as the xml document string is
too long to be supplied as a parameter there - and using a small document
does not cause the problem...
BTW - Has anyone an idea what could be causing the time out in the first
place?
This is driving me insane - Please help anyone?!
Are you sure you are not being blocked when you timeout? Use sp_who2
periodically as the insert is happening to ensure you are not being blocked.
But you should really look at using BULK INSERT instead. This would require
you to convert the format of the file from XML to some type of delimited
file but should yield dramatically faster results.
Andrew J. Kelly SQL MVP
"jalie" <jalie@.discussions.microsoft.com> wrote in message
news:FAF5E01F-F305-4F01-AE7F-1063214EF685@.microsoft.com...
> Hi,
> I am trying to insert 75000+ records into a table via a stored procedure
> (the table is empty) - the information for all those records is contained
> in
> an xml document that is passed to the stored procedure in a string. I am
> using openxml to read the xml data and to insert the records into the
> table:
> The statement is really simple and along the lines of the example below
> INSERT INTO TableA
> {
> SELECT CustomerId,
> CustomerName
> FROM
> OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
> WITH
> (
> CustomerId int 'CustomerId',
> CustomerName varchar(100) 'CustomerName'
> )
> }
> The stored procedure is executed by an application using ADO.
> Sometimes the execution of this stored procedure exceeds the connection
> time
> out (30s) and a Timeout expired exception is thrown. This happens
> intermittently - so I cannot re-produce this problem at will.
> It would probably best to insert the records in batches but this cannot be
> done for various reasons. The only other option that I can see is to
> increase
> the timeout value - however how do I determine the best value for the
> timeout?
> If I run the code that executes the stored procedure it executes fine
> within
> the given timeout period (and then sometimes it doesn't and I cannot find
> the
> determinant that would cause it to happen! .v.) ...also I cannot execute
> the stored procedure from query analyser etc. as the xml document string
> is
> too long to be supplied as a parameter there - and using a small document
> does not cause the problem...
> BTW - Has anyone an idea what could be causing the time out in the first
> place?
> This is driving me insane - Please help anyone?!
>

No comments:

Post a Comment