I am trying to optimize some queries which have turned out to be
surprisingly slow since moving from DBISAM4 -> EDB.

Can anyone please give some hints/tips on what we should be looking for in
the Execution Plan to indicate whether a query is fully optimized and what
(if anything) can be done to better structure the query or to improve
performance?

If I can start the ball rolling from what I've picked up from reading the
news groups :-

---------
HINT 1
---------
If you see "[Row scan (table name)]" in the Execution Plan then there should
probably be an index on the appropriate field.

e.g.
The following join condition was applied to the room table:
("room"."id" = "inv"."room_id" [Row scan (room)])

This means every row is being read in the "room" table. Placing an index on
"room.id" (in this case a primary index) will speed up the query. The
Execution Plan will then read as follows :-

The following join condition was applied to the room table:
("room"."id" = "inv"."room_id" [Index scan (room.PrimaryKey)])

---------

Looking forward to some more input please from you EDB Gurus now!!! For
example, if we see the following :-

....The optimizer attempted to re-order the joins to a more optimal order
The optimizer successfully re-ordered the joins into this more optimal
order:...

Does that mean we should manually re-order the sql ourselves? i.e. is there
a big overhead involved when the query optimizer re-orders joins for us?

Many thanks!

Hedley


I think you've pretty much got it. Basic rules are indices (of the same collation as the column or with a collation clause) for both sides of every test and for any ORDER BY clause. If there are joins you can't get a sensitive result but for other queries check that you've asked for and received a sensitive result - no temporary files needed.

You might also want to think about these
-------------------------------------------------------------
NOJOINOPTIMIZE Clause

The NOJOINOPTIMIZE clause ise used to force the query optimizer to stop re-ordering joins for a SELECT statement.  In certain rare cases the query optimizer might not have enough information to know that re-ordering the joins will result in worse performance than if the joins were left in their original order, so in such cases you can include this clause to force the query optimizer to not perform the join re-ordering.

Note
Only INNER JOIN expressions can be re-ordered by the query optimizer.  LEFT and RIGHT OUTER JOIN expressions cannot be re-ordered.

JOINOPTIMIZECOSTS Clause

The JOINOPTIMIZECOSTS clause is used to force the query optimizer to use I/O cost projections to determine the most efficient way to process a join expression. If you have a join expression with multiple conditions in it, then using this clause may help improve the performance of the join expression, especially if it is already executing very slowly.
-------------------------------------------------------------

Since the join reordering takes place before the query is run (unless I'm wrong) it shouldn't have a major impact on a query. However, there can be conditions in which Tim's join optimiser isn't as good as the programmer (rare and getting rarer) so on a slow query with joins its worth a try.

I do think Tim has missed one important bit off the execution plan which should read "what do you expect when you have tables that big!"


Roy Lambert [Team Elevate]