July 30, 2014 at 3:28 pm
Evil Kraig F (7/30/2014)
churlbut (7/30/2014)
Hello,We just had a meeting a few months ago given by a DBA to us developers...
Big advice to us was to take the Items in a WHERE Clause (if you can), apply the logic to JOINS.
Whut? Why would he recommend that? WHERE or ON clause basically only matters as to the application of OUTER JOIN logic. Predicates are predicates. What reasoning and examples did he use to support this statement?
I have heard others regurgitate that same myth. Not sure where it came from or why it perpetuates. :w00t:
_______________________________________________________________
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 30, 2014 at 3:34 pm
Sean Lange (7/30/2014)
Evil Kraig F (7/30/2014)
churlbut (7/30/2014)
Hello,We just had a meeting a few months ago given by a DBA to us developers...
Big advice to us was to take the Items in a WHERE Clause (if you can), apply the logic to JOINS.
Whut? Why would he recommend that? WHERE or ON clause basically only matters as to the application of OUTER JOIN logic. Predicates are predicates. What reasoning and examples did he use to support this statement?
I have heard others regurgitate that same myth. Not sure where it came from or why it perpetuates. :w00t:
It's nuts. If anything, it's backwards. A subselect in an ON clause has the possibility of running for every row if it's non-deterministic (an honest mistake by an uninformed coder), whereas the WHERE clause should run and cache since you have to make it deterministic (well, as per the query parameters, just not row dependent) so the query runs right.
Gyeah. No no no. Churlbut, please get your DBA on here and start up a thread on this. I'd love to see his justifications for this and be able to work with him to dismiss this myth, at least for one person. Gotta start somewhere.
EDIT: On a side note, I was debating on if I should do another article, this one for tech instead of soft stuff. I may have found a topic...
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 30, 2014 at 3:37 pm
Evil Kraig F (7/30/2014)
EDIT: On a side note, I was debating on if I should do another article, this one for tech instead of soft stuff. I may have found a topic...
Sounds like a good one. I will help proofread if you want. 😛
_______________________________________________________________
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 30, 2014 at 3:55 pm
I suspect it's because SQL's internal processing does "ON" clauses before WHERE clauses.
Therefore, I think they figure the ON clause might eliminate rows sooner than a WHERE clause, and avoid joining unwanted rows. To me, that's a logical goal, then, as far as it goes. But, I know that SQL will pull comparison values out of the WHERE and include them on the underlying table seek. So does putting them in the ON clause really save anything? I'm not sure, would have to create some test cases.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 31, 2014 at 1:02 pm
I posted my reply just to give the original poster something to test. I usually don't put sub-selects in my joins but If I am desperate I will consider anything...
The point was not the sub-select but weeding records out before they are selected makes sense to me.
I definitely don't have all the answers and a lot of what I do is trial and error...
I do know one thing, I would rather work with someone who is willing to try new things and consider new ideas than someone who is adamant they are right and that is that...
July 31, 2014 at 2:00 pm
churlbut (7/31/2014)
I do know one thing, I would rather work with someone who is willing to try new things and consider new ideas than someone who is adamant they are right and that is that...
No argument, thus I want to see the justifications. I'm all for try new things (heck, look at my greatest() function thread for trying new things), but you have to be able to back them up if you're going to make broad stroke statements like that.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 4, 2014 at 4:43 am
Hi guys,
thanks a lot, I'll try your suggestions today and let you know if there are any improvements in the run times, also welcome any other thoughts.
Thanks again
August 4, 2014 at 6:50 am
Ran the amended SQL but there is still no improvement. it takes exactly the same time (50 minutes) to run. Have attached the actual execution plan, looks exactly the same as the old one.
August 4, 2014 at 7:11 am
rajsin7786 (8/4/2014)
Ran the amended SQL but there is still no improvement. it takes exactly the same time (50 minutes) to run. Have attached the actual execution plan, looks exactly the same as the old one.
You have looked at this execution plan yourself, correct? You do see that your query is processing over 511,000,000 rows of data. This is not an insignificant number of rows.
First question, what do the following subqueries return:
select min(date) from date_dim where last_week_prior_flag = 'Y'
select max(date) from date_dim where date_dim.last_week_prior_flag = 'Y')
August 4, 2014 at 7:15 am
Also, when was the last time you updated your statistics? There are massive discrepancies on some of those index seeks between estimated and actual number of rows. Please update your statistics, run the query again, and post the execution plan.
John
August 4, 2014 at 7:37 am
John Mitchell-245523 (8/4/2014)
Also, when was the last time you updated your statistics? There are massive discrepancies on some of those index seeks between estimated and actual number of rows. Please update your statistics, run the query again, and post the execution plan.John
I said the same thing last week. The OP responded that the stats were updated and the performance didn't change. I suspect the stats didn't get updated which is big reason of why we are still dealing with this issue.
_______________________________________________________________
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/
August 4, 2014 at 7:49 am
Hmmm.. I saw where he said "the stats were run 3 weeks back", which doesn't sound very up to date to me. I think you're right - a bit of basic database maintenance could sort this problem out.
John
Edit - I just found the post where he said he'd updated them. Not sure how he did it, but maybe FULLSCAN would be appropriate here?
August 4, 2014 at 8:35 am
Hi ,
sorry I don't have much experience reading execution plans. I told both looks the same based on the % figure on icons within both the plans (because they looked the same). can you please tell me where do you see the 'query is processing over 511,000,000 rows of data.' ?
I just checked the options of the DB and I can see the 'auto create statistics' and 'auto update statistics' is set to 'True'
the statistics were last updated on sales_fact on 26-06-2014.
the statistics were last updated on date_dim table on 03-August (yesterday)
I found this information using the below query,
SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID('sales_fact')
The result of the other queries you had asked for is below,
select min(date) from date_dim where last_week_prior_flag = 'Y'
--2013-07-21 00:00:00.000
select max(date) from date_dim where date_dim.last_week_prior_flag = 'Y'
-- 2013-07-27 00:00:00.000
Thanks for all your help and looking forward to your help on this.
August 4, 2014 at 8:55 am
OK, so your statistics on sales_fact are more than five weeks out of date. Please update all statistics on that table with FULLSCAN, run the query again, and post the execution plan.
John
August 4, 2014 at 9:05 am
sure, just finished running the exec sp_updatestat, all stats are updated on this DB now. Running the SQL now, will keep you posted.
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply