July 20, 2012 at 7:27 am
Not sure I am buying this.
--Performance is better if you have the parm to the right of the equation symbol
@svcdt >= Effective and @svcdt <= Termination
--Should be
Effective <=@svcdt and Termination >=@svcdt
always get a backup before you try that.
July 20, 2012 at 7:35 am
Could be one of those used to be type situations. Perhaps in an older versions of the software (meaning the optimizer) people found that the order of the values actually mattered. Has the software improved these old ways of doing things didn't change as they did test the old presepts with the new software. I'm guilt of that with regard to rebuilding indexes.
July 20, 2012 at 7:36 am
Where did you hear that? There is no truth to that at all. It doesn't even make sense. Why would the optimizer care which order they are in? It is certainly easy enough to disprove. Just create a two queries. One with the parameter each way. Then check out the actual execution plan for each. They will be identical.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 20, 2012 at 7:41 am
My Senior DBA who reviews my code. I just wanted to know if it was true or not. I need to follow the sr. DBA standard but wanted to know if it was factual before I commited that as a truth or a group standard make sense?
always get a backup before you try that.
July 20, 2012 at 7:44 am
Mountain Steve (7/20/2012)
My Senior DBA who reviews my code. I just wanted to know if it was true or not. I need to follow the sr. DBA standard but wanted to know if it was factual before I commited that as a truth or a group standard make sense?
I do tend to think that it makes code easier to read for us mortals but the optimizer doesn't care.
declare @MyTest int = 436
select * from Tally where N < @MyTest
select * from Tally where @MyTest < N --identical but makes me pause to look at it
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 23, 2012 at 3:04 am
We seem to be overlooking the cardinality rule of comments:
"Comments in code are always out of date and hence inaccurate."
Could be this used to apply but does no more.
I like the parameter on the right side for readability too, as Sean has suggested.
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
July 23, 2012 at 2:28 pm
@ Sean
surely you meant
declare @MyTest int = 436
select * from Tally where N < @MyTest
select * from Tally where @MyTest > N --identical but makes me pause to look at it
instead of
declare @MyTest int = 436
select * from Tally where N < @MyTest
select * from Tally where @MyTest < N --identical but makes me pause to look at it
@steve-2, just ignore it. Really doesn't make any difference. If you're on good terms with your snr DBA, talk to him about it (and show him that the query plans aren't any different). If you are not, just go with the flow, then change the rules once YOU are the Snr DBA 😎 And fix the comments...
July 23, 2012 at 2:35 pm
No worries people. I just many times like to make sure something is true before I add it to my knowledge. We are on good terms but the Sr. DBA is sometimes reluctant to make changes.
thanks for the input!
oh and the comments were to make it easier for us to read and aren't in the actual code.
always get a backup before you try that.
July 23, 2012 at 2:37 pm
Jan Van der Eecken (7/23/2012)
@ Seansurely you meant
declare @MyTest int = 436
select * from Tally where N < @MyTest
select * from Tally where @MyTest > N --identical but makes me pause to look at it
instead of
declare @MyTest int = 436
select * from Tally where N < @MyTest
select * from Tally where @MyTest < N --identical but makes me pause to look at it
@steve-2, just ignore it. Really doesn't make any difference. If you're on good terms with your snr DBA, talk to him about it (and show him that the query plans aren't any different). If you are not, just go with the flow, then change the rules once YOU are the Snr DBA 😎 And fix the comments...
Well I suppose if you want it to be correct. 😀 Yes in fact that is what I meant. Good catch.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 23, 2012 at 5:07 pm
Mountain Steve (7/23/2012)
No worries people. I just many times like to make sure something is true before I add it to my knowledge. We are on good terms but the Sr. DBA is sometimes reluctant to make changes.thanks for the input!
oh and the comments were to make it easier for us to read and aren't in the actual code.
"A Developer must not guess... A Developer must KNOW!" 😉
To that end, even if a bunch of very well meaning folks insist on one thing or another, I always like to find out myself. To do such performance proofing, you need lots and lots of data.
Here are some relatively easy and quick ways to generate more data than you can shake a stick at...
http://qa.sqlservercentral.com/articles/Data+Generation/87901/
http://qa.sqlservercentral.com/articles/Test+Data/88964/
And get out of the habit of trusting supposedly identical query plans to decide. Query plans aren't always accurate (less often than not, IMHO) for % of batch and/or cost even between supposedly identical plans.
--Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply