Performance effect: Calling SP in the same DB VS Calling SP from other DB

Question

Performance effect: Calling SP in the same DB VS Calling SP from other DB

Joe-420121

Hall of Fame

Points: 3152
More actions
May 14, 2012 at 2:38 pm

#260826

will there be any performance impact if SP is called from Same DB or Called from Other DB.
For example:
DB1 has SP1 (Select 'Hello World')
DB2 has SP1 (Select 'Hello World')
Will there be any performace impact:
if I call do in DB1 as below:
EXEC dbo.SP1
EXEC DB2.dbo.SP1
thanks in advance. I am using hello world as example. the SP could have more statements..

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

No.

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

DiverKas SSCrazy Points: 2049 More actions · Answer 2

Yes, depending on what the procs are doing. If they are joining data across servers, then all bets are off. Even if they are processing data on each server locally, the return for the remote call WILL take longer, depending on distance to other server, number of switches, size of return resultset etc.

The answer involves asking a lot more questions to determine what it is exactly you want to do.

Joe-420121 Hall of Fame Points: 3152 More actions · Answer 3

the server is same Local. My question is a kind of theory question.

when SP is called from same DB or the same DB calles the SP that is in another DB, will it be a any impact on performance.

for example: Let us say:

what if XP_cmdShell is in local DB1 instead of Master DB. Performance wise, will there be any differance?

thanks

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

No, there will be no effect from having procedures in DB1 vs DB2 on the same server.

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

DiverKas SSCrazy Points: 2049 More actions · Answer 5

GilaMonster (5/15/2012)
No, there will be no effect from having procedures in DB1 vs DB2 on the same server.

Yup, on same server, no issue.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

DiverKas (5/15/2012)
Yes, depending on what the procs are doing. If they are joining data across servers, then all bets are off. Even if they are processing data on each server locally, the return for the remote call WILL take longer, depending on distance to other server, number of switches, size of return resultset etc.
The answer involves asking a lot more questions to determine what it is exactly you want to do.

While the op didn't actually say, I believe the question was only for across DB's on the same server in which case the answer would be "no".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

Jeff Moden (5/15/2012)
DiverKas (5/15/2012)
Yes, depending on what the procs are doing. If they are joining data across servers, then all bets are off. Even if they are processing data on each server locally, the return for the remote call WILL take longer, depending on distance to other server, number of switches, size of return resultset etc.
The answer involves asking a lot more questions to determine what it is exactly you want to do.
While the op didn't actually say, I believe the question was only for across DB's on the same server in which case the answer would be "no".

Dammit.. did it again. :blush: Gotta remember to "scroll down".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)