how to make a better performance from query 1…. query 2 I have changed but no change in performance
2014-03-24 17:47:36
Query 1:
select t.Months, t.Miles, od.Minimum, od.Maximum, vc.Descr, f.Name, f.Descr, f.Amount from Rating.EquityTypeMinMax etmm join Rating.TermOdometer tod on etmm.TermOdometerID = tod.ID join Rating.Term t on tod.TermID = t.ID join Rating.Odometer od on tod.OdometerID = od.ID join Rating.VehicleCondition vc on tod.VehicleConditionID = vc.ID join Rating.FactorGroup fg on etmm.FactorGroupID = fg.ID join Rating.FactorGroupDetail fgd on fg.ID = fgd.FactorGroupID join Rating.Factor f on fgd.FactorID = f.id where f.Descr in ('Minimum', 'Maximum', 'Multiplier') order by t.months, t.miles, od.minimum, od.maximum, vc.descr;
Query 2:
select t.Months, t.Miles, od.Minimum, od.Maximum, vc.Descr, f.Name, f.Descr, f.Amount from Rating.TermOdometer tod join Rating.Term t on tod.TermID = t.id join Rating.Odometer od on tod.OdometerID = od.ID join Rating.VehicleCondition vc on tod.VehicleConditionID = vc.id join Rating.EquityTypeMinMax etmm on tod.id = etmm.TermOdometerID join Rating.FactorGroupDetail fgd on etmm.FactorGroupID = fgd.FactorGroupID join Rating.FactorGroup fg on etmm.FactorGroupID = fg.ID join Rating.Factor f on fgd.FactorID = f.ID and f.Descr in ('Minimum', 'Maximum','Multiplier')
2014-03-25 10:58:05
Hi Guru
How long does it take at present?
The statistics appear a little out, estimating at 19 but returning 534, it could be good to start by updating the stats.
Rating.EquityTypeMinMax is having a table scan on it, it looks as though it's a heap so I'd question whether that's the best choice; it may be but a clustered index would probably help there.
See how you go.
2014-03-26 14:12:04
Thank you. Yes I have changed the EquityTypeMinMax and works better.
Appreciated.
Guru
Also is the only difference between the queries that you removed the order by? (Moving the f.Descr filter from the join conditions to the where clause has absolutely no material impact on the plan or the results.)