February 19, 2009 at 8:40 am
There's a couple of definite row estimate discrepancies.
The ad-hoc query shows an estimated 697 rows from the att_patient table, while the function shows just 1. Which sounds more correct?
There's no useful index on the tb_Reg_Medgroup table, leading to a clustered index scan. I would suggest (eff_datebegin, eff_dateend, ReportGroup) INCLUDE (MedGroupID)
How many rows in that table?
I would suggest a similar index on the tb_Att_MedGroup (MedGroupID, Eff_DateBeg, Eff_DateEnd) INCLUDE (ProviderID)
and one on tbAtt_Provider (AsOfDate, BatchType, ProviderID) INCLUDE (RIGlobalProviderID)
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
February 19, 2009 at 8:43 am
Here are the actual execution plans.
February 19, 2009 at 8:47 am
The ad-hoc query shows an estimated 697 rows from the att_patient table, while the function shows just 1. Which sounds more correct?
The 697 is more correct. Dave posted the actual plan for the function so it should clarify that issue.
There's no useful index on the tb_Reg_Medgroup table, leading to a clustered index scan. I would suggest (eff_datebegin, eff_dateend, ReportGroup) INCLUDE (MedGroupID)
How many rows in that table?
It's not a big table (less than 100 rows), but I'll certainly look at that.
I would suggest a similar index on the tb_Att_MedGroup (MedGroupID, Eff_DateBeg, Eff_DateEnd) INCLUDE (ProviderID)
and one on tbAtt_Provider (AsOfDate, BatchType, ProviderID) INCLUDE (RIGlobalProviderID)
The primary index on that table is (AsOfDate, BatchType, ProviderID), so I can certainly add the include here.
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
February 19, 2009 at 8:53 am
Ok, disregard index suggestions from earlier, the actual plan's very different
Widen the index IX_tbAtt_Patient_RIGlobalProviderID on tbAtt_Patient. Add SourceID and IsParticipating to the index key
Add an index on tbREg_MedGroup (ReportGroup, Eff_DateBeg, Eff_DateEnd) INCLUDE (MedGRoupID)
That should do it.
The primary index on that table is (AsOfDate, BatchType, ProviderID), so I can certainly add the include here.
A primary key cannot have 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
February 19, 2009 at 9:10 am
Yeah, Gail's got it. Fix that lookup operation with the included columns and should see a pretty substantial increase in performance.
I think you're getting those scans because of the ISNULL clauses. You could try an OR where you check for each of the values independently, but that still might not work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 19, 2009 at 9:13 am
I think you're getting those scans because of the ISNULL clauses. You could try an OR where you check for each of the values independently, but that still might not work.
If I change them for an OR clause, won't that invalidate the index lookup?
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
February 19, 2009 at 9:17 am
Aaron N. Cutshall (2/19/2009)
If I change them for an OR clause, won't that invalidate the index lookup?
Try it and see.
Test a version with the ISNULL against a version with OR and see which is faster, does less IOs (Statistics IO) or has a lower cost exec plan.
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 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply