June 30, 2015 at 4:49 pm
Hi all,
I am working on a MONTHLY SQL job which runs a stored procedure for archiving data (volume of data could be half million records). The stored procedure is in a user database. The job step would be of "T-SQL" type.
One way of doing the job step is to execute the stored procedure directly in the user database
- by selecting the user database in the step and just have the script has "EXEC dbo.usp_Name"
Another way is to execute it in master DB.
- by selecting "master" database in the step and have the script as "EXEC USER_DB.dbo.usp_Name"
Do you guys think this would have any impact in performance?
It may not make big/ any difference. But I am just curious to know if there could be any variation in performance when the current DB is master.
Regards
July 1, 2015 at 6:47 am
I dont think it will have any impact on performance, but option two would be better in terms of maintenance and clarity as it is more specific. It will always run the procedure that resides in the correct database and there is no risk that an incorrect procedure could be called.
July 1, 2015 at 8:47 am
Should be no real performance impact. It's a minor security check across databases, but it should be overwhelmed by the time for the job.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 1, 2015 at 11:40 pm
Thanks guys.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply