Performance doesn't change even move the where condition statement to JOIN

Guru 2014-05-01 18:58:36

Esc Cost took 44.0% and I have moved the where conditions statement to JOIN statement. I am getting the same result. Any comments would be really appreciatable.

/storage/attachments/870-Plan.pesession
SQLSentrySDyckes 2014-05-01 20:31:29
Moving filter conditions between the ON clause and the WHERE clause will not change how the optimizer will produce the plan. To make a difference to the optimizer, you would have to change the ON clause or add to it.

For example, changing the the "join Rating.ElementFactor ef on ef.FactorID = f.ID" to "join Rating.ElementFactor ef on ef.FactorID = f.ID and ef.FactorID = rcf.FactorID" could possibly cause a change in your plan.

Jonathan Kehayias 2014-05-01 20:53:38
Agreed, especially if the change involved v.ID = @vehicleId in this JOIN:

    join Rating.RCVehicleSeries v on rcfvs.RCVehicleSeriesID = v.ID and v.ID = @vehicleId

If the predicate v.ID = @vehicleId was in the WHERE clause or here in this JOIN it wouldn't make a difference.

May 1, 2014
No Comments