At 12:19 AM 22-02-02 +0200, you wrote:

>hello
>
>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.

This is where your problem lies. Primary keys have indexes created
automatically for them. If you create another identical index (and
InterBase won't stop you), you actually defeat the optimiser when it
constructs the plan for your query. Because of the ambiguity of two
identical indexes, it uses *no* index. So it will join, sort, etc. by
organising the column in natural order. This will be expensive if it is
sorting, joining or even just selecting with a WHERE on alphanumeric
columns with no index support.

>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....)

This is not caused by the relative size of the databases - both are very
tiny by client/server standards. The difference in their behaviour
indicates that something is wrong with that table in the second database.
It could indicate that the selection is encountering corrupted data,
corrupted indexes, a lot of uncollected garbage, or something else quite
anomalous.

>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).

Rest assured, the DB size has NOTHING to do with a problem like this.

>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?

Running gfix and gbak will probably turn up what your problem is. Get hold
of this document and follow the instructions exactly:

http://www.ibohoenix.com/ibp_db_corr.html

Another thing you can do is create a new temp table with exactly the same
structure as the problem table and, in IB_SQL, pump the data from the
problem table to the temp table. Don't put any keys or indexes in the temp
table...just include the not null constraint on the column(s) that are used
for the PK in the bad table.

Once it is populated with data, add the primary key constraint. If it
works, then you know that the corruption is in the indexes, not the
data. All that may be needed then is is to drop and recreate the original
table and pump the data back from your temp table.

Don't create those duplicate indexes at all!!

You mentioned IBQuery - are you by some chance mixing IBX components with
IBO components in your application?

regards,
Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at
www.ibobjects.com