I have a large dynamic query that is causing huge performance problems in our database. It looks like we're running into the "parameter sniffing" issue where using sp_executesql is causing cached execution plans to used, or a full table scan is happening vs. using the indexes that are defined in place.

In this thread:

http://llblgen.com/tinyforum/GotoMessage.aspx?MessageID=95391&ThreadID=13271

the author came up with a way to rewrite the fetch query such that the parameters were defined first, then executed the sql. However, this was done using Adapter.

Using SelfServicing, how could I achieve the same result where the SQL to be executed would be retrieved to be "reorganized" to avoid this behavior?

I tried to inherit from my base collection class (in this case PositionUsersCollection) but I could not override the UpdateMulti method.

Any thoughts on a direction for this?

EDIT: LLBLGen 2.6 SQL 2005

The methods you need are present in the DaoBase class. So in the generated project you can find the CommonDaoBase class which inherits from the DaoBase, and which all your Dao classes inherits.

In there you should override all methods in the DaoBase class which has an IRetrievalQuery as a parameter. (use a partial file to add you methods in order not to be overwritten when re-generating the code).

And then tweak the query as you like and pass it again to the base method.

What I ended up doing was similar to the previous thread I posted about using "INSERT FROM". I created an IRetrievalQuery to get my set to filter on. I then created a new IActionQuery where I could manipulate the Command.CommandText to declare new parameters at the beginning of the query, associated the SQLParameters and the PhysicalTransaction and ran the query.