At 12:23 AM 17-08-01 +0200, you wrote:

>I have a really strange index-problem with a lookupquery. But before
>some background info. I have a table CUSTOMERS with a col CSSAPNR and a
>index I_CSSAPNR_A on this col, which is not unique and ascending. On a
>form I show all customers ordered by CSSAPNR and opening the query takes
>0,03 sec. I deactivate the index, opening takes 1,4 sec and after
>activating it again it takes 0,03 sec. That's all normal and as
>expected.
>Now I have form with all CONTACTS. Every Contact belongs to a customer
>linked over a foreign key. The customers-table is now a lookup-query for
>contacts and the customers are ordered by CSSAPNR again. After I open
>the contacts-query, the customers-query will be opened. Opening the
>Customers-query takes 0,03 sec. I deactivate the I_CSSAPNR_A-index and
>it takes 1,4 sec. But when I now activate the index again, it still
>takes 1,4 sec !!!! It seems, that this lookup-query doesn't use the
>index, but the plan shows, that the index is taken.
>This behaviour is reproducable. When I insert records in a empty
>database, the lookup-query works as fast as the normal query. Now I
>deactivate and activate the index, the normal query works as fast as
>before, but the lookup-query works as if the index is still deactivated.
>
>I really go crazy here. Has anybody a idea, what could be the reason for
>this behaviour ??

Guido,
Does this slowness persist? Normally, the first query on a large-ish table will be slow after reactivating an index because it is the first opportunity the engine has to rebuild the index - hence a slow-down is expected.

Helen


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