M
mj2736
In our .Net 2.0 web service we need to insert/update data records into
SQL Server 2005 that have been uploaded to us in XML documents. There
could be anywhere from one to literally thousands of records in each
uploaded transaction, and there are several dozen destination tables.
Since performance is critical for this particular app, we were
thinking of combining the stored procedure calls into configurable-
sized batches using CommandType.Text and multiple concatenated EXEC
statements, so we minimize database roundtrips as opposed to calling
each SP individually in its own SqlCommand.
Is this a good approach? One concern is that SQL Server would have to
compile each and every batch since they will all be different, and the
overhead involved in this could become significant. Is this really
something to worry about, or not? Wouldn't a few batch compiles per
transaction still be better than potentially thousands of roundtrips?
Are there other problems with this approach?
Of course we are planning to stress test to see how it actually
responds, but I just wanted to get some opinions first in case someone
has done this before and can give advice/recommendations/suggestions
before we get too far into it.
Thanks!
MJ
SQL Server 2005 that have been uploaded to us in XML documents. There
could be anywhere from one to literally thousands of records in each
uploaded transaction, and there are several dozen destination tables.
Since performance is critical for this particular app, we were
thinking of combining the stored procedure calls into configurable-
sized batches using CommandType.Text and multiple concatenated EXEC
statements, so we minimize database roundtrips as opposed to calling
each SP individually in its own SqlCommand.
Is this a good approach? One concern is that SQL Server would have to
compile each and every batch since they will all be different, and the
overhead involved in this could become significant. Is this really
something to worry about, or not? Wouldn't a few batch compiles per
transaction still be better than potentially thousands of roundtrips?
Are there other problems with this approach?
Of course we are planning to stress test to see how it actually
responds, but I just wanted to get some opinions first in case someone
has done this before and can give advice/recommendations/suggestions
before we get too far into it.
Thanks!
MJ