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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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