Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Bugs & Issues> sp_executesql fails to pick correct indexes
 

Pages: 1 2 3
Bugs & Issues
sp_executesql fails to pick correct indexes
Page:2/3 

  Print all messages in this thread  
Poster Message
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14585 posts
# Posted on: 24-Dec-2009 09:38:20.  
You can modify the RewriteQuery() mthod to receive an IQuery instead of the IRetrievalQuery.
IRetrievalQuery and IActionQuery both implement IQuery

Code:
private string RewriteQuery(IQuery query)

  Top
acullen
User



Location:

Joined on:
24-Dec-2009 07:36:47
Posted:
7 posts
# Posted on: 26-Dec-2009 14:13:49.  
Doesn't solve the problem. See this thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=17102

  Top
arschr
User



Location:
Atlanta, Georgia; USA
Joined on:
14-Dec-2003 16:57:29
Posted:
881 posts
# Posted on: 26-Dec-2009 14:57:33.  
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.
- Al  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37870 posts
# Posted on: 26-Dec-2009 18:54:05.  
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.

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
acullen
User



Location:

Joined on:
24-Dec-2009 07:36:47
Posted:
7 posts
# Posted on: 26-Dec-2009 19:08:08.  
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?
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37870 posts
# Posted on: 27-Dec-2009 12:29:06.  
IActionQuery is just a Query object, which contains a DbCommand object, and you can manipulate that to your own liking and execute the IActionQuery.

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
DvK
User



Location:
Delft (centre), Netherlands
Joined on:
22-Mar-2006 10:43:57
Posted:
292 posts
# Posted on: 04-Jan-2010 14:22:48.  
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 :

Quote:
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 :

Quote:
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
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37870 posts
# Posted on: 04-Jan-2010 15:57:47.  
that sounds like the typical parameter sniffing issue of sqlserver (google that for more info)

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
JohnL
User



Location:
Tucson, USA
Joined on:
07-Oct-2005 19:18:13
Posted:
44 posts
# Posted on: 04-Jan-2010 16:01:39.  
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.
  Top
DvK
User



Location:
Delft (centre), Netherlands
Joined on:
22-Mar-2006 10:43:57
Posted:
292 posts
# Posted on: 04-Jan-2010 20:56:39.  
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


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37870 posts
# Posted on: 05-Jan-2010 10:15:25.  
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).
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
DvK
User



Location:
Delft (centre), Netherlands
Joined on:
22-Mar-2006 10:43:57
Posted:
292 posts
# Posted on: 05-Jan-2010 10:46:41.  
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 Cool

Regards,
Danny


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37870 posts
# Posted on: 05-Jan-2010 11:38:48.  
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 Cool

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 Wink)

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
DvK
User



Location:
Delft (centre), Netherlands
Joined on:
22-Mar-2006 10:43:57
Posted:
292 posts
# Posted on: 07-Jan-2010 09:39:30.  
Is it worth reporting this bug one more time ?

  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14585 posts
# Posted on: 07-Jan-2010 10:22:00.  
Yes, I guess.
  Top
carni4
User



Location:

Joined on:
09-Aug-2010 20:05:04
Posted:
20 posts
# Posted on: 02-Nov-2010 15:16:03.  
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 Wink

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


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.


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37870 posts
# Posted on: 02-Nov-2010 15:41:47.  
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.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
carni4
User



Location:

Joined on:
09-Aug-2010 20:05:04
Posted:
20 posts
# Posted on: 02-Nov-2010 15:51:13.  
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?


  Top
JohnL
User



Location:
Tucson, USA
Joined on:
07-Oct-2005 19:18:13
Posted:
44 posts
# Posted on: 02-Nov-2010 16:21:58.  
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 Wink

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


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).
  Top
carni4
User



Location:

Joined on:
09-Aug-2010 20:05:04
Posted:
20 posts
# Posted on: 02-Nov-2010 16:23:04.  
Regarding LINQ, I got it:

Code:
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.


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37870 posts
# Posted on: 02-Nov-2010 16:38:27.  
that's the only method, as that's the only method creating a select query Regular Smiley
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
carni4
User



Location:

Joined on:
09-Aug-2010 20:05:04
Posted:
20 posts
# Posted on: 02-Nov-2010 16:45:11.  
Otis wrote:
that's the only method, as that's the only method creating a select query Regular Smiley

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


  Top
DvK
User



Location:
Delft (centre), Netherlands
Joined on:
22-Mar-2006 10:43:57
Posted:
292 posts
# Posted on: 16-Nov-2010 09:37:13.  
Hi,

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



Location:
Tucson, USA
Joined on:
07-Oct-2005 19:18:13
Posted:
44 posts
# Posted on: 24-Nov-2010 18:25:49.  
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.


  Top
DvK
User



Location:
Delft (centre), Netherlands
Joined on:
22-Mar-2006 10:43:57
Posted:
292 posts
# Posted on: 24-Nov-2010 21:19:55.  
Doesn't this work with SQL 2005 as well ?
http://decipherinfosys.wordpress.com/2007/10/23/optimize-for-query-hint-in-sql-server-2005/
  Top
Pages: 1 2 3  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.