November 5, 2013 at 12:04 am
Comments posted to this topic are about the item How to Design, Build and Test a Dynamic Search Stored Procedure
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 5, 2013 at 1:12 am
Great article, thanks for sharing. I've used similar constructs for flexible SSRS reports. I learned a few nice tricks, I particularly like the @debug and the line numbers, very nice.
Based on Celko's war on dynamic SQL, I wonder if you'll get a response here too. Surely that would be a mark of success 😉
November 5, 2013 at 1:17 am
davoscollective (11/5/2013)
Great article, thanks for sharing. I've used similar constructs for flexible SSRS reports. I learned a few nice tricks, I particularly like the @debug and the line numbers, very nice.Based on Celko's war on dynamic SQL, I wonder if you'll get a response here too. Surely that would be a mark of success 😉
And thank you sir for taking the time to read the article and I'm happy to hear you learned a couple of tricks. I also have to apologize for the SQL formatting issues. I've got an email in to the editor to correct that.
If Joe Celko were to chime in with his thoughts on dynamic SQL, I'd consider it a badge of honor. Dynamic SQL certainly has its place, and this is most certainly one of them. There are cases where it can be overused and/or abused, so a healthy and productive discussion of that (here if necessary) would be most welcome.
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 5, 2013 at 5:35 am
Dwain does it again - nice article sir!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 5, 2013 at 6:20 am
Nice article,
For truly dynamic behavior, do you think moving that responsibility to coding might reduce verbosity, error prone string concatenation, and allow more flexible dynamic searches?
I'm talking about solution s like dynamic linq in c#.
Or using predicates with linq.
That type of solution would be testable using formal unit tests
November 5, 2013 at 6:58 am
A very nice and complete article - thank you for your efforts in creating this.
I like the Excel idea to manage some test cases and data, interesting approach.
Scott
November 5, 2013 at 7:53 am
Hi there,
How do you fix the null value?
Execute dbo.Shipment_Tracking @custID = null
This will give all records
Thanks,
Oded Dror
November 5, 2013 at 11:46 am
Phil Parkin (11/5/2013)
Dwain does it again - nice article sir!
Ditto that! Nicely done, Dwain. Great explanations and revelation of code.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2013 at 1:10 pm
Awesome article. I particularly like using NULLIF to check the search parameters.
Also a reminder that I wish the display more than 8k characters SQL Spackle were default server behavior. Thanks Dwain.
November 5, 2013 at 5:36 pm
Phil, Scott, Jeff and Erik,
Thanks for stopping in to read the article and offer your comments. I hope you find the content useful in your future endeavors.
And to Jeff, special thanks to you sir for taking the time to slog through such a long article clearly not targeted at someone with your level of expertise. Your critical eye is always welcome of course. Keeps me honest.
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 5, 2013 at 5:46 pm
IdRatherCodeIt (11/5/2013)
Nice article,For truly dynamic behavior, do you think moving that responsibility to coding might reduce verbosity, error prone string concatenation, and allow more flexible dynamic searches?
I'm talking about solution s like dynamic linq in c#.
Or using predicates with linq.
That type of solution would be testable using formal unit tests
IRC,
Well I guess I'd have to say "it depends" (to quote one of my SQL Heroes). I think what I did is pretty darn flexible already. Recall what I said at the end, about this being a small (about 20%) subset of the actual functionality delivered by the Production SP.
The thing I don't like about queries embedded in front end apps is that the code is quite obfuscated. Not only (when in an SP) can you pretty clearly read the underlying SQL even though it is embedded in strings, it opens the code up to scrutiny by those that may not have C# skills (like DBAs). The more scrutiny a code undergoes, the likely better it will be. While you could still uncover the underlying code using Profiler, that's an unnecessary hassle as far as I'm concerned.
Furthermore, I think it depends on the confidence you have in your C# development teams. If they're really good and you have plenty of resources available to support an application through multiple platform releases, you may be OK. SQL doesn't change much from version to version. With the exception of assignment on the DECLARE statements, the SQL I've used here is pretty compliant back through SQL 2000 I'd think (although that statement is untested).
Finally, when your testing team uncovers an error, in C# it may require recompiling and an application redeployment, which in production may mean some app downtime. A fix to an SP can be deployed without that, and I'd venture to say finding the bug and fixing it might take a fraction of the time (if in an SP). This also allows retesting the SP using just the @debug facility.
Just a few things to think about.
Edit: An additional point that occurred to me is that if you're talking about web apps here (and I presume you are), constructing very large SQL query strings and then sending them through for execution on the server side increases the amount of data transferred between the client-side and the server. In most cases I'd think that the call to execute the SP would be a much shorter string. Possibly a minor performance improvement.
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 5, 2013 at 5:49 pm
odeddror (11/5/2013)
Hi there,How do you fix the null value?
Execute dbo.Shipment_Tracking @custID = null
This will give all records
Thanks,
Oded Dror
That is by design. Passing a NULL to @CustID is like passing no value, and allowing the default behavior of the SP cover it, i.e., return all Shipment records.
If you are asking how you would check a Shipment for @CustID IS NULL (like in my challenge question at the end), you'd need to do that by specifically looking for "missing status MDE." In other words someone has passed in the @MissingStatus = 'MDE' parameter, so look especially for that and change the appending of the NOT EXISTS code fragment to "AND CustID IS NULL" on the Shipment record.
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 5, 2013 at 7:51 pm
What about
execute [dbo].[Shipment_Tracking] @CustID = 'Apple', @ReferenceNo = null
Will give all record regardless null
Thanks,
Oded Dror
November 5, 2013 at 7:58 pm
odeddror (11/5/2013)
What aboutexecute [dbo].[Shipment_Tracking] @CustID = 'Apple', @ReferenceNo = null
Will give all record regardless null
Thanks,
Oded Dror
Have you tried it? It should only return records with @Custid = 'Apple'
Because @ReferenceNo is passed as NULL, that AND is not added to the WHERE clause.
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 5, 2013 at 9:39 pm
of course I try it will give you two record for Apple one with ReferenceNo SO 1111 and one with null
Look the code doesn't answer the null
Viewing 15 posts - 1 through 15 (of 45 total)
You must be logged in to reply to this topic. Login to reply