is it a good idea to use forceplan within the stored procedure.

I have a stored procedure that is not using the correct query plan, so i have forced the query plan within the stored procedure. Is this a good long term solution, and if not what are the implications of doing this ?
Who is Participating?
Joe WoodhouseConnect With a Mentor Principal ConsultantCommented:
Heh. I'm going to disagree with both my learned colleagues here and say that I regard "forceplan" (and its cousin, forced indexes) to be very much a weapon of last resort, for multiple reasons.

1) (Bill hinted at this one) Regardless of the merits of doing it today, as soon as I force the optimiser's hand I have now created an ongoing maintenance issue for myself and my successors. Just because this might've been the best way to carry out the SQL today does not mean it still will be in a month's time. Data will be different, rowcounts will be different, indexes might have changed, table fragmentation will be different... all of these play a crucial role in determining what the optimiser produces. This is now an ongoing performance and tuning issue where I must prove that the forced way is still superior to anything the optimiser can come up if given a free hand.

I have to say that when I've found forced join orders or indexes at a client as part of a performance & tuning exercise, when I take them out and give the optimiser good statistics, the optimiser beats the forced query plan at least half the time.

So if you challenge yourself to constantly test whether the forced way is still best - say, every month - then I withdraw this objection. But even if you can say that now in good faith, I don't think I believe any DBA would still be doing this every month in a year's time. 8-)

2) I think there is only one scenario that justifies overriding the optimiser... and that's at the end of a performance investigation and you believe you've found an ASE bug affecting optimiser behaviour. (Or perhaps there's a known bug in the patch you're using and there are compelling business reasons why you can't just apply the latest EBF.)

Let's think about this. The optimiser is the result of a lot of good coding from a lot of people who understand it better than you or I. I'm inclined to believe they know what they're talking about. If the optimiser is demonstrably not choosing the optimal query plan ... it is doing this for a reason. So let's find that reason!

In most areas of life it's better to treat the cause rather than the symptom, if we have that option. I always want to know *why* the optimiser picked a non-optimal plan. There are ASE traceflags we can use to find this out and almost always these show something interesting. It may be as simple as bad statistics - and as I just said in my answer to another question, remember that "update statistics" merely refreshes what's already there. If the first time anyone ran update stats they gave the bare default version, then we don't have stats on all index columns, and we're using only 20 values in the histogram. We can do better.

If you have the luxury of some time to investigate, try:

     delete statistics [table]
     update index statistics [table] using 200 values

I find that this one step fixes 50% of weird optimiser behaviour!

The next step is to look at data fragmentation, as this definitely affects optimiser decisions. When was the last time you reorged/rebuilt the tables involved? This seems to fix at least 25% of strange optimiser choices that I've dealt with.

Other things to look at would be data skew, and whether the optimiser has been told to deal with that or not, and even server-wide configuration issues like large I/O, cache sizes, whether backward scans are allowed, etc...

I'm going into more detail than I meant to here, so my summary is : forced query plans are at best a short term solution. Long term they are a maintenance issue. The optimiser does everything it does for what it thinks are excellent reasons. Fixing the cause lasts forever. Fixing the symptom means you have to fix it again tomorrow.
It is ok.  You might be giving up possible gains if something changes, such as a better index being found.

The new Abstract Plans feature gives a great deal more power and flexibility for bypassing the optimizer.
You are probably fine until you do an upgrade from say 11.9.2 to 12.5.x or from 12.5.x to 15.

The optimizer is a lot smarter in 15 so you will want to reevaluate the query plans on anthing that has given you a problem.

The good news is that, with the exception of the occaissional anticdotal exception, Sybase is pretty good about making sure the wheels don't fall off of things when they rework the optimizer.

As far as abstract plans go, they are still rather difficult to use.  At this point, if FORCEPLAN is all you need to get a good result, I would stick with that.  The other relatively simple way to kick the optimizer to do what you want is to use index hints on the tables in the FROM clause.  Generally, between those two techniques, if you can't get the optimizer to do the right thing, you have another problem with your query or database design.

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

grant300Connect With a Mentor Commented:
Joe reitterates a good point about maintenance and also adds some good techniques for dealing with the underlying problems.  If you can get the optimizer to do the right thing on it's own, it saves a bunch of hassles down the road.

The one problem with the "tweak it till it works" approach is that it is possible to wind up with a rather brittle solution; something changes a little or stuff drifts over time and you have to go back and do it again.  This is really bad when you are building a system that will be deployed at many sites with data volumes that range over an order of magnitude and characteristics that vary significantly from installation to installation.  The operations are 24x7 with some lulls in processing so we are lucky to get them to schedule update statistics on a nightly basis.  Finally, the bulk of the tables have composite primary keys where the first field segments the data in a very scewed manor, about 75 percent of the data in one value and the rest in another 20 to 40.

I have a client in exactly that situation.  It is so bad that the test data available is, at best, an approximation of what the deployed system will actually see.  Further, the sites are not readily accessible.  Finally, it is permissable to do some platform/server tuning on each site, having a different code base for each site is out of the question.  It's a tough (and justifyably inflexible) situation that we just have to live with.

I, like Joe, normally use FORCEPLAN only when I can't get it work anyother way.  The client has a guy that makes very liberal use of FORCEPLAN partially to compensate for the fact that he writes overly complex TSQL.  When he started doing it I cringed and even removed them from the code where they were not necessary.  Experience has shown us that tuning/testing with the lab data we have sometimes results in performance problems when the system is fielded against real data.  To bolster Joe's point, usually I have been able to fix it by reworking the queries and, in some cases, going so far as to break a complex statement up into two pieces with a temp table bridging them.  In a couple of cases I have had to resort to FORCEPLAN to get consistent results.

I guess the moral of the story is thee fold:
1)  Write clean code that the optimzer has at least some hope of unwinding
2)  Consider your deployment and maintenance issues
3)  Let Sybase do it's thing whenever possible

Boy, who would have guessed that a question about FORCEPLAN would have turned into a philosophy debate :-)

Joe WoodhousePrincipal ConsultantCommented:
All questions turn into philosophy eventually. 8-)

Bill has very good points about when it may not be possible or advisable to "do it by the book" (if doing it by the book means forced query plans as last resort only). Of course we don't always have the luxury of the long slow road to the best possible SQL and dataserver architecture.

I'll also admit I've forced indexes every now and then just to buy time in production while I try to troubleshoot at leisure in a test system. 8-)

The orginal question was is this a good idea *in the long term*, and I have to say, in the long term, no. The costs will eventually come to outweigh the benefits. But that's a decision we can't make for the original poster - they're the only one with a good understanding of what the non-technical issues are (business requirements, politics, etc etc etc) that could override a compelling technical case.

I hope between us we've shed some light on what the implications could include.
colechrAuthor Commented:
thanks a lot guys for your comments. I will try some of your suggestions and let u know how i get on.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.