SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored Procedure


Stored Procedure

Author
Message
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (405K reputation)SSC Guru (405K reputation)SSC Guru (405K reputation)SSC Guru (405K reputation)SSC Guru (405K reputation)SSC Guru (405K reputation)SSC Guru (405K reputation)SSC Guru (405K reputation)

Group: General Forum Members
Points: 405916 Visits: 43734
One other thing I dislike is differed binding. What is that you ask? This is using differed binding: select top 1 * from CoreHistory..PRODUCT where PRODUCT_ID = c.PRODUCT_ID order by SESSION_ID desc. Instead of specifying the schema of the table PRODUCT in the CoreHistory database you have used the .. notation. This will default to dbo except if the user running this query has a different default schema. Unless you need this functionality you really should explicitly specify the schema.

Other than that, I haven't looked through the code as it is long and I am at work so no time at them moment (maybe later or when I get home). If you could post the actual execution plan for the execution of the procedure (as a .sqlplan file), that I would look at quicker than the code at the moment.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223766 Visits: 25288
Lynn Pettis - Friday, January 11, 2019 7:11 AM
One other thing I dislike is differed binding. What is that you ask? This is using differed binding: select top 1 * from CoreHistory..PRODUCT where PRODUCT_ID = c.PRODUCT_ID order by SESSION_ID desc. Instead of specifying the schema of the table PRODUCT in the CoreHistory database you have used the .. notation. This will default to dbo except if the user running this query has a different default schema. Unless you need this functionality you really should explicitly specify the schema.

Other than that, I haven't looked through the code as it is long and I am at work so no time at them moment (maybe later or when I get home). If you could post the actual execution plan for the execution of the procedure (as a .sqlplan file), that I would look at quicker than the code at the moment.

Did you mean deferred binding, by any chance?
I definitely agree that it should be avoided unless necessary.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)

Group: General Forum Members
Points: 365452 Visits: 34534
Yikes. Just glanced back through and spotted this:
isnull(c.DBAction, '') != ''

That's another performance killer. You have a ton of tuning opportunities here without even looking at execution plans for all these queries. I would also examine whether or not all the manipulation of the data in temp tables is necessary. How much of that can be done in one query (note, I'm not advocating for a single query to do this, just some reduction in the quantity) instead of a hundred separate calls. After fixing the bad code smells and reducing the work, I'd break out the execution plans to understand how the optimizer is treating the resulting queries. However, at this point, too much easy work is available to start sweating execution plans.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
ZZartin
ZZartin
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27206 Visits: 17814
I'm also seeing a ton of things like this PARTY_ID > 0 in statements that are creating temp tables. Is that an identity field? If so aren't you basically just selecting the entire table?
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (405K reputation)SSC Guru (405K reputation)SSC Guru (405K reputation)SSC Guru (405K reputation)SSC Guru (405K reputation)SSC Guru (405K reputation)SSC Guru (405K reputation)SSC Guru (405K reputation)

Group: General Forum Members
Points: 405916 Visits: 43734
Phil Parkin - Friday, January 11, 2019 7:27 AM
Lynn Pettis - Friday, January 11, 2019 7:11 AM
One other thing I dislike is differed binding. What is that you ask? This is using differed binding: select top 1 * from CoreHistory..PRODUCT where PRODUCT_ID = c.PRODUCT_ID order by SESSION_ID desc. Instead of specifying the schema of the table PRODUCT in the CoreHistory database you have used the .. notation. This will default to dbo except if the user running this query has a different default schema. Unless you need this functionality you really should explicitly specify the schema.

Other than that, I haven't looked through the code as it is long and I am at work so no time at them moment (maybe later or when I get home). If you could post the actual execution plan for the execution of the procedure (as a .sqlplan file), that I would look at quicker than the code at the moment.

Did you mean deferred binding, by any chance?
I definitely agree that it should be avoided unless necessary.

Yes, can't seem to type today.


Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search