Are the posted questions getting worse?
-
WayneS (8/4/2010)
Jack Corbett (8/4/2010)
Just made my first "real" forum post in awhile. Had to pick a thread where Mr. Celko has already weighed in. http://www.sqlservercentral.com/Forums/Topic963903-391-1.aspxThen it looks like you went on a roll and posted another half-dozen posts (that I saw)!
Welcome back! :w00t:
Well, I should be either sleeping, writing a blog post, working, or prepping for my PASS Nominating Committee interview, but I got started with the forums and couldn't stop!:w00t:
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Jack Corbett (8/4/2010)
WayneS (8/4/2010)
Jack Corbett (8/4/2010)
Just made my first "real" forum post in awhile. Had to pick a thread where Mr. Celko has already weighed in. http://www.sqlservercentral.com/Forums/Topic963903-391-1.aspxThen it looks like you went on a roll and posted another half-dozen posts (that I saw)!
Welcome back! :w00t:
Well, I should be either sleeping, writing a blog post, working, or prepping for my PASS Nominating Committee interview, but I got started with the forums and couldn't stop!:w00t:
Do we need to have an intervention and an introduction to the 12-step program?:-D
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Brandie Tarvin (8/4/2010)
Jack Corbett (8/4/2010)
Probably the most complex SQL I've had to write. Too many OUTER JOINs (business requires this design), and then parameters that could turn the OUTER JOINs to INNER JOINs and may not be what the business wants in some cases.Are you putting the parameters in the WHERE clause or in the ON statements?
I know you know this, but since you said your brain was fried... Remember that putting the parameters in the ON statements of your OUTER JOINS prevents them from becoming a "hidden" INNER JOIN.
Is this really true? After reading Kimberly Tripp's post here:http://www.sqlskills.com/BLOGS/KIMBERLY/post/Determining-the-position-of-search-arguments-in-a-join.aspx (followup to her original post here: http://www.sqlmag.com/blogs/sql-server-questions-answered/sql-server-questions-answered/tabid/1977/entryid/12626/Default.aspx) I basically stopped messing with criteria in my joins, as my understanding was that if it's not an inner join, you're not helping, and if it changes anything at all, it screws you up. (at least that's what I found when I tried to test it)
Would appreciate any clarification you guys can give me on this one.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
-
WayneS (8/4/2010)
Steve Jones - Editor (8/4/2010)
I vote no. I like my watch. I don't want to have to buy a new one that has 10 digits.Plus I have this cool clock:
Okay, looks like I'm getting a new clock for the office! :w00t:
I like this one better, although I did print out Steve's, cut out the middle so you could see through it and tape it over the clock in our copy room. :hehe:
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
-
Steve Jones - Editor (8/2/2010)
In case anyone needs a humorous break: http://www.youtube.com/watch?v=KiFKm6l5-vEI was finally able to watch that last night. Reminds me of some of my end users. @=)
-
Jon,
Where you place the criteria for OUTER JOIN's DOES make a difference. Whenever you place criteria in the WHERE clause that references a column in your OUTER table SQL Server will convert that JOIN to an INNER JOIN. This is because of when the criteria is evaluated. I don't have the order of evaluation memorized but it does make a difference. Itzik covers this in one of his books, I think it is Professional T-SQL Programming, but not sure.
Itzik also had a great tips and tricks session where he showed order of JOIN criteria can make a difference as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
jcrawf02 (8/5/2010)
Brandie Tarvin (8/4/2010)
Jack Corbett (8/4/2010)
Probably the most complex SQL I've had to write. Too many OUTER JOINs (business requires this design), and then parameters that could turn the OUTER JOINs to INNER JOINs and may not be what the business wants in some cases.Are you putting the parameters in the WHERE clause or in the ON statements?
I know you know this, but since you said your brain was fried... Remember that putting the parameters in the ON statements of your OUTER JOINS prevents them from becoming a "hidden" INNER JOIN.
Is this really true? After reading Kimberly Tripp's post .... I basically stopped messing with criteria in my joins, as my understanding was that if it's not an inner join, you're not helping, and if it changes anything at all, it screws you up. (at least that's what I found when I tried to test it)
Would appreciate any clarification you guys can give me on this one.
I have not read these articles yet. I will when I get my daily work done. However, in my experience, adding filters to the ON part of a JOIN statement can be extremely beneficial.
Here's the best example my tired brain can come up with. Customer says "I want StoreNumber, StoreName, General Manager Name, and EstablishDate for all stores in Florida. And I want the franchise amounts due for any unpaid royalties."
Here's the table structure:
Create Table dbo.Store (StoreID int, StoreName varchar(50), GMName varchar(100),
EstablishDate datetime);
Create Table dbo.FranchiseDetails (FranchiseID int, StoreID int, RoyaltyWeek datetime,
RoyaltiesPaid money, RoyaltiesDue money);
Bear in mind that these tables are loosely based on a RL example I encountered but are not 100% accurate due to poor memory. Join code is below.
--This is the hidden INNER JOIN Code
--that will only get me those stores that owe Royalties. Unless all stores in Florida owe,
--then there will be missing records.
Select t1.StoreID, t1.StoreName, t1.GeneralManager, t1.EstablishDate, t3.RoyaltiesDue
from StoreInfo t1
LEFT OUTER JOIN FranchiseDetails t3
on t1.StoreID = t3.StoreID
where t1.StoreState = ‘FL’ and t3.RoyaltiesDue > 0.00;
--Code that will get me all stores in Florida and then print RoyaltiesDue for ONLY
--the stores that owe Royalties
Select t1.StoreID, t1.StoreName, t1.GeneralManager, t1.EstablishDate, t3.RoyaltiesDue
from StoreInfo t1
LEFT OUTER JOIN FranchiseDetails t3
on t1.StoreID = t3.StoreID
and t3.RoyaltiesDue > 0.00
where t1.StoreState = ‘FL’;
Does that make sense to you?
-
jcrawf02 (8/5/2010)
Brandie Tarvin (8/4/2010)
Jack Corbett (8/4/2010)
Probably the most complex SQL I've had to write. Too many OUTER JOINs (business requires this design), and then parameters that could turn the OUTER JOINs to INNER JOINs and may not be what the business wants in some cases.Are you putting the parameters in the WHERE clause or in the ON statements?
I know you know this, but since you said your brain was fried... Remember that putting the parameters in the ON statements of your OUTER JOINS prevents them from becoming a "hidden" INNER JOIN.
Is this really true? After reading Kimberly Tripp's post here:http://www.sqlskills.com/BLOGS/KIMBERLY/post/Determining-the-position-of-search-arguments-in-a-join.aspx (followup to her original post here: http://www.sqlmag.com/blogs/sql-server-questions-answered/sql-server-questions-answered/tabid/1977/entryid/12626/Default.aspx) I basically stopped messing with criteria in my joins, as my understanding was that if it's not an inner join, you're not helping, and if it changes anything at all, it screws you up. (at least that's what I found when I tried to test it)
Would appreciate any clarification you guys can give me on this one.
In a perfect world, the placement of the conditions in the ON or WHERE clause in INNER JOINs makes absolutely no difference. Same thing with the tables in JOIN order.
Let me know when you find the perfect world. On this planet, most of the time, that holds true. But... and you knew there was one of those coming, in certain situations you can help the optimizer by rearranging JOIN order or placing the criteria in the ON clause instead of the WHERE clause. I only ever try that in complicated situations though. If you're looking at a straight forward set of joins with straight forward JOIN conditions and straight forward WHERE conditions, I'd put the JOIN conditions in the ON clause and filtering conditions in the WHERE clause, by default. It won't change the execution and it makes for more clarity in the code.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Praveen Goud Kotha (8/5/2010)
Thank you all the members for their replies/suggestions who participated in my topic..especially for Fritchey..
If any other reasons for "Reasons for difference in execution plans"..
Please dont forget to post here..
what i mean to say is: i have solved my issue(it is because of difference in service packs(2 and 3) )..
but more answers are welcomed to help others too...
Not especially me, especially Gail. She had the definitive list.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Right, that last post, that was to a completely different thread... uh, Steve, DBCC Timewarp is acting up or something.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Grant Fritchey (8/5/2010)
uh, Steve, DBCC Timewarp is acting up or something.Maybe you need to clear the warp cache on your local machine. It seems to be working just fine for me.
Also, check your DMVs. sys.dm_io_chrono_checkalloc might be acting up.
-
Grant Fritchey (8/5/2010)
Right, that last post, that was to a completely different thread... uh, Steve, DBCC Timewarp is acting up or something.I bet you somebody has set DBCC TimeWarp to run on Metric time. :angry:
Alvin Ramard
Memphis PASS Chapter[/url]All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
-
Alvin Ramard (8/5/2010)
Grant Fritchey (8/5/2010)
Right, that last post, that was to a completely different thread... uh, Steve, DBCC Timewarp is acting up or something.I bet you somebody has set DBCC TimeWarp to run on Metric time. :angry:
Don't look at me, when it comes to changing settings I need to look things up and I can't find the documentation on DBCC TimeWarp OR Metric time! : -)
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams -
Grant Fritchey (8/5/2010)
Praveen Goud Kotha (8/5/2010)
Thank you all the members for their replies/suggestions who participated in my topic..especially for Fritchey..
If any other reasons for "Reasons for difference in execution plans"..
Please dont forget to post here..
what i mean to say is: i have solved my issue(it is because of difference in service packs(2 and 3) )..
but more answers are welcomed to help others too...
Not especially me, especially Gail. She had the definitive list.
but, but, but.... You're the *celebrity*
(sorry, couldn't resist)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Jack Corbett (8/5/2010)
...Where you place the criteria for OUTER JOIN's DOES make a difference. Whenever you place criteria in the WHERE clause that references a column in your OUTER table SQL Server will convert that JOIN to an INNER JOIN...
It makes perfect sense, especially when you consider this as well:
LEFT JOIN TableB b ON b.SomethingOrOther = a.SomethingOrOther
WHERE b.SomethingOrOther IS NULL
Having any expression referencing a column of the outer-joined table, other than a NULL-check, in the WHERE clause, forces it to match rows from the outer-joined table. Write it as an inner-join instead. But we all know that.
What creeps me out is when I left-join a child table to a parent, then inner join child to a grandchild, with no reference to either the child or the grandchild in the WHERE clause, and the join between child and parent automagically becomes an inner join.
I can't predict whether or not the join between the parent and child will remain as an outer join, which it sometimes appears to do, or convert to an inner join, as it seems to, erm... when it feels like it. When I'm working with a query which does this conversion, I'll often set the child/grandchild inner join up in a derived table and then left join to parent, after comparing performance against the alternative which is to outer join child and grandchild.
/ creep over. Am I being dumb here?
Edit: right brain on work, left brain on SSC
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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
Viewing 15 posts - 17,116 through 17,130 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply