Is there support to force the SQL generator to use a specific index?

what database are you using?

Index usage depends on the query and is decided by the dbms, not llblgen pro.

If the dbms doesn't choose the way we want, is there a way to specify hints? in sql server these can be specified as a component of the query string.

It's my understanding that hints like index hints are not recommended.

I agree that they are not recomended, but sometimes they are the resque. We have hints on one sql (Oracle) where it was the only way...

If the optimizer is choosing a plan that runs for hours and by using hints you can get the correct results in seconds, I'd have to wonder who is doing the recomending.

Of course hints can become invalid, so they shouldn't be used lightly. But the optimizer is "only human" and dba's are god (like).

We are using SQL Server 2000. We have determined that the optimizer consistently makes a poor choice for a specific query.

It would help performance a lot (reduce from minutes to seconds) if we could supply a hint to an LLBL object that it would include in the SQL that was generated. I thought that support for this might be available in some low level class that I hadn't discovered yet. You seem to have thought of everything else

markF wrote:

We are using SQL Server 2000. We have determined that the optimizer consistently makes a poor choice for a specific query.

It would help performance a lot (reduce from minutes to seconds) if we could supply a hint to an LLBL object that it would include in the SQL that was generated. I thought that support for this might be available in some low level class that I hadn't discovered yet. You seem to have thought of everything else

Interesting. Have you used the Index Tunning Wizard to see what Index it recommends for a specific query? I've always been able to target my queries to the index without needing a hint.

BOb

pilotboba wrote:

markF wrote:

We are using SQL Server 2000. We have determined that the optimizer consistently makes a poor choice for a specific query.

It would help performance a lot (reduce from minutes to seconds) if we could supply a hint to an LLBL object that it would include in the SQL that was generated. I thought that support for this might be available in some low level class that I hadn't discovered yet. You seem to have thought of everything else

Interesting. Have you used the Index Tunning Wizard to see what Index it recommends for a specific query? I've always been able to target my queries to the index without needing a hint.

Same here, even with large complex queries. With the new setup of DQE objects in the generated code, there is room for hint emitting in queries, though I won't add it to 1.0.2005.1, perhaps v2.0 (note: there is support for one hint: NOLOCK on sqlserver, and on DB2 it uses optimize hints in paging queries)

Otis wrote:

markF wrote:

We are using SQL Server 2000. We have determined that the optimizer consistently makes a poor choice for a specific query.

It would help performance a lot (reduce from minutes to seconds) if we could supply a hint to an LLBL object that it would include in the SQL that was generated. I thought that support for this might be available in some low level class that I hadn't discovered yet. You seem to have thought of everything else

Same here, even with large complex queries. With the new setup of DQE objects in the generated code, there is room for hint emitting in queries, though I won't add it to 1.0.2005.1, perhaps v2.0 (note: there is support for one hint: NOLOCK on sqlserver, and on DB2 it uses optimize hints in paging queries)

We've just experienced the same problem. For one of our clients, some particular queries are suddenly performing catastrophically slowly (normally takes around 1 second and now takes 22 minutes!). The cause is SQLServer 2000 getting the query plan wrong. We had never had this problem before and I suspect that it's due to the unusual distribution of data that this particular client happens to have in the schema.

We ran the index tuning wizard but it has no suggestions that conclusively fix the problem. The only solution that we've been able to find is to add a join hint on one particular join: instead of using INNER JOIN, we can use INNER LOOP JOIN and that immediately fixes the problem. But of course I cannot specify this with llblgen code. Before I go investigating, do you think it's possible to extend the DQE to support such hints? Is there a chance that I could extend the libraries to provide such support?

