August 15, 2012 at 11:26 pm
rajawat.niranjan (8/15/2012)
Thanks dwain.cI have following queries regarding this:
1. Here I don't have all parameters compulsory except city, so if i use this single query every time I will be join all the tables whether there is some value in parameter or not. If I use If-else then I guess there will be many iterations.
2. Does this query give better performance compared to previous dynamic query?
Answers:
1. The code I provided uses no IF-ELSE.
2. I don't know and I previously suggested you try it and let us know.
In my original posting, when I suggested the IF-ELSE approach, it was based on an assumption that you didn't pass in multiple bits of query criteria. That in each case, you'd pass in only city, province, country, or whatever (but only one). Because you pass in multiple parameters on occasion, it is not possible to use the IF-ELSE approach I suggested, hence the "catch-all" query I provided.
As I said, I'm not sure it will be better but a performance test would tell you for sure.
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
August 16, 2012 at 5:11 am
Thank you dwain.c
While using sp_executesql dynamic sql working well. I will be preferring dynamic SQL here.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply