March 22, 2011 at 12:08 am
Hi Guys,
I'm trying to optimize some of our queries. One of the thing I'll do is to create an index in the source table. I came across this query and checked the execution plan for the existing design.
SELECT DISTINCT APP.SYSID,APP.APP_ID_C,REFERENCE_NAME AS GUARANTOR_NAME,ADDRESS1 AS ADDRESS_L1 ,
ADDRESS2 AS ADDRESS_L2,CITY,PHONE1 AS CONTACT_NO ,MOBILE ,PHONE2 AS FAX,
'R' AS GUARANTOR_TYPE, ADDRESS3 AS ADDRESS_L3,ADDRESS4 AS ADDRESS_L4
FROM VIEW_LOS_APP_APPLICATIONS APP
INNER JOIN (
SELECT A.* FROM VIEW_NBFC_REFERENCE_M A
INNER JOIN
(
SELECT SYSID,APP_ID_C REF_APP,REFERENCE_NAME REFNAME,MAX(REFID) REFERENCE_ID FROM VIEW_NBFC_REFERENCE_M
GROUP BY SYSID,APP_ID_C,REFERENCE_NAME
) B ON A.REFID = B.REFERENCE_ID AND A.SYSID=B.SYSID
) REFERRAL ON APP.APP_ID_C = REFERRAL.APP_ID_C AND APP.SYSID=REFERRAL.SYSID
Existing Version
*VIEW_NBFC_REFERENCE_M - contains two union tables (Table1 & Table2). Table1 contains a non-clustered unique index while Table 2 doesn't have any indexes.
Result
Estimated Subtree Cost :117.505
Query/Response time: 20secs
Modified Version
I created a non-clustered unique index in Table 2 just like in Table 1.
Result
Estimated Subtree Cost :30.9544773
Query/Response time: 56secs
All along i thought when the estimated subtree cost is lower, it's efficient and much faster, but how come it's longer to finish the query?
Please enlighten me and also can you help me where can I check if I'm doing an effective tuning.
Thanks guys
March 22, 2011 at 2:11 am
Costs are estimates, there are any number of things that can make them wrong, inaccurate cardinality estimates being the main one.
Focus your optimisation on duration, CPU and IO, don't focus on the cost. It can indicate a place to start, but not always.
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
March 22, 2011 at 2:41 am
GilaMonster (3/22/2011)
Costs are estimates, there are any number of things that can make them wrong, inaccurate cardinality estimates being the main one.Focus your optimisation on duration, CPU and IO, don't focus on the cost. It can indicate a place to start, but not always.
Is there a way to check the accumulative/total CPU & IO usage? instead of checking 1 by 1?
Most of our queries takes too long to finish, is it okay to focus on CPU and IO usage only without verifying the response time?
This is where I got the idea regarding estimated subtree cost.:unsure:
March 22, 2011 at 2:50 am
Marvin Maralit (3/22/2011)
Is there a way to check the accumulative/total CPU & IO usage? instead of checking 1 by 1?
Profiler?
Most of our queries takes too long to finish, is it okay to focus on CPU and IO usage only without verifying the response time?
Are you optimising to reduce CPU or IO usage? If so, yes. If you're optimising to reduce duration, then you need to look at duration
This is where I got the idea regarding estimated subtree cost.:unsure:
Far too much focus on costs and far too little focus on measurable effects.
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
March 22, 2011 at 3:06 am
If you're optimising to reduce duration, then you need to look at duration
Actually I'm focusing on the duration bro. You mean every time I tune my queries, I should wait for them to be finished in order for me to compare which is faster?:crying:
Is there a way I could view the estimated time in the execution plan?
Thanks
March 22, 2011 at 4:47 am
Marvin Maralit (3/22/2011)
Actually I'm focusing on the duration bro. You mean every time I tune my queries, I should wait for them to be finished in order for me to compare which is faster?:crying:
Yes. Otherwise how are you going to tell if your changes helped or not?
Is there a way I could view the estimated time in the execution plan?
Nope. Cost is kinda related to how long SQL thinks something will take, and as you've seen those can be wrong.
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
March 22, 2011 at 7:03 pm
This will be goin to be a pain in the neck 🙁
Bro, off topic. Which is quicker in terms of large record. Temp or Variable table?
Thanks
March 22, 2011 at 11:29 pm
Marvin Maralit (3/22/2011)
This will be goin to be a pain in the neck 🙁
Why?
Performance tuning is all about tweaking then testing for effects. If you don't test, how do you know if you've achieved something?
Bro, off topic. Which is quicker in terms of large record. Temp or Variable table?
Usually temp table, because of the statistics, but it can and does depend on details, so test both and see.
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
March 23, 2011 at 3:36 am
Actually I was referring to the thought that every time I made some changes, I need to wait to see the response time unlike what I did before in execution plan, this will give you the result instantly however this method like what you've said is wrong. I thought execution plan alone will help me identity which tweaking is more optimize.
Our DBA suggested instead of using Temp Table for large data, I should be using a real table. Is it true?
Lastly, should I start a new thread? I want to post 1 of our queries which causes a slow response time or can I just post it here 😛
Thank you \m/
March 23, 2011 at 3:57 am
Marvin Maralit (3/23/2011)
Our DBA suggested instead of using Temp Table for large data, I should be using a real table. Is it true?
Probably not, especially if there's going to be concurrent access
Lastly, should I start a new thread? I want to post 1 of our queries which causes a slow response time or can I just post it here 😛
New thread please. Post execution plans, query, table definition, index definitions. See http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply