Subject RE: [IBO] Slow query question
Author Svein Erling Tysvær
Hi Nico,
> Hope you still have some time for me ? :-)
A little bit, at least...

> First I've tested this interactively, and I must say that your
>statement is executing faster then my JOIN. Strange, because everybody is
>recommending JOIN's with IB ??
Joins are fine, but you're not really joining two tables, just looking up
one field to see if it exists in your other tables. When returning 0 or 1
record, I think I'd prefer a subselect to a left outer join.

>Second : I found what was the problem, very strange : when I delete all my
>TIB_LookupCombo's from the form, it's preparing fast. Maybe there is a
>problem with my design of the form. I will try to explain what I'm doing :
>When a user is just "looking" at the data, I set the datasource of the
>TIB_LookupCombo's to the main dataset "DEALER", and display for example the
>description of the Language field, example "ENGLISH". When a user wants to
>add or edit a record, I set the datasource of the TIB_LookupCombo to the
>lookup table and I set the Keylinks, so the whole Language table is
>displayed and the user can choose for example another language.
>Now, my question is, instead of picking up all the Descriptions fields with
>JOINS or SUBSELECTS from the lookup tables, is it better to open all the
>lookuptables immediatly and work with Keylinks and remove the JOIN's ??

Then I guess it prepares the lookup datasets as well. Maybe you should
check if these prepare quickly as well? IBO's Getting Started Guide has
some useful information about TIB_LookupCombos. If you do it the way it
describes and it works (that's where I currently have problems, but I'll
investigate a bit more before asking here), you should be fine. I've never
seen a LookupCombo work with the JOIN-parts of your query, how do you
manage that?

>With a subselect, only one record is retrieved to the client, with KeyLinks,
>there will be 6 queries open, and for every query, let's say an average of
>50 records will be transfered to the client.

If these are pretty static and used as a lookup several places, you could
follow the advice given on this list some time ago: Use TIB_ComboBoxes and
fill them at program startup (Items and ItemValues).