Hello:

The table has 3.2 Million records. There are 2 indexes. The
primary key is as follows: CONSTRAINT PK_ID AS PRIMARY KEY ("ID")

The index is as follows: CREATE UNIQUE INDEX OWNERNAMEIDX ON
OHIOFUNDS ("OWNERNAME", "ID")

Ownername is not unique so ID, which is unique, is added.

When I initially connected to the database connection took 4
minutes. After posting a message about this Hellen suggested I name
the database something other than .gdb because of a Windows XP
problem. I renamed the database to .ib and connection time went
down to 30 seconds. I have tried a few other extentions but
connection time seems to stay at around 30 seconds. This only
happens on the 1st connection after a reboot. Subsequent
connections are instant untill the next reboot.

In any case, the 1st time I do the query it took several minutes.
Subsequent queries, whether for the same ownername or not, are much
faster but still around 2 to 3 seconds.

I found that my setting up a master/detail relationship between the
small and large file it works reasonably well. However, here is what
happens. As long as I move record by record or search for a
ownername that is no to far in the file from the ownername I am on,
all goes well. But let's say I am on ownername ALBERT and I search
for ownername SMITH. A message box appears stating FETCHING ROW#
XXXXX, the ROW continues to update and the system goes off into
never never land for some period of time, eventually, SMITH will be
displayed and all works fine. THIS ONLY HAPPENS ONCE. After that,
searches anywhere on the file work fine and search time is around a
second or less. My only guess is that the applicatio is fetching all
records from the server causing the delay. To prove this I set the
queries AutoFetchAll property, which is normally False, to True.
Now when I restart the application it takes about 2 minutes to come
up but, once up, I can search for any record without the 1st record
search delay.

All this being said, none of it actually fixes my problem. The BDE
application has 2 grids. When the user moves through the small grid
(which displays only owners who owe a large enough amount of money
to make it work persuing) the same owner is displayed in the large
grid (which shows all monies owed by anyone having the same owner
name) Since the people that entered the information make typos etc
the lawyers want to see all owner by the same or close names like
SMITH or SMITHS or SMIHTS. In the BDE version I simply do a locate
into the large file for "ID" every time the small file scrolls.
This keeps the large and small files in sync. The user seatches for
SMITH in the small file and anyone close to SMITH is dieplayed on
the large grid. Works great. This is what I wanted to accomplish.
I really don't want to SELECT a subset of records in the large grid,
I simply want to establish position in the large files based on 'ID'
(which will be the same ID) in the large file. Selecting a subset
does not accomplish this. Master/Detail sort of works but limits
the large grid to only SMITH.

Problem is if I use LOCATE on "ID" the message box about fetching
records is diplayed and the fetch takes HOURS!!!. Eventually I get
control again and the proper records are displayed in the large
grid.

Keylinks seemed to be the answer. I thought if I linked the files
together with keylinks "ID" all, would work. Problem is, if the
user moves record by record with a navigator bar, all works exactly
as expected, if the user is at ALBERT and searches for BAKER, all
works as expected, however, if the user is at ALBERT and searches
for SMITH, the fetching records message box is diplayed and the
fetch takes HOURS!!! God forbid the use is sitting near the
beginning of the file and clicks the Last Button on the navigator
bar, the fetching records message box is displayed and it truely
will be the last thing the user does for several hours!!!

--- In IBObjects@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@...> wrote:

>
> Sorry John, I don't believe you.
>
> SELECT * FROM XXXXXXXX
> WHERE OWNERNAME = 'RANKLEY'
> ORDER BY OWNERNAME
>
> cannot possibly take minutes to return a handful of records if
> OWNERNAME is indexed. Well, maybe it is possible, but then there

are

> other problems that causes this (e.g. Windows XP doing a file copy

of

> .gdb files before allowing anyone to access them or there being
> thousands of fields to transfer and you connecting through the
> Internet), not the select itself.
>
> Firebird is generally fast, and - if treated correctly - a lot

better

> than Paradox to handle large tables. I would expect the above

query to

> execute in less than a second, not two seconds.
>
> So, what is it that you're not telling us? Is the above SQL the

exact

> query you're executing or are there other parts you've not told us
> about. What's the reported plan (you can find this by e.g. typing

it

> into IB_SQL and prepare the statement) and your table/index

definitions?

>
> HTH,
> Set
>
> --- In IBObjects@yahoogroups.com, "John Costanzo" wrote:
> >
> > Hello:
> >
> > I meant to say OWNERNAME. Ownername is the field that is

indexed

> > and the query selects ownername = xxxxx.
> >
> > > I then wrote a IBO test application. One databound grid, 1
> > > IBOQuery, 1 button. The SQL for the query is
> > > SELECT * FROM XXXXXXXX
> > > WHERE OWNERNAME = RANKLEY
> > > ORDER ON OWNERNAME (Ownwername is a defined index).
> > > The Grid is linked to the query. The button code simply says
> > > query1.active := true. When I click the button the IBO cursor
> > > is displayed and the program goes to never never land for

almost

> > > 2 minutes before the grid is displayed. Unless I am doing
> > > something real wrong, this would mean that every time the user
> > > selected a name from the top grid there would be a lag of at
> > > least 2 minutes (I say at least because I selected Rankley
> > > because he only had a dozen or so records). What would happen
> > > if I selected SMITH?
>