Our company is very pro-stored procedure and our group is unique in using llblgen pro and the dynamic query approach. So I'm conscious of this when talking to our DBA's and their reaction is "just add this query hint to the stored procedure" and I say, "hmm, well I don't want to (can't) modify my query in that way". An alternative would be to implement this particular query as a view and create a typed view over it. And maybe that's my longer term solution. Unfortunately this code is in production and altering the way I'm approaching this query will be a significant effort to develop, test and get into production (when we've got so much else that we need to be doing). So I'm currently working with one of our DBA's on alternative approaches (like fiddling with statistics and the like).

Anyway, just wondering if it's worth considering adding essentially database specific hint options to llblgen or whether the longer term solution is to use SP's or Views in this kind of circumstance. (And of course a point in the right direction if you think I can extend the framework to add this myself - we're using 1.0.2005.1).

Interestingly, SQL2005 gets the query plan right every time so there's a compelling reason for us to upgrade

takb wrote:

Otis wrote:

markF wrote:

We are using SQL Server 2000. We have determined that the optimizer consistently makes a poor choice for a specific query.

It would help performance a lot (reduce from minutes to seconds) if we could supply a hint to an LLBL object that it would include in the SQL that was generated. I thought that support for this might be available in some low level class that I hadn't discovered yet. You seem to have thought of everything else

Same here, even with large complex queries. With the new setup of DQE objects in the generated code, there is room for hint emitting in queries, though I won't add it to 1.0.2005.1, perhaps v2.0 (note: there is support for one hint: NOLOCK on sqlserver, and on DB2 it uses optimize hints in paging queries)

We've just experienced the same problem. For one of our clients, some particular queries are suddenly performing catastrophically slowly (normally takes around 1 second and now takes 22 minutes!). The cause is SQLServer 2000 getting the query plan wrong. We had never had this problem before and I suspect that it's due to the unusual distribution of data that this particular client happens to have in the schema.

We ran the index tuning wizard but it has no suggestions that conclusively fix the problem. The only solution that we've been able to find is to add a join hint on one particular join: instead of using INNER JOIN, we can use INNER LOOP JOIN and that immediately fixes the problem. But of course I cannot specify this with llblgen code. Before I go investigating, do you think it's possible to extend the DQE to support such hints? Is there a chance that I could extend the libraries to provide such support?

During v2 development a lot of discussion was held about this topic. A couple of 'solutions' were tried out but nothing really worked well or was really useable and maintainable.

So this feature won't be added to v2 nor do I see a proper solution possible any time soon. The problem is/was that it has to work in all situations before it can be part of the framework, so also in inheritance situations etc. etc., this was impossible to solve at this point with a proper solution.

Our company is very pro-stored procedure and our group is unique in using llblgen pro and the dynamic query approach. So I'm conscious of this when talking to our DBA's and their reaction is "just add this query hint to the stored procedure" and I say, "hmm, well I don't want to (can't) modify my query in that way". An alternative would be to implement this particular query as a view and create a typed view over it. And maybe that's my longer term solution. Unfortunately this code is in production and altering the way I'm approaching this query will be a significant effort to develop, test and get into production (when we've got so much else that we need to be doing). So I'm currently working with one of our DBA's on alternative approaches (like fiddling with statistics and the like).

Anyway, just wondering if it's worth considering adding essentially database specific hint options to llblgen or whether the longer term solution is to use SP's or Views in this kind of circumstance. (And of course a point in the right direction if you think I can extend the framework to add this myself - we're using 1.0.2005.1).

Interestingly, SQL2005 gets the query plan right every time so there's a compelling reason for us to upgrade

I'm glad sqlserver 2005 does get it right .

In v2 we've added the possibility to fetch entity collections or resultsets from a proc, and also the ability to call db functions. Especially that last part can help in your situation by placing the filter in a function, though it has to be possible to do it in a function of course.

You can of course alter the runtime code in RelationCollection, to emit hints there. The ToQueryText is the place where to add the hints if you need to. Though as you will find out, how to specify which tables to get which hint is very hard, although it might look simple at first.

I'm sorry I can't help you here.

Thanks for the feedback Frans.

Then I think I'll manage our way around our current production problem and switch to a typed view for this query in future versions.

takb wrote:

Interestingly, SQL2005 gets the query plan right every time so there's a compelling reason for us to upgrade

Otis wrote:

I'm glad sqlserver 2005 does get it right .

And I'll push further for production to get upgraded to SQL2005!

As our DBA's have commented: Well they did have 5 years to work on improving things .