8

I want to do something like:

exec sproc1 and sproc2 at the same time
when they are both finished exec sproc3

I can do this in dts. Is there a way to do it in transact sql? Or is there a way to do it with a batch script (eg vbs or powershell)?

Cade Roux
  • 83,561
  • 38
  • 170
  • 259
cindi
  • 4,181
  • 8
  • 28
  • 35
  • It might be easier to answer the question if you would state why, other than it's an interesting question. Then we could address the purpose rather than the technique. – dkretz Nov 10 '08 at 17:28
  • Loading data into a sql-server datawarehouse involves executing many sql statements. The ability to execute statements in parallel would speed up this process. You can do this with DTS, which is a tool. I am one of those people who prefer to use code where possible. – cindi Jan 22 '09 at 16:47

4 Answers4

5

You could create a CLR Stored Procedure that (using C#) would call the first two on their own threads, and then block until both are complete... then run the third one.

Are you able to use CLR sprocs in your situation? If so, I'll edit this answer to have more detail.

Timothy Khouri
  • 29,873
  • 18
  • 80
  • 125
  • thanks Timothy, but I guess I'm just lucking for a high level solution : an extra couple of lines of transact sql or batch script code. I just wonder is there a simple alternative to dts - Cindi – cindi Nov 10 '08 at 16:14
  • Thread.Join is blocked by the host protection attribute. This solution can be made to work, but it is not reliable. Also note that SQL Server can yield your threads whenever it feels like it. – Filip De Vos Sep 15 '11 at 08:54
4

sp _ start _ job

I'm doing a similar thing at the moment, and the only way I've found to avoid using SSIS or some external shell is to split my load routine into 'threads' manually, and then fire a single master sqlagent job which in turn executes as many sp _ start _ job's as I have threads. From that point, they all run autonomously.

It's not exactly what we're looking for, but the result is the same. If you test the job status for the sub jobs, you can implement your conditional start of sproc 3 as well.

What's the point in 8 cores if we can't use them all at once?

Grokling
  • 182
  • 2
  • 9
  • This is great! When each thread finishes I can check each of the other jobs, so the final thread to finish can fire off the next job. – cindi Mar 10 '09 at 11:02
1

Do you absolutely need both SPs to be executed in parallel?

With simple CRUD statements within a single SP, I've found SQL S. does a very good job of determining which of them can be run in parallel and do so. I've never seen SQL S. run 2 SPs in parallel if both are called sequentially from a T-SQL statement, don't even know if it's even possible.

Now then, do the DTS really execute them in parallel? It could be it simply executes them sequentially, then calls the 3rd SP after the last finishes successfully.

If it really runs them in parallel, probably you should stick with DTS, but then I'd like to know what it does if I have a DTS package call, say, 10 heavy duty SPs in parallel... I may have to do some testings to learn that myself :D

Joe Pineda
  • 5,045
  • 3
  • 28
  • 39
  • Splitting a single statement into parallel threads is a different topic. DTS can run seperate statements in parallel, and gives you a lot if control over how this is done. However sometimes code is preferable to a tool, which is the reason for the question. – cindi Jan 22 '09 at 16:40
0

You can use SSIS. The benefits of this are that the package can be stored in the SQL Server and easily scheduled there.

From PowerShell or just about any external scripting language, you can use the SQL command line osql or sqlcmd. This technique can also be used to schedule it on the SQL Server by shelling out using xp_cmdshell also.

Cade Roux
  • 83,561
  • 38
  • 170
  • 259
  • SSIS = DTS, the question was how to avoid DTS Obviously you can run SQL from a scripting language, the question was how to run SQL statments in parallel. – cindi Jan 26 '09 at 10:16