Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 5 down vote favorite

We have an 3rd party accounting system that runs on SQL Server 2008 R2. To insert invoices into this accounting system, we have to use the API that comes with it (which is just a set of encrypted SPs).

Some of our batch processes need to insert a large number (1,000s) of invoices. So, ordinarily in SQL you'd write an INSERT statement and they'd get inserted very quickly.

However, because we have to use the SP to insert the rows we currently have a C# application that runs a PARALLEL FOR loop and makes a call to the SP once per iteration.

The performance isn't great and, while much of this is down to what happens inside the API SP, I'm wondering if there's anything SQL Server can do for me to improve performance. I know I could get rid of the C# app and use a CURSOR in T-SQL, but as far as I know, that wouldn't run in parallel - is that correct?

Similarly, I could dynamically generate thousands of SQL statements to call the SP with different parameters and I'd like to think that the optimizer can see what the encrypted SP is doing (certainly better than a C# app) and come up with a plan that will run them all in with as much parallelism as possible.

share|improve this question
3  
Running it in parallel is possible via service broker or SQL Server agent but will be more cumbersome and probably won't bring you any benefit. – Martin Smith Nov 22 '12 at 13:55
3  
I agree with Martin. And the optimizer doesn't really work in a way that it looks inside of a Stored Procedure, sees that you are trying to run many of them and do something different because you are running it multiple times. Sounds like your current method may be the best for now. I'd work with the vendor here and talk to them about your issue. They should create a batch upload process for you that inserts in a more intelligent method for this situation. It is a feature that will help other customers, too. – Mike Walsh Nov 22 '12 at 14:00
@MartinSmith We've looked into service broker and you're right - it probably won't bring any benefit. I'm not sure how we'd use SQL Server Agent though - can you briefly elaborate on that? – Paul Spangle Nov 23 '12 at 9:33
Thanks for all the comments and links. I think we'll continue as we are for now, but we'll discuss it with the vendor at our next meeting with them – Paul Spangle Nov 23 '12 at 16:04

closed as too localized by jcolebrand♦ Nov 23 '12 at 20:56

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, see the FAQ.

1 Answer

up vote 3 down vote

You should consider Table-Valued Parameters and a new stored procedure that can take 1000s of invoices and deal with them as a set rather than the singleton insert you have now. Knowing how to write the procedure to deal with all invoices as a set would require first-hand knowledge of what the procedure does now with a single invoice.

I wrote up a quick article on TVPs here:

http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql

And Erland's article is useful as well:

http://sommarskog.se/arrays-in-sql-2008.html

share|improve this answer
1  
I agree with this approach Aaron - but this is a vendor app/database with encrypted procs. If Paul had access to the app I'd say it was a no brainer, but with it being an accounting package this is one of those things that should really go through the vendor. – Mike Walsh Nov 22 '12 at 15:06
1  
@Mike I missed the encrypted part. Still, the OP could take this suggestion back to the vendor. – Aaron Bertrand Nov 22 '12 at 16:14
agreed. That is one approach for the vendor to consider but the vendor really should offer a batch import process. – Mike Walsh Nov 22 '12 at 16:40
Thanks for all the comments. +1 for the interesting links. I think we'll continue as we are for now, but we'll discuss it with the vendor at our next meeting with them. – Paul Spangle Nov 23 '12 at 9:35

Not the answer you're looking for? Browse other questions tagged or ask your own question.