At 01:37 AM 5/10/2012, patrick_marten wrote:

>Hello,
>
>I'm using an IB_Cursor to obtain some data for statistics. These data can be viewed in different way (grouped on different fields). Additionally several filter criterias can be applied, depending on what the user would like to do.
>
>I'm checking all this and build my SQL statement. In order to increase performance, I'm using query plan, but sometimes it's causing problems, it seems.
>
>Depending on the final statement, query plan can differ

Exactly. The plan depends entirely on what the engine has to do to fetch the result. Obviously, if the final statement is unknown, any predefined plan is going to be hit or miss. So - don't predefine the plan unless a) the statement is 100% static and b) you know that your plan is better than the one deduced by the engine.

>- at least that's what I see in IBXpert or other tools. I'd copied it and added it to the cursor at the end. Mostly it seemed to work with this single plan string for all cases, but recently I'm getting some errors sometimes:
>"unsuccessful metadata update
>request depth exceeded. (Recursive definition?)"
>
>Once I remove the plan string, everything works fine.

A predefined plan can be "bad" in several ways, even if "mostly it seemed to work". In these cases, it will "work" if it is valid but it is unlikely to be optimal, since the final selection criteria are unknown. In other cases it will fail for various reasons, such as being unable to use the indexes in the plan to arrive at the result, or ending up with a plan that is asked to recurse through too many layers (as in this instance), and others...

>Is it neccessary at all, to add a plan string?

No, almost never. Firebird uses heuristics to determine the plan and, in most cases, it comes up with the best one.

>That's what I thought because of the performance.

Performance depends on plenty of other factors besides indexes. Occasionally, it will bypass an index you think it should use because its latest view of the statistics of that index is out of date. Sometimes, it will ignore an index you think it should use because a table has more than one index with the same definition. If it cannot tell which is better, it could ignore both.

>If that's correct: is there a way to determine a correct plan string for each SQL statement (automatically)?

Yes: avoid using user-defined plans for ad hoc SQL. The optimizer does a pretty good job on the whole, provided your indexing is clean and well maintained. You only ever need predefined plans for a specific statement and then only if the optimizer consistently gets it wrong. You cannot determine this without testing rigorously on typical data. Poor maintenance practices can cause the optimizer to produce a sub-optimal plan sometimes.

This topic is right off-topic for IBO...so if you need some advice about cleaning up and maintaining indexes, ask in firebird-support (unless you are using InterBase, in which case ask in the appropriate Embarcadero forum).

>There are just too many constellations how the SQL statement could be, depending on the criterias defined by the user...

You have hit the nail right on the head there! Don't try to fix something if it aint broke! ;-)

Helen