Done. thanks
Instead of a cursor to walk the code, what about simply using the SELECT statement with an INSERT statement to add the code to the table variable? Better still, why not just a SELECT statement and no table variable at all?
Also, total side note, when a procedure gets compiled, all statements within it get compiled together. When you have a lot of IF/THEN or CASE operations, this becomes an issue because, even if a given statement won't be executed, it will still be compiled. This can lead to problems if the statement gets compiled for NULL or 0 values when, in fact, when it gets called, it will have a value and will return rows. Where applicable, create additional procedures to move statements into a place where they will be compiled independently.
"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
October 8, 2020 at 7:27 pm
Grant ,
I can see now where Cursor is not needed rather use a simple select statement with insert records into the table variable.
Grant, thank you. I will rewrite the script and submit to the developer that asked me help.
October 9, 2020 at 11:11 am
Best of luck on it. You should see a radical improvement in speed.
"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
October 9, 2020 at 12:03 pm
Grant ,
I can see now where Cursor is not needed rather use a simple select statement with insert records into the table variable.
Grant, thank you. I will rewrite the script and submit to the developer that asked me help.
Further on Grant's fine comments, I suggest a simplification by splitting the procedure into three different procedures, multi-purpose code tends to be a second class citizen in the eyes of the query optimizer;-)
😎
October 9, 2020 at 12:16 pm
zocial411 wrote:Grant ,
I can see now where Cursor is not needed rather use a simple select statement with insert records into the table variable.
Grant, thank you. I will rewrite the script and submit to the developer that asked me help.
Further on Grant's fine comments, I suggest a simplification by splitting the procedure into three different procedures, multi-purpose code tends to be a second class citizen in the eyes of the query optimizer;-)
😎
Eirikur is dead on accurate as usual. Primary reason is what I talked about above with how the compile process happens.
"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
October 12, 2020 at 7:22 pm
Hi Grant and Eirikur,
I'm so glad to have your inputs and also I think that Procedure can use spiting. also a good input 'multi-purpose code tends to be a second class citizen in the eyes of the query optimizer;-)'
thanks.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply