February 28, 2008 at 9:36 am
The is a job running a SQL script. Inside the script, there are some variables that need to set every time it runs so I recommend to change to stored procedure with parameters.
Actually is there a difference in performance in running script vs stored procedure?
February 28, 2008 at 10:52 am
It really depends on the SQL statement in question. To a large degree, depending on what the SQL is doing, there is no substantive difference between a script and a procedure. However, if you do have to mess with parameters, setting it into a procedure is the better way to go.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 28, 2008 at 11:14 am
Actually it is a very large script. It extracts data from multiple tables from one database, does some checking and cleansing work and then put them into multiple tables in another database. The two databases are in different servers.
February 28, 2008 at 11:24 am
If it's that big & complex, script or proc probably won't make a difference in performance terms. The procedure is likely to be easier to maintain since you can set parameters & store it seperately from the job that is running it.
Considering the size and complexity, might it be better to look at moving the process to SSIS? I'm not proposing it, I'm just curious if that was considered.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 28, 2008 at 1:53 pm
The script was written by a web programmer. It had cursor within cursor within cursor. The company never bothered to hire a SQL server DBA or developer until now (me). The script ran fine if nothing was running otherwise it got time out and everything. So I am in the midst of re-writing it. Since I am not familiar with the whole process, I just replace the cursor with select statement.
February 29, 2008 at 6:35 am
Cool. Sounds like you're in the right track.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply