Puzzling results

Question

Puzzling results

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

There was another option, set the parameter to a local variable and use that variable in the query... Should save you sum grief and won't need recompiles.

Jean Maurais Right there with Babe Points: 737 More actions · Answer 2

Yes but I'm not sure what to put as a default. The data is going to change over time and the spread between the different lines will not be the same. I figured recompiles were safer in the long run. I could put a 10 and it would work now, but what if they add a line 11 later?

This SP gets called maybe once every minute or so.

Jean

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

I think I saw a solution with no default value on the parameter that had the same results. Can you check this out and report back?

The idea behind this technic is that the parameter is set in proc with some unknown logic to the optimizer, therefore a plan cannot be generated with the input values as they are not directly used in the query.

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 4

Try this:

SELECT TOP 10 u.Serial, r.Model, u.Release, d.EventDate

FROM Release r

LEFT JOIN Production_Unit u ON u.Release = r.Release

LEFT JOIN Production_Detail d ON u.Serial = d.Serial

and d.EventID = 'PO' And d.EventPF = 1

WHERE r.Line = @Line

ORDER BY d.EventDate desc

WHERE clause filters for a LEFT JOIN force it to become an INNER JOIN, moving the LEFT JOIN WHERE clause filters to the ON clause allow them to be applied and retain the LEFT JOIN functionality.

Andy

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Already told him that... maybe he'll pick it up this time...

But I feel I must warn him that since you're changing the query... you'll be getting different results from now with that change.

Jean Maurais Right there with Babe Points: 737 More actions · Answer 6

Thanks guys. I'll be leaving the query as is for now. So far, the 'with recompile' change has done what we want and server impact has not been noticable. I have limited input into how the query is written, so it isn't always an easy situation.

Thanks again,

Jean

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Happy we could help. Did the query came back to the previous speed, or it just leveled at an in-between speed?