Large Messy Query. Need help ASAP!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Large Messy Query. Need help ASAP!!

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-07-03 : 02:36:47

I have this extremly large query, and I unfortuantly have a ton of variables that all need to be checked in order to get the proper results. Is there anything obvious anyone can see that will speed this up. It worked when I was dealing with a small record sample, but now the datasets are too large to make this at all efficient.


			    Insert Into #InvoiceDetailTmp(BatchID,GenerationDateID,EmployerID,PlanID,EmployeeID,GenerationDate,PeriodToBillDate,Rate,DateActive,ContractID,PlanBenWWaitID,BillingCycleID,SetRateAtEmployee,PlanRateID,InitialFee,BillEmployeeDirect,AccountingPeriodID,Fee)
				select 	@BatchID,
						a.GenerationDateID,
						c.EmployerID,
						b.PlanID,	
						e.EmployeeID,
						a.GenerationDate,
						dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate),
						c.Rate,
						dbo.EmployeeActiveinPeriod(e.EmployeeID,dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate),d.BillingCycleID) as DateActive,
						f.ContractID,
						f.PlanBenWWaitID,
						d.BillingCycleID,
						d.SetRateAtEmployee,
						c.EmployerPlanRateID,
						2,	
						d.BillEmployeeDirect,
						@AccountingPeriodID,
						c.Fee
				From		#GenerationDate a
				inner join	TMI_Plan b
				on			a.BenefitTypeID	=	 b.BenefitTypeID				
				inner join	tmi_EmployerPlanrate c 
				on			b.PlanID		=	c.PlanID
				inner join	TMI_BenefitType d
				on			a.BenefitTypeID	=	d.BenefitTypeID
				inner join	TMI_EmployerPlanBenWWait f
				on			c.ContractID	=	f.ContractID	
				and			b.BenefitTypeID =	f.BenefitTypeID
				inner join	TMI_Employer g 
				on			c.EmployerID	=	g.EmployerID
				Inner Join	TMI_EmployeePlan e
				on			e.EmployerID = g.EmployerID
				and			e.PlanID = c.PlanID
				inner join	TMI_Provider h
				on			g.ProvID = h.ProvID
				inner join	TMI_ProviderBenefitType j
				on			h.ProvID		=	j.ProvID 
				and			a.BenefitTypeID =	j.BenefitTypeID
				where 
						d.SetRateAtEmployee = 2 
				and		c.EmployerPlanRateID =(
												Select Top 1 a1.EmployerPlanRateID 
												From 
													TMI_EmployerPlanRate a1 
												where 
														a1.EffectiveDate <= dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate) 
												and		a1.EmployerID = c.EmployerID 
												and		a1.PlanID = c.PlanID 
												order by	
														a1.EffectiveDate desc
												)
				and		d.BillingTypeID = 1
				and not dbo.EmployerActive(c.EmployerID,dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate)) is null
				and		h.ProvStatusID = 1
				and		b.statusid = 1
				and		c.statusID = 1
				and		j.statusID = 1
				and		e.AddedToEmployeeDate <= dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate)
				and not exists(
								Select a4.InvoiceDetailID 
								from 
									Tar_InvoiceDetail a4 
								where 
										a4.EmployeeID = e.EmployeeID 
								and		a4.PlanID = b.PlanID 
								and		a4.GenerationDateID = a.GenerationDateID
								)
				and (
						(f.ExpirePeriod = 0)
						or 
						(
						f.ExpirePeriod > 0
						--If there is a expire period it is 2 billing cycles after startdate
						and dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate) <= dbo.FnGetFirstBillDate(d.BillingCycleID,d.PeriodtoBill + f.ExpirePeriod,d.BillStartDate,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc ),f.BillCurrent)  --DATEADD(mm, DATEDIFF(mm, 0, dateAdd(Month,f.ExpirePeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc )))+0, 0)
						 )
					)
				and (
					--This says billing date >= Waiting Period
					dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate) >= dbo.FnGetFirstBillDate(d.BillingCycleID,d.PeriodtoBill,d.BillStartDate,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc ),f.BillCurrent)
					--Scenerio someone starts has 2 month waiting period quits after the first month, then comes back, the status should be pulled from the last active status or the first??
					 --Also Waiting Period should be Billing Periods
					 --Expire period should be based off first bill date plus x periods
					 --Someone starts on the 20th w/ no waiting period when is their first bill
					
					/*(f.BillCurrent = 1 and 
					 dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate)>= DATEADD(mm, DATEDIFF(mm, 0, dateAdd(Month,f.WaitingPeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc )))+0, 0))
					or
					(f.BillCurrent = 2 and
					 dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate)>= DATEADD(mm, DATEDIFF(mm, 0, dateAdd(Month,f.WaitingPeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc )))+1, 0))
					*/
					 )
				and 
				(
				( --BIll by daterange employer = 2 bill by daterange employee = 2
					d.BillByDateRangeEmployer = 2 and d.BillByDateRangeEmployee =2
					and not dbo.EmployeeActiveinPeriod(e.EmployeeID,dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate),d.BillingCycleID) is null
				)
				or
				( --BIll by daterange employer = 1 BypassStatus = 2
					d.BillByDateRangeEmployer = 1 and d.BypassStatus =2
					and dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate) between  f.startdate and f.Enddate 
					and not dbo.EmployeeActiveinPeriod(e.EmployeeID,dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate),d.BillingCycleID) is null
				)
				or	
				( --BIll by daterange employee = 1 BypassStatus = 2
					d.BillByDateRangeEmployee = 1 and d.BypassStatus =2
					and dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate) between  e.startdate and e.Enddate 
					and not dbo.EmployeeActiveinPeriod(e.EmployeeID,dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate),d.BillingCycleID) is null
				)
				or
				( --BIll by daterange employer = 1 BypassStatus = 1
					d.BillByDateRangeEmployer = 1 and d.BypassStatus =1
					--and not dbo.EmployeeActiveinPeriod(e.EmployeeID,dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate),d.BillingCycleID) is null
					and dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate) between  f.startdate and f.Enddate 
				)
				or
				( --BIll by daterange employee = 1 BypassStatus = 1
					d.BillByDateRangeEmployee = 1 and d.BypassStatus =1
					--and not dbo.EmployeeActiveinPeriod(e.EmployeeID,dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate),d.BillingCycleID) is null
					and dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate) between  e.startdate and e.Enddate 
					)
				)

