Subject Effect of Database size
Author Paul Hoshovsky

I have discovered something strange that I thought others may be interested
to read.

I have two DBs, identical format, from different clients. One is about 8 MB
and the other is about 17MB.

Two of the tables in the DB have primary keys and indexes placed on the same
tables. That is, for the History table, I have the primary key on two fields
and a composite index on these two fields. On the other table, called
Records, I have a single primary key on one field and on this same field I
have also an index.

When I execute a query on the smaller DB, I get (for example ) from the
"Select count(*) from History" a value within a short period of time. I
have also executed this same query using IBconsole and also get back the
correct value quickly.

Now when I swap the databases (from the smaller one to the larger one), and
run the exact same query on these same tables, I get an odd event.

After the IBquery gets the result set, it then attempts to read the values
using IBquery1.first. The program goes into a loop
with the CPU usage monitor (from Task Manager on NT) sitting at 100 percent.

When I kill the app, the IBserver merrily continues to spin around with no
change in the CPU usage monitor. (So I have to shutdown the server in order
to regain control....)

Now the question is very simple. I am in trouble if the size of the DB has
an impact. (I refuse outright to accept this possibility).

If the same program is operating in the same manner on two different DBs
(the only differences is the size of the values and the actual numbers in
the tables), what would make IBquery1. First hang on the larger DB and not
on the smaller one?

I would greatly appreciate comments on how to conclusively validate this

Thank you.