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.