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


Optimize Update Statement


Optimize Update Statement

Author
Message
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10292 Visits: 5157
Grant Fritchey (1/24/2014)
ChrisM@Work (1/24/2014)
IgorMi, you can use http://extras.sqlservercentral.com/prettifier/prettifier.aspx.

I tend not to. If I'm about to spend a few minutes reading and understanding a chunk of code, reformatting it easily becomes part of the process.



Another option is SQL Prompt from Red Gate. Can't live without that tool.


Yes, I saw it as well. It helps a lot.
Thanks to both!

Igor Micev,
My blog: www.igormicev.com
wit_jp2001
wit_jp2001
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 20
Thanks,
I will try out the new query this afternoon. Just so that eveyone knows this process is to update the 1st SKU with the heaviest SKU. When this system prints a pick ticket it has to find the heaviest sku to determine box size from the start.
But this statement plus a few others just eat away at the time to do the actual run of the code.
Thanks again.
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41248 Visits: 20000
wit_jp2001 (1/24/2014)
Thanks,
I will try out the new query this afternoon. Just so that eveyone knows this process is to update the 1st SKU with the heaviest SKU. When this system prints a pick ticket it has to find the heaviest sku to determine box size from the start.
But this statement plus a few others just eat away at the time to do the actual run of the code.
Thanks again.


I'm not sure what it's meant to do. There's no mention of weight anywhere, and it updates tons of columns, not just the 1st SKU. This description doesn't sit well with the evidence.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
wit_jp2001
wit_jp2001
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 20
The SKU1 is updated with the heaviest sku in the list of SKU's.
The heaviest SKU is predetermined in SKUVAL, the weights are hidden.
So essentially it looks through the list and switches the heaviest with the first sku.

Thanks
wit_jp2001
wit_jp2001
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 20
Chris - Thanks works great.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214018 Visits: 41979
ChrisM@Work (1/24/2014)
Same statement rewritten as UPDATE FROM, with nonsense logic commented out:


While I agree that the commented out code is incorrectly written, I think it should be rewritten with a proper NULL test so that the logic actually does work. The implied logic is that if a particular non-null condition exists, use it. If a NULL condition does exist, preserve the NULL. If neither condition exists, then apply the alternative. The bottom line of what I read as the intent of the code is that if the null conditions exists, then DO NOT apply the alternative using ELSE.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214018 Visits: 41979
wit_jp2001 (1/24/2014)
Chris - Thanks works great.


If your final code is still using an "=" sign for a NULL test, then it might not actually be doing what it's supposed to be doing. If you have "Concatenate NULL Yields NULL" turned off to make such bad code work, then you're going to be in a heap of trouble when MS no longer gives you the option to turn it off.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214018 Visits: 41979
wit_jp2001 (1/24/2014)
Thanks,
I will try out the new query this afternoon. Just so that eveyone knows this process is to update the 1st SKU with the heaviest SKU. When this system prints a pick ticket it has to find the heaviest sku to determine box size from the start.
But this statement plus a few others just eat away at the time to do the actual run of the code.
Thanks again.


What if there's a quantity of 10 of the largest, heaviest SKU that you have and it's the only item for a given order? What then? And what about the problem of packing incompatible items? For example, you wouldn't want to pack an anvil with a carton of eggs (as an example).

Also, what if the "heaviest item" perfectly matches the max capacity of the "container"? You wouldn't want to add other items to the container.

This has all the ear marks of a "bin packing" problem and I don't believe that this code is going to solve it correctly. I also see a major problem in having pivoted the data to 15 stripes instead of processing the data as a single stripe and then pivoting the results.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41248 Visits: 20000
Jeff Moden (1/25/2014)
wit_jp2001 (1/24/2014)
Thanks,
I will try out the new query this afternoon. Just so that eveyone knows this process is to update the 1st SKU with the heaviest SKU. When this system prints a pick ticket it has to find the heaviest sku to determine box size from the start.
But this statement plus a few others just eat away at the time to do the actual run of the code.
Thanks again.


What if there's a quantity of 10 of the largest, heaviest SKU that you have and it's the only item for a given order? What then? And what about the problem of packing incompatible items? For example, you wouldn't want to pack an anvil with a carton of eggs (as an example).

Also, what if the "heaviest item" perfectly matches the max capacity of the "container"? You wouldn't want to add other items to the container.

This has all the ear marks of a "bin packing" problem and I don't believe that this code is going to solve it correctly. I also see a major problem in having pivoted the data to 15 stripes instead of processing the data as a single stripe and then pivoting the results.


The other thread from the same op is concerned with CROSS TABbing normalised SKU data into a structure similar to the sample data here. It would be far cheaper (and simpler) to assign the heaviest SKU to SKU1 prior to CROSS TABbing, and the exercise might hopefully open the door to a proper "bin packing" solution.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
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