Concurrent Stored Procedures

Question

Concurrent Stored Procedures

pgmoschetti

SSC-Addicted

Points: 487
More actions
September 9, 2014 at 3:02 am

#310851

I'm developing a database where one client will call 4 stored procedures one after the other - the client program calls will be something like this:
SQLExec("sp1");
SQLExec("sp2");
SQLExec("sp3");
SQLExec("sp4");
Each SP will have to:
- look into a common table and search for a common item (common means shared between the 4 stored procedures)
- if that item is not there already, insert the item, and then use it
- otherwise if the item is there, just read and use it
My question is about how sql server handles the 4 incoming sp calls: multi-threading or serialized?
In the first case (i.e. multi-threading), I have a big problem, because of that shared item!
Thank you in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246963 More actions · Answer 1

This depends on how 'SQLExec' is implemented. More details needed about that before we can answer.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

pgmoschetti SSC-Addicted Points: 487 More actions · Answer 2

Thank you Phill. What I know about the client is as follows:

- it's a SCADA software (Citect SCADA)

- the SQLExec is using ADO.NET behind the scene

- the four SQLExec are issued on four different threads

Thank you again for your reply

Phil Parkin SSC Guru Points: 246963 More actions · Answer 3

pgmoschetti (9/9/2014)
Thank you Phill. What I know about the client is as follows:
- it's a SCADA software (Citect SCADA)
- the SQLExec is using ADO.NET behind the scene
- the four SQLExec are issued on four different threads
Thank you again for your reply

Then I think it's bad news - the four threads will execute, or attempt to execute, the procs in parallel.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

pgmoschetti (9/9/2014)
- the four SQLExec are issued on four different threads

Then they will be run in parallel because SQL will just see 4 connections and 4 separate requests

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass