>Thank you Sergey. Differences are clearly indicated by the results of SYS(3054,11).
>
>When I LIST STATUS it appears to me that all of the tags and collation sequences are identical. Can you (or anyone) give me some guidance as to me what else I need to check? I am obviously missing something.
>
>One more slowdown clue. The very first user in the morning does not, at first, see the speed degradation. However, as soom as a second user opens the .DBC's files the slowdown occurrs. Again, this behavior only appeared last week. For years previously we never experienced this issue.
>
>Below are both the "fast" an "slow" results of SYS(3054,11):
>
>* Slow ( Approx. 20 seconds):
>Using index tag Ptname to rushmore optimize table patient
>Rushmore optimization level for table patient: partial
>Rushmore optimization level for table subscriber: none
>Rushmore optimization level for table medcert: none
>Rushmore optimization level for table typepick: none
>Rushmore optimization level for table certkey: none
>Rushmore optimization level for table meddetail: none
>Rushmore optimization level for table procdetail: none
>** The differences follow:
>Joining table typepick and table medcert using temp index
>Joining table certkey and intermediate result using temp index
>Joining intermediate result and table meddetail using index tag Meddtlkey
>Joining table subscriber and intermediate result using temp index
>Joining table patient and intermediate result (Cartesian product)
>Joining intermediate result and table procdetail (Cartesian product)
>
>
>* Fast (< 1 second):
>Using index tag Ptname to rushmore optimize table patient
>Rushmore optimization level for table patient: partial
>Rushmore optimization level for table subscriber: none
>Rushmore optimization level for table medcert: none
>Rushmore optimization level for table typepick: none
>Rushmore optimization level for table certkey: none
>Rushmore optimization level for table meddetail: none
>Rushmore optimization level for table procdetail: none
>** The differences follow:
>Joining table patient and table meddetail (Cartesian product)
>Joining intermediate result and table subscriber (Cartesian product)
>Joining intermediate result and table medcert (Cartesian product)
>Joining intermediate result and table procdetail (Cartesian product)
>Joining intermediate result and table typepick (Cartesian product)
>Joining intermediate result and table certkey (Cartesian product)
>
>Thanks again!
>
>Spencer

Hi Spencer,

There is a difference in the order in which tables are joined and the slow query builds a bunch of temporary indexes, which could be expensive if done over network. The change in query plan could be caused by the addition of new fields because this changes table's size, which is one of the factors considered by the query optimizer. I would try to use FORCE keyword and make sure that all tables are listed in the FROM clause in the same order as they are joined by the fast query.

... FROM FORCE ...

Thanks,
Aleksey.