At 09:03 AM 11-07-01 +0000, you wrote:

>Hi there,
>I came accross a problem yesterday when I tested my app on a larger
>GDB (over 2 files). The problem being it took over 5 mins for a
>query to open. After checking the query, I found I had to change
>a "Join", after which, the query openned in 5 - 10 seconds (on the
>larger DB).
>
>"Hooray - that's sorted", i thought! I then went back to testing on
>my normal, smaller GDB (on 1 file) and low and behold, that one now
>takes ages to open.
>
>If I revert the "Join" back to how it was in the first place, I am
>back in the position I started.
>
>The users of my app will be able to select which GDB they wish to
>attach to.
>
>If the larger GDB was on one file also, would that make a difference,
>ie: is it the amount of files the GDB is split over which determines
>how the query is optimised, or is it simply the amount of records in
>the tables concerned.
>
>How the hell do I go about soting this out! Is this an IBO thing, or
>Interbase itsef.

Probably a "programmer thing". The first obvious thing to do is drop a Monitor_Dialog on your form and watch what's happening as the statement goes through its cycles. You should be able to see where the bottleneck is.

Of course, the size of the table matters; but the size of the GDB file or whether it is spread over multiple files doesn't.

Things that matter in JOINs are specifically INDEXES. You will get poor optimization in joins from bad or non-existent indexes on JOIN criteria; and, if you are using implicit joins, it will slow things down on the client side because IBO has extra parsing to do.

Show us:

the SQL for the dataset (both versions)

the query plan generated by the optimizer for each version of the statement. (The Monitor dialog will give you this on prepare; or you can run the query into the DSQL tab if IB_WISQL and see the plan there after you click the Prepare button...

Check out...

- the possibility of duplicate indexes on columns being used in joins (typical happens when you create an explicit index on a primary or foreign key column)

- existence of a bad index (= an index or FOREIGN constraint on a column that links up with a lookup table)

- non-indexed columns as JOIN criteria (esp. long character columns)

- mixing implicit and explicit JOIN syntax (not just slow but can also return nonsense results)

Also eliminate the possibility that garbage collection is holding up the start of your query - this can be quite hefty if you have recently deleted or inserted large numbers of rows inside a single transaction).

..for starters...

Helen




All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________