June 26, 2012 at 1:31 pm
Hi,
I am having hard time troubleshooting query performance. As soon the I run the following query CPU is hitting sky.
I did checked indexes/stats and all looks good.
I know, Im not providing complete information but I just need your advice on where to start
Select * from table0 uu
Inner Join DBTest.dbo.TABLEA g
on uu.COLA=g.COLA and g.COLB=1
Inner join TABLEB sg
On sg.COLB=1 and Left(sg.cold,2)=uu.cold2
Inner Join DBTest.dbo.TABLEC gsm
On g.cole=gsm.cole
and sg.colc=gsm.colc
Inner join DBTest.dbo.TABLED u on g.COLA=u.COLA
and u.COLg = left(sg.cold,len(u.cold + ' ' + u.colf))
and u.udate >GetDate()-15
June 26, 2012 at 1:42 pm
The LEFT() function on columns involved in your join clause can/will negate any indexes you have.
You might try using like instead and see how that works. I can replace the first one but I don't know how to do that on the second occurrence. But if you could eliminate the "left(sg.cold,len(u.cold + ' ' + u.colf))" that might help.
Beyond that I would try breaking this one query joining 5 tables into two. Select intermediate results into a temp table then join to final tables. Divide and conquer to simplify the plan can often help.
Select * from table0 uu
Inner Join DBTest.dbo.TABLEA g
on uu.COLA=g.COLA and g.COLB=1
Inner join TABLEB sg
On sg.COLB=1 and sg.cold like uu.cold2+'%' -- just to limit the scope
Inner Join DBTest.dbo.TABLEC gsm
On g.cole=gsm.cole
and sg.colc=gsm.colc
Inner join DBTest.dbo.TABLED u on g.COLA=u.COLA
and u.COLg = left(sg.cold,len(u.cold + ' ' + u.colf))
and u.udate > GetDate()-15
The probability of survival is inversely proportional to the angle of arrival.
June 26, 2012 at 1:50 pm
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Without that, we're guessing. The LEFT in the join looks like a potential problem, but it could be just fine, can't tell.
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
June 26, 2012 at 2:16 pm
Thanks Gila.
I have attached the the sqlplan.
FYI. The attached plan is estimated plan not actual.
June 26, 2012 at 2:21 pm
Actual plan please (can't see row estimation errors on an estimated), table definitions and index defintions also.
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
June 26, 2012 at 2:54 pm
Gila- Will row counts from all tables involved in the query will do ?
June 26, 2012 at 3:14 pm
No, need to see the rows affected by every operator.
If you can't get an actual plan, then please at least the index and table definitons.
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
June 26, 2012 at 3:23 pm
What a friggin mess. If I were SqlServer I'd refuse to run it. Here is how I would format it before even beginning to optimize:
select
*
from
(
select cold2
from table0
) as uu
join
(
select COLA, cole
from DBTest.dbo.TABLEA
where COLB = 1
) as g on uu.COLA = g.COLA
join
(
select colc, Left(sg.cold,2) as l2, cold
from TABLEB
where COLB = 1
) as sg On sg.l2 = uu.cold2
join
(
select colc
from DBTest.dbo.TABLEC
) as gsm On g.cole=gsm.cole and sg.colc=gsm.colc
join
(
select COLA, cold, COLg, colf,
from DBTest.dbo.TABLED
where udate > GetDate()-15
) as u on g.COLA=u.COLA
where
u.COLg = left(sg.cold, len(u.cold + ' ' + u.colf))
June 27, 2012 at 9:47 am
First of all, thanks for your time in helping to solve this issue.
I have formatted code as mentionted by bill and I was able to get actual execution plan by selecting top 100 rows
select * from
(select subgroup2,GroupNo from #temp) as uu
join
(select GroupNo,PlanGroupID from fccore.dbo.tPlGroups
where PlanID = 1) as g on uu.GroupNo=g.GroupNo
join
(
select SubGroup,Left(SubGroup,2) as temp,PLanSubGroupID
from tPlSubGroups
where PlanID = 1
) as sg On sg.temp=uu.SubGroup2
join
(
select *
from fccore.dbo.tPlGroupSubgroupMap
) as gsm On g.PlanGroupID=gsm.PlanGroupID and sg.PLanSubGroupID=gsm.PlanSubGroupID
join
(
select top 100 GroupNo,SubGroupBenefitPlanID,SubGroup,BenefitPlanID -- The actual rows from this select are 57000
from fcimport.dbo.tNHPGroupCopays_All --I used top 100 to get the output fast so that I can include Actual exe plan
--and if I put more than 1000 the query is taking for ever to run.
where udate > GetDate()-15
) as u on g.GroupNo=u.GroupNo -- this join is culprit where it is forming cartestion product and but it is required.
where
u.SubGroupBenefitPlanID = left(sg.SubGroup,len(u.SubGroup + ' ' + u.BenefitPlanID))
Please find the attached actual execution plan.
June 27, 2012 at 10:55 am
An execution plan of the TOP 100 is useless. It'll be a different plan to the full query. If you can't get actual plan, please the index and table definitions.
Personally I'd never consider writing code with all those nested subqueries. Makes it much harder to read, much longer, more typing and for no gain. The original query was perfectly acceptable in terms of formatting and layout (unless you're paid by line of code or number of keywords)
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
June 27, 2012 at 11:04 am
Ok Sure.
Do you want to see complete result set from sp_help for table defination?
How do you want me to get index defination?
I mean do you need something like this for index defination
index_nameindex_descriptionindex_keys
IX_tPlGroupSubgroupMapnonclustered, unique located on PRIMARYPlanGroupID, PlanSubGroupID
IX_tPlGroupSubgroupMap_1clustered located on PRIMARYSubContractID
IX_tPlGroupSubgroupMap_2nonclustered located on PRIMARYPlanID
IX_tPlGroupSubgroupMap_3nonclustered located on PRIMARYUserID
IX_tPlGroupSubgroupMap_4nonclustered located on PRIMARYPlanBenefitID
IX_tPlGroupSubgroupMap_5nonclustered located on PRIMARYBenefitLevelID
IX_tPlGroupSubgroupMap_6nonclustered located on PRIMARYPlanSubGroupID
IX_tPlGroupSubgroupMap_7nonclustered located on PRIMARYFeeCategoryID
PK_tPlGroupSubgroupMapnonclustered, unique, primary key located on PRIMARYRecID
June 27, 2012 at 12:16 pm
The create index statements and the create table statement. Want to see data types, key and include columns
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
June 27, 2012 at 12:31 pm
please find the attached scripts.
June 27, 2012 at 1:08 pm
One general comment. Single-column nonclustered indexes are generally useless unless you make a habit of writing queries like SELECT col1 from table WHERE col1 = @var1
Widen [IX_tPlGroupSubgroupMap_6] ON [dbo].[tPlGroupSubgroupMap] include RecID, PlanGroupID
Clustered index on the temp table on GroupNo
Let's see how much that helps.
Also, how many rows does that return? SQL thinks 1. If not 1, then you have an estimation problem, consider updating statistics on all tables with fullscan.
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
June 27, 2012 at 1:32 pm
I really appreciate your time on this Gilamonster.
I will get back with my findings.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply