SQL Server execute several commands simultaneously

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 us开发者_如何学JAVAing batch or something similar so that it would speed up processing instead of executing one and then the next.

Thanks!

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

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.)