Hi, we have an adhoc select query being generated by EF and uses literal strings in the where clause.The query is running very slow and I am wondering if forced param. can be of any use in this case.
Has anyone used this for such purposes, what are the pros and cons of forced param. I am aware of one that it flushes everything from the plan cache except of current running queries.
Also, I was thinking if at all this needs to be enabled then would a plan guide be a good option to do this instead if enabling it for the whole DB.
Is this something that In-House Developers could change? Recoding it in EF to be a properly parameterized query or maybe even a call to a well written stored procedure seems like a much more effective way to go.
Thanks for your reply.
Unfortunately no, it seems it is a big task for them to change as it is in multiple places, so we are thinking if there is anything that can be done purely from Sql without changing EF as the effort to do that is enormous.
If you don't want to set parameterization to "FORCED" for the whole database, then you might be able to pull off a small miracle with Plan Guides. Here's a link on that.
Personally, I'd rather fix the front end code to correctly use parameterized queries because there is a sometimes very serious chance of SQL Injection if you don't. There's really no excuse for not using parameterized queries even in EF.
Yes I do agree that code should be modified and it might be done eventually.
I too thought about plan guides, will look into this again.May have to create multiple plan guides to cover most of the queries, at least better than enabling it for the whole DB.
Thanks