Post reply

SQL job referring to a procedure from user DB from "master"

  • June 30, 2015 at 4:49 pm

    #299708

    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

    #1809455

    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

    #1809523

    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.

  • July 1, 2015 at 11:40 pm

    #1809715

    Thanks guys.

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

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