SQL Server execute several commands simultaneously

Question

I have a stored proc that I need to execute 100 times (one for each parameter). I was wondering if I could execute these all at the same time using batch or something similar so that it would speed up processing instead of executing one and then the next.

Thanks!

This sounds suspiciously like a procedural solution instead of a set based solution. If you show us some code, we might be able to tell you a way that you can call the stored procedure just one time. — Michael Fredrickson
– Michael Fredrickson, Commented Jun 21, 2011 at 14:05
Where are you executing the proc from? Another SQL batch, or some sort of external caller like a (threading-capable...) .NET assembly? Also 100 parallel executions would not necessarily "speed up" processing - have you made sure you're not possibly incurring locks, resource contention, etc. with this proc running a non-trivial number of simultaneous instances? — pelazem
– pelazem, Commented Jun 21, 2011 at 14:06
I have records in a table that I am looping through and running them through a proc. I am dealing with 2 million records and was trying to find the fastest way to get through all the records — Jim
– Jim, Commented Jun 21, 2011 at 14:11

a1ex07 · Accepted Answer · 2011-06-21 14:05:51Z

3

Can you rewrite your procedure to accept TABLE parameter, fill it with 100 values, and process table instead of 100 scalars?

answered Jun 21, 2011 at 14:05

a1ex07

37.4k12 gold badges93 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MatBailie · Accepted Answer · 2011-06-21 14:29:04Z

To directly answer your question, you could open 100 separate connections and execute 100 separate queries concurrently.

But as has been mentioned, I don't think this is the solution for you.

As you have a table with the 100 values, it seems you have a few options...

Change the StoredProcedure to a View, and join on the view.
Change the StoredProcedure to a Table Valued Function, and use CROSS APPLY.
(Inline functions will perform a lot better than Multi-Statement functions.)

These two are limitted by the fact that neither a view nor a function can have any side-effects... No writing to tables, etc, etc.

If you can't refactor your code to use a view or function, you still need a stored procedure to encapsulate the code.

In that case, you could either:
- pass the table of values in as a Table Valued Parameter.
- or simply have the Stored Procedure read from the table directly.

Depending on your needs, you may even wish to create a table specifically for this SP to read from. This introduces a couple of extra issues though...
- Concurrency : How to keep my data separate from someone elses? Have a field to hold a unique identifier, such as the session's @@SPID.
- Clean Up : You don't want processes inserting data all day, but never deleting it.

The one thing I would strongly suggest you avoid is using loops/cursors. If you can find a set based approach, use it :)

EDIT

A comment you just left mentions that you have millions of records to process.

This makes using set based approaches much more preferable. You may, however, find that this creates extremely large transactions (if you're doing a lot of INSERTs, UPDATEs, etc). In which case, still find a set based approach, then find a way of doing this in smaller pieces (say split by day if the data is time related, or just 1000 records at a time, whatever fits.)

Collectives™ on Stack Overflow

SQL Server execute several commands simultaneously

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related