May 2, 2008 at 8:29 am
I'm having an issue with a query that I inherited when the developer left our company. The query outputs way too many fields for what is actually used by the website so I know that that is part of the cause of my performance problems but I have also noticed that if I do an update statistics on the three largest tables that the query uses that the runtime of my query goes from 30 seconds to about 3 seconds.
The kicker is that I update statistics every morning at around 3 am before anyone would run this query and it still takes 30 seconds to run when I check it first thing at 8. Only when I go to the website and run the query then go back in and update statistics does the query go from a 30 second runtime to a 3 second runtime.
I thought statistics were only related to queries via the query optimizer which uses them to develop the most efficient execution plan? If that is the case shouldn't the query run the same before I run update statistics? Is there something I'm missing with my understanding of query optimization that would cause the query optimizer to choose a faster execution plan after I run update statistics?
May 2, 2008 at 8:37 am
If it's a complex query then it's possible that most of those 30 seconds are taken compiling a new execution plan. This would explain why it takes 30 seconds the first time but 3 seconds thereafter. The query optimizer may or may not decide to recompile the query after statistics are updated. You can test this out by looking at the estimated execution plan before and after you do the update.
John
May 2, 2008 at 9:33 am
I'll check it out. Is there any way to get this stable?
May 2, 2008 at 9:52 am
If all the proc does is select stuff, you could always have the scheduled job that updates the statistics run the proc as a second step. Would that work for you?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 2, 2008 at 10:10 am
That might work. I'll look at doing that. Thank you!
May 2, 2008 at 10:17 am
You should also look at your buffer cache, it may be that the data is not in the cache when first run and then it is in cache and it is fast. I have seen performance gains of this much when the data is in the cache.
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
May 2, 2008 at 11:01 am
I'd suggest instead of spending your time trying to prop it up, you dive in and start refactoring it in bits and pieces to reduce the compile time and run time.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 2, 2008 at 1:48 pm
I do plan to refactor it. I was just wondering why the performance changed after I ran the query and updated the statistics.
May 2, 2008 at 2:10 pm
- as already mentioned, IO is probably the most slowdown factor.
- do you rebuild indexes (clix / nci) on a regular bases ?
- check your db for "auto update statistics". If this is turned off, try to find out why and turn it back on again if you can.
- Is your db always "open" (check "auto clause" in the db-settings)
Avoid auto clause in production environments !
- Check the (xml) execution plans and analyse them.
Needless to say to avoid scans, avoid implicit conversions,...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 2, 2008 at 3:03 pm
Thanks for the tips! I'll check that stuff out!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply