how to make a better performance from query 1…. query 2 I have changed but no change in performance

Guru 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')

/storage/attachments/811-Plan.queryanalysis

Aaron Bertrand 2014-03-26 13:54:20

This query took 10 milliseconds. How much faster do you expect it to run? Where exactly do you perceive the performance problem? Perhaps it is in your application, or in the network, if the results take a long time to render.

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.)

Mart 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.

Guru 2014-03-26 14:12:04

Thank you. Yes I have changed the EquityTypeMinMax and works better.

Appreciated.

Guru

March 24, 2014

No Comments