Kristen
Test

22859 Posts

Posted - 2007-07-03 : 05:03:50

"Is there anything obvious anyone can see that will speed this up"

The usual candidates!

Appropriate indexes
Indexes recently rebuilt or statistics updated (for large tables possibly using the FULLSCAN attribute)

Add owner (dbo.) in front of all table names, in case that helps the optimiser to keep the query plan cached.

Possibly use @TabelVariable instead of #TempTable - e.g. if not too many records being processed

Make sure temp table (#GenerationDate) has a PK and/or Index - presumably on a.BenefitTypeID

The string of ORs towards the end is probably inefficient. But not sure how you can work around that, unless you preprocess the TempTable first (delete rows that are not value, or Update a column to indicate which ones are OK - possibly with 5 updates matching each of the OR conditions.

I wonder if you can replace this:


	and		c.EmployerPlanRateID =(
												Select Top 1 a1.EmployerPlanRateID 
												From 
													TMI_EmployerPlanRate a1 
												where 
														a1.EffectiveDate <= dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate) 
												and		a1.EmployerID = c.EmployerID 
												and		a1.PlanID = c.PlanID 
												order by	
														a1.EffectiveDate desc

)
with a table you build beforehand with a single row for each a1.EmployerPlanRateID [i.e. only the ones that are needed in this query] and just join it in.

Kristen

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-07-03 : 15:45:01

I know this was a difficult question, with no obvious answer from looking at the data w/o the set.

I re-Wrote the query. I went from a query that went from taking 4 and a half hours to run, to one that takes appox 17 seconds. I still will try tweaking more.

The biggest time saver in this query was changing


inner join	TMI_ProviderBenefitType j
				on			h.ProvID		=	j.ProvID 
				and			a.BenefitTypeID =	j.BenefitTypeID

to using a exist statment since I only needed that table to verify if something was active.

Other then that I re-Built the indexes, replaced some top clauses where I could.

I like Kristen's suggestion of possibly building a table prior to accomidate not having so many joins.

All and all I can deal with this query taking 17 seconds, but I will try to tweak to get a little quicker.

Here's the new query


			select 	@BatchID,
						a.GenerationDateID,
						c.EmployerID,
						b.PlanID,	
						e.EmployeeID,
						a.GenerationDate,
						dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate),
						c.Rate,
						dbo.EmployeeActiveinPeriod(e.EmployeeID,dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate),d.BillingCycleID) as DateActive,
						f.ContractID,
						f.PlanBenWWaitID,
						d.BillingCycleID,
						d.SetRateAtEmployee,
						c.EmployerPlanRateID,
						2,	
						d.BillEmployeeDirect,
						@AccountingPeriodID,
						c.Fee,
						dbo.FnGetFirstBillDate(d.BillingCycleID,f.WaitingPeriod,d.BillStartDate,(select Min(zz.EffectiveDate) from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1),f.BillCurrent)
				From		#GenerationDate a
				inner join	TMI_Plan b
				on			a.BenefitTypeID	=	 b.BenefitTypeID				
				inner join	tmi_EmployerPlanrate c 
				on			b.PlanID		=	c.PlanID
				inner join	TMI_BenefitType d
				on			a.BenefitTypeID	=	d.BenefitTypeID
				inner join	TMI_EmployerPlanBenWWait f
				on			c.ContractID	=	f.ContractID	
				and			b.BenefitTypeID =	f.BenefitTypeID
				inner join	TMI_Employer g 
				on			c.EmployerID	=	g.EmployerID
				Inner Join	TMI_EmployeePlan e
				on			e.EmployerID = g.EmployerID
				and			e.PlanID = c.PlanID
				inner join	TMI_Provider h
				on			g.ProvID = h.ProvID
				where exists (Select 1 from TMI_ProviderBenefitType abcde where abcde.ProvID = g.ProvID and abcde.BenefitTypeID = a.BenefitTypeID and abcde.StatusID = 1)				
			--	and g.EmployerID = 934
			    --and not dbo.EmployerActive(c.EmployerID,dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate)) is null
				and		d.SetRateAtEmployee = 2 
				and		c.EmployerPlanRateID =(
												Select Top 1 a1.EmployerPlanRateID 
												From 
													TMI_EmployerPlanRate a1 
												where 
														a1.EffectiveDate <= dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate) 
												and		a1.EmployerID = c.EmployerID 
												and		a1.PlanID = c.PlanID 
												order by	
														a1.EffectiveDate desc
												)
				and		d.BillingTypeID = 1
				and not dbo.EmployerActive(c.EmployerID,dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate)) is null
				and		h.ProvStatusID = 1
				and		b.statusid = 1
				and		c.statusID = 1
				and		e.AddedToEmployeeDate <= dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate)
				and not exists(
								Select a4.InvoiceDetailID 
								from 
									Tar_InvoiceDetail a4 
								where 
										a4.EmployeeID = e.EmployeeID 
								and		a4.PlanID = b.PlanID 
								and		a4.GenerationDateID = a.GenerationDateID
								)
				and (
						(f.ExpirePeriod = 0)
						or 
						(
						f.ExpirePeriod > 0
						--If there is a expire period it is 2 billing cycles after startdate
						and dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate) <= dbo.FnGetFirstBillDate(d.BillingCycleID,f.WaitingPeriod + f.ExpirePeriod,d.BillStartDate,(select min(zz.EffectiveDate) from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1),f.BillCurrent)  --DATEADD(mm, DATEDIFF(mm, 0, dateAdd(Month,f.ExpirePeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc )))+0, 0)
						 )
					) 
				and (
					--This says billing date >= Waiting Period
					dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate) >= dbo.FnGetFirstBillDate(d.BillingCycleID,f.WaitingPeriod,d.BillStartDate,(select Min(zz.EffectiveDate) from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1),f.BillCurrent)
					 )
				and --good
				(
				( --BIll by daterange employer = 2 bill by daterange employee = 2
					d.BillByDateRangeEmployer = 2 and d.BillByDateRangeEmployee =2
					and not dbo.EmployeeActiveinPeriod(e.EmployeeID,dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate),d.BillingCycleID) is null
				)
				or
				( --BIll by daterange employer = 1 BypassStatus = 2
					d.BillByDateRangeEmployer = 1 and d.BypassStatus =2
					and dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate) between  f.startdate and f.Enddate 
					and not dbo.EmployeeActiveinPeriod(e.EmployeeID,dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate),d.BillingCycleID) is null
				)
				or	
				( --BIll by daterange employee = 1 BypassStatus = 2
					d.BillByDateRangeEmployee = 1 and d.BypassStatus =2
					and dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate) between  e.startdate and e.Enddate 
					and not dbo.EmployeeActiveinPeriod(e.EmployeeID,dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate),d.BillingCycleID) is null
				)
				or
				( --BIll by daterange employer = 1 BypassStatus = 1
					d.BillByDateRangeEmployer = 1 and d.BypassStatus =1
					--and not dbo.EmployeeActiveinPeriod(e.EmployeeID,dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate),d.BillingCycleID) is null
					and dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate) between  f.startdate and f.Enddate 
				)
				or
				( --BIll by daterange employee = 1 BypassStatus = 1
					d.BillByDateRangeEmployee = 1 and d.BypassStatus =1
					--and not dbo.EmployeeActiveinPeriod(e.EmployeeID,dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate),d.BillingCycleID) is null
					and dbo.FnAdjustDatesByBillingCycle(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate) between  e.startdate and e.Enddate 
					)
				) -- Close out entire section

Kristen
Test

22859 Posts

Posted - 2007-07-04 : 04:53:07

"to using a exist statment since I only needed that table to verify if something was active"

If there are multiple matching rows in TMI_ProviderBenefitType the EXIST will change the query to only return one row (from other tables) instead of multiple rows. I see you have also moved the StatusID = 1 test out of the WHERE clause. You could move all the other constants out of the WHERE clause into the (INNER) JOIN that they are appropriate to. The optimiser should do this anyway, but it won't hurt if you move them. You could then also consider changing the indexes on those Joined tables to include those extra criteria columns so that the indexes "cover" the JOIN criteria.

e.g.


JOIN TMI_BenefitType AS d
	 ON a.BenefitTypeID = d.BenefitTypeID
	AND d.SetRateAtEmployee = 2
	AND d.BillingTypeID = 1

and make sure there is an index on TMI_BenefitType which includes BenefitTypeID, SetRateAtEmployee, BillingTypeID

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources