You can modify the RewriteQuery() mthod to receive an IQuery instead of the IRetrievalQuery. IRetrievalQuery and IActionQuery both implement IQuery

private string RewriteQuery(IQuery query)

Doesn't solve the problem. See this thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=17102

How long does the update take if you capture it with sql profiler, then execute it from query analyzer?

This would tell you if the problem is in the execution of the update or elsewhere.

Also consider, that during load (stress) it might be that your query is waiting for locks to be lifted before it is proceeding, which could take more time than when it's ran in isolation.

All true, but the weird thing is, the code has not changed at all. I think there are some blocks going on, but why now and not before? I'd like to at least try this code change to see if the plan changes. And now I'm just curious - how DO you change the command for an IActiveQuery?

IActionQuery is just a Query object, which contains a DbCommand object, and you can manipulate that to your own liking and execute the IActionQuery.

How about this url, where they in-depth discuss the same sp_executesql problem :

http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server/45362/Heavy-performance-issue-with-sp-executesql

At this line :

OK, I can finally see the plans. It seems that with the sp_executesql version it's choosing loop joins/groups instead of using the hash match operator. What happens if you add the following to the end of the sp_executesql query:

option (hash join, hash group)

...and then the answer of the person after applying the OPTION hint :

Hey Adam,

that's it! It's working, it's working! :-D

Well, now to the interesting question: why does SQL Server makes a different(=wrong) decision here (is it a bug - or is it a feature?)?

Could this be something ?

Regards, Danny

that sounds like the typical parameter sniffing issue of sqlserver (google that for more info)

The code I posted can be modified to work with any of the query types with a bit of adjustment for variable names in each context.

To be honest, this "fix" appears to be nothing more than a _workaround _ (or hack) for cached query plans being selected incorrectly (parameter sniffing error). By building a set of "local" variables inside the query text it appears that SQL 2005 and 2008 are forced to re-evaluate the plan by actually looking at the values. (SQL 2000 runs these queries unmodified fine). The downside is the plan has to be rebuilt (which takes time), the upside is that "incorrect" choices are avoided (at least so far for us).

We only apply the derived adapter class for problem queries that exhibit the property of working correctly when run manually but are slow when run in code. To date that is only one group of queries that share some similar foundations.

Our experience was that OPTION keywords didn't help, but perhaps that was because we were querying a view that itself was the problem in some way. Most of the posts I have seen that talk about this problem are doing some complex queries with complex views underlying them. I think the multiple levels of indirection are the source of the "bad" choices. We stopped investigating once we found a workaround, so I can't say that OPTION wouldn't be a better fix in some cases though.

Thanks for your input ! We ran into this issue today once again as I have before. This time it concerns an EXISTS clause that seems to trouble SQL Server. Running the same query without sp_executesql takes about 3-4 seconds, with sp_executesql it takes 3.5 minutes, let alone the enormous reads that the Profiler states....

It's insane and it takes hours to figure this out in a normal and explainable way. Conclusion for today : we're re-writing our query for now, but I'm pissed !

Regards, Danny

JohnL wrote:

The code I posted can be modified to work with any of the query types with a bit of adjustment for variable names in each context.

To be honest, this "fix" appears to be nothing more than a _workaround _ (or hack) for cached query plans being selected incorrectly (parameter sniffing error). By building a set of "local" variables inside the query text it appears that SQL 2005 and 2008 are forced to re-evaluate the plan by actually looking at the values. (SQL 2000 runs these queries unmodified fine). The downside is the plan has to be rebuilt (which takes time), the upside is that "incorrect" choices are avoided (at least so far for us).

We only apply the derived adapter class for problem queries that exhibit the property of working correctly when run manually but are slow when run in code. To date that is only one group of queries that share some similar foundations.

Our experience was that OPTION keywords didn't help, but perhaps that was because we were querying a view that itself was the problem in some way. Most of the posts I have seen that talk about this problem are doing some complex queries with complex views underlying them. I think the multiple levels of indirection are the source of the "bad" choices. We stopped investigating once we found a workaround, so I can't say that OPTION wouldn't be a better fix in some cases though.

Sadly it is indeed a workaround, but as MS is not willing to fix the issue, there's nothing else we can offer. Executing a SqlCommand always issues an execute_sp call, no matter what (as we don't add it to the query).

Strange thing is : MS says it has been fixed in SQL 2005 SP2 -> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=295646

We're running SQL 2005 SP3 here but it's still one pile of shit

Regards, Danny

DvK wrote:

Strange thing is : MS says it has been fixed in SQL 2005 SP2 -> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=295646

We're running SQL 2005 SP3 here but it's still one pile of shit

Regards, Danny

The connect bug says that the issue was caused by a CASE statement problem, not a parameter sniffing issue. (if I read the MS remark in that report correctly )

Is it worth reporting this bug one more time ?

Yes, I guess.

Otis wrote:

Thanks John for sharing. Yes, it's a problem which sometimes pops up its ugly head. Your code snippet is very small, so we'll look into adding a setting for v3.0's adapter to switch the parameter declaration.

There's one caveat: in v3 we're moving towards a DbProviderFactory approach, so your sqlclient specific code will fail. The db specific type will be available though, through the persistence info provider, so the code will require an adjustment in v3, but if we manage to squeeze this in before RTM (no promises) you don't need to

We won't seal classes, as we hate that concept as much as everyone else .

Can I assume that a generalized version of this fix didn't make it into 3.0? If not then it would be awesome if someone from the LLBLGen team could post an updated version of JohnL's code sample.

it didn't make it into 3.0, as we ran out of time, it's on the roll for 3.1 though.

The code posted on page 1 of this thread is usable on 3.0 as well, at least I don't see any problems there.

Otis wrote:

it didn't make it into 3.0, as we ran out of time, it's on the roll for 3.1 though.

The code posted on page 1 of this thread is usable on 3.0 as well, at least I don't see any problems there.

Ok, well both of those facts are good news. How about for a LINQ query to an anonymous type? Which OnFetch procedure do I override in the adapter?

carni4 wrote:

Otis wrote:

Thanks John for sharing. Yes, it's a problem which sometimes pops up its ugly head. Your code snippet is very small, so we'll look into adding a setting for v3.0's adapter to switch the parameter declaration.

There's one caveat: in v3 we're moving towards a DbProviderFactory approach, so your sqlclient specific code will fail. The db specific type will be available though, through the persistence info provider, so the code will require an adjustment in v3, but if we manage to squeeze this in before RTM (no promises) you don't need to

We won't seal classes, as we hate that concept as much as everyone else .

Can I assume that a generalized version of this fix didn't make it into 3.0? If not then it would be awesome if someone from the LLBLGen team could post an updated version of JohnL's code sample.

I have not yet upgraded to 3.0, in part because I was concerned that this old hack wouldn't work. I will post here once I have done so and verified the continued operation of this (or what revisions are required).

Regarding LINQ, I got it:

protected override IRetrievalQuery CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, long maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, bool allowDuplicates, IGroupByCollection groupByClause, int pageNumber, int pageSize)
{
        IRetrievalQuery selectQuery = base.CreateSelectDQ(fieldsToFetch, persistenceInfoObjects, filter, maxNumberOfItemsToReturn, sortClauses, relationsToWalk, allowDuplicates, groupByClause, pageNumber, pageSize);
        selectQuery.Command.CommandText = RewriteQuery(selectQuery);
        return selectQuery;
}

It seems to be working. Thanks everyone.

that's the only method, as that's the only method creating a select query

Otis wrote:

that's the only method, as that's the only method creating a select query

Ok, that makes sense. I don't need to override any of the OnFetch procedures for non-LINQ queries then. Thanks.

Hi,

Could this be something that leads to more clarification ? http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/

DvK wrote:

Hi,

Could this be something that leads to more clarification ? http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/

Yes, this is exactly what my workaround does: it forces the use of the statistics because it has nothing else to work from. If our software required SQL 2008 I would contemplate using the OPTIMIZE FOR options, but we run on 2005 and 2008.

Doesn't this work with SQL 2005 as well ? http://decipherinfosys.wordpress.com/2007/10/23/optimize-for-query-hint-in-sql-server-2005/