November 5, 2013 at 10:00 pm
odeddror (11/5/2013)
of course I try it will give you two record for Apple one with ReferenceNo SO 1111 and one with nullLook the code doesn't answer the null
That is correct. The SP does not support the case of searching for a NULL reference number. Nor a NULL for any of the other possible columns on the Shipment record.
I believe such a scenario would only be possible by making the @ReferenceNo parameter required, then if NULL construct the WHERE clause accordingly.
It would be intriguing to see how you present that search option to a user in the UI.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 6, 2013 at 1:48 am
Jeff Moden (11/5/2013)
Phil Parkin (11/5/2013)
Dwain does it again - nice article sir!Ditto that! Nicely done, Dwain. Great explanations and revelation of code.
I'll second that - and give credit where it's due.
"A friend named Chris Morris once suggested that when writing SQL: “Make it work, make it fast and then make it pretty.” Truer words were never spoken, unfortunately I see a lot of people skipping the last one or two steps. Nicely formatted SQL, with consistent indentation, is much easier for the ordinary mortal to process and understand. I really hate it when I have to wade through what I’d call “stream of consciousness” or write-only SQL. This is when it is appropriate to reinstate firing squads as appropriate punishment."
Credit to Jeff for the original quote, which I've been using ever since it was posted here on ssc - and to you Dwain, for the explanation.
A coworker on a very recent gig wrote only step 1 "expert beginner" code. Truly awful code from a "closed-ear" expert. Apart from that particular idiot and the PM who didn't give a damn, folks are generally very sympathetic to the suggestion - more so if they've had an opportunity to maintain code written with this little gem in mind.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
November 6, 2013 at 3:42 am
ChrisM@home (11/6/2013)
Jeff Moden (11/5/2013)
Phil Parkin (11/5/2013)
Dwain does it again - nice article sir!Ditto that! Nicely done, Dwain. Great explanations and revelation of code.
I'll second that - and give credit where it's due.
"A friend named Chris Morris once suggested that when writing SQL: “Make it work, make it fast and then make it pretty.” Truer words were never spoken, unfortunately I see a lot of people skipping the last one or two steps. Nicely formatted SQL, with consistent indentation, is much easier for the ordinary mortal to process and understand. I really hate it when I have to wade through what I’d call “stream of consciousness” or write-only SQL. This is when it is appropriate to reinstate firing squads as appropriate punishment."
Credit to Jeff for the original quote, which I've been using ever since it was posted here on ssc - and to you Dwain, for the explanation.
A coworker on a very recent gig wrote only step 1 "expert beginner" code. Truly awful code from a "closed-ear" expert. Apart from that particular idiot and the PM who didn't give a damn, folks are generally very sympathetic to the suggestion - more so if they've had an opportunity to maintain code written with this little gem in mind.
I am really going to need to start vetting the provenance more thoroughly when I'm quoting people in the future!
Sorry Jeff, but at least I can say for my ignorance rather than my ill intent.
I'm also going to need find myself more quotable SQL Heroes, so that I don't always have to be quoting Jeff.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 6, 2013 at 4:12 am
dwain.c (11/6/2013)
ChrisM@home (11/6/2013)
Jeff Moden (11/5/2013)
Phil Parkin (11/5/2013)
Dwain does it again - nice article sir!Ditto that! Nicely done, Dwain. Great explanations and revelation of code.
I'll second that - and give credit where it's due.
"A friend named Chris Morris once suggested that when writing SQL: “Make it work, make it fast and then make it pretty.” Truer words were never spoken, unfortunately I see a lot of people skipping the last one or two steps. Nicely formatted SQL, with consistent indentation, is much easier for the ordinary mortal to process and understand. I really hate it when I have to wade through what I’d call “stream of consciousness” or write-only SQL. This is when it is appropriate to reinstate firing squads as appropriate punishment."
Credit to Jeff for the original quote, which I've been using ever since it was posted here on ssc - and to you Dwain, for the explanation.
A coworker on a very recent gig wrote only step 1 "expert beginner" code. Truly awful code from a "closed-ear" expert. Apart from that particular idiot and the PM who didn't give a damn, folks are generally very sympathetic to the suggestion - more so if they've had an opportunity to maintain code written with this little gem in mind.
I am really going to need to start vetting the provenance more thoroughly when I'm quoting people in the future!
Sorry Jeff, but at least I can say for my ignorance rather than my ill intent.
I'm also going to need find myself more quotable SQL Heroes, so that I don't always have to be quoting Jeff.
Nice article, Dwain. I like how you presented your methodical process to handle each step at a time. Hopefully people won't just want to skip to the end before starting at the beginning.
The trick about printing the SQL during debug by SELECTing from DelimitedSplit8K - brilliant. Line numbers in dynamic SQL...what a concept. That's a new technique for me, so thank you for it.
November 6, 2013 at 5:05 am
odeddror (11/5/2013)
What aboutexecute [dbo].[Shipment_Tracking] @CustID = 'Apple', @ReferenceNo = null
Will give all record regardless null
Thanks,
Oded Dror
Are you trying to return only records where ReferenceNo IS null?
Imagine this is the code running behind an application. It is designed to allow the user to type in a reference number or part of a reference number and search for matching records.
The case where a particular customerID has no ReferenceNo is really an Exception report. You would want to do that as part of a checking process to make sure there are no orphaned records. You could easily do that by adding a checkbox to your application with a label "Include records with missing Reference number", map this checkbox to another parameter and use that to modify the dynamic SQL. Any variations on that requirement are also easy to achieve with minor extensions to this code.
Better to have these special requirements as part of a different auditing or checking process, rather than as part of a search tool. As it is, the code works exactly as most people would want: If I type in a Reference number, find me matches, if I don't type something into reference number field then don't filter the results on reference number.
Considering the business application of code, what users expect, and what will cover 90% of usage is an important part of coding effectively.
November 6, 2013 at 6:31 am
Ed Wagner (11/6/2013)
Nice article, Dwain. I like how you presented your methodical process to handle each step at a time. Hopefully people won't just want to skip to the end before starting at the beginning.The trick about printing the SQL during debug by SELECTing from DelimitedSplit8K - brilliant. Line numbers in dynamic SQL...what a concept. That's a new technique for me, so thank you for it.
I am pleased that the SELECT to display the SQL for debugging was received well. Actually it was a bit of lagniappe (a New Orleans expression) that I threw in at the end. The truth be known, I usually just PRINT the SQL. But after doing it a few times I got to thinking how annoying it was when you've got more than 10 lines of SQL to not be able to use the line number that sp_executesql throws at you when there's an error and how useful it would be if you got something like an ambiguous column reference.
So I came up with what I did there. Now I just need to go back and retrofit it to the SPs I've already put into production.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 6, 2013 at 6:49 am
davoscollective (11/6/2013)
odeddror (11/5/2013)
What aboutexecute [dbo].[Shipment_Tracking] @CustID = 'Apple', @ReferenceNo = null
Will give all record regardless null
Thanks,
Oded Dror
Are you trying to return only records where ReferenceNo IS null?
Imagine this is the code running behind an application. It is designed to allow the user to type in a reference number or part of a reference number and search for matching records.
The case where a particular customerID has no ReferenceNo is really an Exception report. You would want to do that as part of a checking process to make sure there are no orphaned records. You could easily do that by adding a checkbox to your application with a label "Include records with missing Reference number", map this checkbox to another parameter and use that to modify the dynamic SQL. Any variations on that requirement are also easy to achieve with minor extensions to this code.
Better to have these special requirements as part of a different auditing or checking process, rather than as part of a search tool. As it is, the code works exactly as most people would want: If I type in a Reference number, find me matches, if I don't type something into reference number field then don't filter the results on reference number.
Considering the business application of code, what users expect, and what will cover 90% of usage is an important part of coding effectively.
Nice explanation sir! Care to be a proof-reader for my next article?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 6, 2013 at 11:21 am
Thanks for your article, I wish I read it before writing my first dySQL procedure 🙂
As for me now I prefer to do full testing of all possible combinations of parameters just to be sure that resulting SQL doesn't have syntax errors.
I declare few table variables with couple of values for every parameter (value/no value) and open a cursor with cross join of them, passing the result of join into my proc (try/catch with error handling is nessesary here - good idea is to put erroneous combinations in some table). As a result I get list of all problematic parameters.
It takes more time because of huge amount of variants (in my case it is more than 1 million combinations) but after full testing I fill more positive while deployment.
November 6, 2013 at 12:31 pm
dwain.c (11/6/2013)
ChrisM@home (11/6/2013)
Jeff Moden (11/5/2013)
Phil Parkin (11/5/2013)
Dwain does it again - nice article sir!Ditto that! Nicely done, Dwain. Great explanations and revelation of code.
I'll second that - and give credit where it's due.
"A friend named Chris Morris once suggested that when writing SQL: “Make it work, make it fast and then make it pretty.” Truer words were never spoken, unfortunately I see a lot of people skipping the last one or two steps. Nicely formatted SQL, with consistent indentation, is much easier for the ordinary mortal to process and understand. I really hate it when I have to wade through what I’d call “stream of consciousness” or write-only SQL. This is when it is appropriate to reinstate firing squads as appropriate punishment."
Credit to Jeff for the original quote, which I've been using ever since it was posted here on ssc - and to you Dwain, for the explanation.
A coworker on a very recent gig wrote only step 1 "expert beginner" code. Truly awful code from a "closed-ear" expert. Apart from that particular idiot and the PM who didn't give a damn, folks are generally very sympathetic to the suggestion - more so if they've had an opportunity to maintain code written with this little gem in mind.
I am really going to need to start vetting the provenance more thoroughly when I'm quoting people in the future!
Sorry Jeff, but at least I can say for my ignorance rather than my ill intent.
I'm also going to need find myself more quotable SQL Heroes, so that I don't always have to be quoting Jeff.
Absolutely no problem. Although I'm tickled that's it's recognized as another "Modenism", the real key isn't so much about who said it but rather that it's being said and some are beginning to follow it. 😀 Well done to both of you for spreading the word and all you do in helping people on these fine forums.
There is a piece missing from it, though, and Dwain actually mentioned the missing piece in his fine article. What I originally said was [font="Arial Black"]"Make it work, make it fast, make it pretty... and it ain't done 'til it's pretty." [/font]:-)
--Jeff Moden
Change is inevitable... Change for the better is not.
November 6, 2013 at 3:13 pm
Is there any reason not to avoid using dynamic SQL here? Performance? Elimination of subqueries/joins (no joins in your example)?
Running the select with conditional comparisons produces the same logic:
.
.
.
AND (
@StartDate is NULL
OR
(a.Last_Status_DT >= @StartDT AND a.Last_Status_DT < @EndDT)
)
.
.
.
November 6, 2013 at 3:25 pm
dwain.c (11/6/2013)
davoscollective (11/6/2013)
odeddror (11/5/2013)
What aboutexecute [dbo].[Shipment_Tracking] @CustID = 'Apple', @ReferenceNo = null
Will give all record regardless null
Thanks,
Oded Dror
Are you trying to return only records where ReferenceNo IS null?
Imagine this is the code running behind an application. It is designed to allow the user to type in a reference number or part of a reference number and search for matching records.
The case where a particular customerID has no ReferenceNo is really an Exception report. You would want to do that as part of a checking process to make sure there are no orphaned records. You could easily do that by adding a checkbox to your application with a label "Include records with missing Reference number", map this checkbox to another parameter and use that to modify the dynamic SQL. Any variations on that requirement are also easy to achieve with minor extensions to this code.
Better to have these special requirements as part of a different auditing or checking process, rather than as part of a search tool. As it is, the code works exactly as most people would want: If I type in a Reference number, find me matches, if I don't type something into reference number field then don't filter the results on reference number.
Considering the business application of code, what users expect, and what will cover 90% of usage is an important part of coding effectively.
Nice explanation sir! Care to be a proof-reader for my next article?
Sure, maybe not as technical proof reader though 😉
November 6, 2013 at 4:54 pm
I'm really new at this, but is there a UI to this article?
November 6, 2013 at 5:17 pm
dratnikova (11/6/2013)
Thanks for your article, I wish I read it before writing my first dySQL procedure 🙂As for me now I prefer to do full testing of all possible combinations of parameters just to be sure that resulting SQL doesn't have syntax errors.
I declare few table variables with couple of values for every parameter (value/no value) and open a cursor with cross join of them, passing the result of join into my proc (try/catch with error handling is nessesary here - good idea is to put erroneous combinations in some table). As a result I get list of all problematic parameters.
It takes more time because of huge amount of variants (in my case it is more than 1 million combinations) but after full testing I fill more positive while deployment.
Some very interesting suggestions here. And from a first time poster too!
Testing all combinations of the input parameters becomes quite a chore as those rise very quickly as more parameters are introduced (2^n-1 if I'm not mistaken). I usually settle for testing the most common combinations but I try to spread them around, e.g., combine a with b, c with d, e with f, and so on.
I do like the idea of logging any invalid combinations that occur. That is magnificent! Perhaps I'll include it in rev 1.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 6, 2013 at 5:19 pm
quagmired (11/6/2013)
Is there any reason not to avoid using dynamic SQL here? Performance? Elimination of subqueries/joins (no joins in your example)?Running the select with conditional comparisons produces the same logic:
.
.
.
AND (
@StartDate is NULL
OR
(a.Last_Status_DT >= @StartDT AND a.Last_Status_DT < @EndDT)
)
.
.
.
Performance is the reason to do it with dynamic SQL. Read the article I quoted by Gail Shaw at the beginning of the article. She covers the suggestion you've made much more thoroughly than I ever could hope to do.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 6, 2013 at 5:21 pm
commish13 (11/6/2013)
I'm really new at this, but is there a UI to this article?
It's a virtual or theoretical UI. 😀
The application basically throws up a form that is scattered with selection criteria, date range, customer ID, customer type, etc. The user fills in the ones that they want. In our case, the business rules implemented in the presentation layer (the UI form) are minimal. Some things like date range is required when no consignment number is entered. That sort of thing.
All the heavy lifting is left to the SP.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 16 through 30 (of 45 total)
You must be logged in to reply to this topic. Login to reply