June 18, 2009 at 7:46 pm
So, we have a stored proc that is currently taking anywhere from 2-5 minutes to return a result set. So today I was told to try and optimize it a bit. So I start to work my magic on it and have gotten to a point where I can now return the full record set in about 9-18 seconds by using a few join hints in the query. Now, when doing some performance analysis between the old and the new, I notice that the old way has about 1/3 less reads and writes, but takes 2-5 minutes to run. The new way runs in 9-18 seconds. So, I guess my question is, should I feel ok absorbing the extra read/write time in favor of a faster query? From an end users perspective, they will love it because their report is coming back fast. But from my side, I will always have a query that is showing a bit higher number for reads and writes in my perfmon reports. I'd like to hear what you'll have to say about this.
I compare being a DBA to that of Smokey The Bear. Helping to prevent wildfires in your forest of servers and applications.
June 18, 2009 at 8:18 pm
Hard to really say. Please take the time to read the 2nd article I have referenced in my signature block below reagrding asking for assistance with performance issues. Following the guidelines in that article, and I'm sure you will get some very good answers in response. Just from your description of the issue, hard to really provide you with any true advice. Just not enough information.
June 18, 2009 at 8:28 pm
I'm not really asking for perf help...but more of an opinion on sacrificing read/writes versus execution time. I have worked in various shops where one will focus mainly on speed where as the other will focus on the i/o.
I compare being a DBA to that of Smokey The Bear. Helping to prevent wildfires in your forest of servers and applications.
June 18, 2009 at 8:47 pm
You are asking for performance help, even if you don't know it. I can't tell you one way or the other based solely on your description. The first thing I would say, however, is that you don't really want to rely on query hints to improve performance. Using those should be a last resort in performance tuning. We really need to see the actual execution plans for both versions, as well as the DDL for the tables, the code involved, and whatever else the article is asking for.
If you really want the best advice, please following the guidelines in that article.
June 19, 2009 at 3:19 am
Rather stay away from join hints. By using them you're saying that you know the query and the data better than the query optimiser does. It may help today but as the data grows it may start to hinder more and more.
There are usually ways to optimise queries without resorting to hints. Personally I've just join/index hints twice in about 5 years.
To say more we need more info, which is what Lynn's requesting.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply