Hello Jay:

Thanks for the tip about datasource.ondatachange. I will try it.
As far as my particular application goes, the client wants it to
work exactly as the Paradox version works, thuis forcing me to do
lookups into the 3 million+ file. I will keep messing weith this in
my spare time but for now it looks like I will have to stick with
the BDE/Paradox version since it runs circles around the IBO version.

Thanks

--- In IBObjects@yahoogroups.com, "Jose Ostos Turner" <jostost@...>
wrote:

>
> With Paradox, when you open a file, it does not read all records,

it just positions itself according to the index. With SQL (any DB)
will fetch ALL records and keep them in memory it DOES NOT position
itsel according to the index. With a WHERE condition it will only
read ALL the records that comply with the condition. If the
condition can use an existing index, it will use it and can be very
fast (reading only the records according to the index), if the
condition can not use an index, it has to read ALL records. That is
the way that SQL databases work.

>
> As I said before, ALL records will be read into memory either at

the same time (FecthAll=True) or as required (FecthAll=False). Once
you have obtained the records (resultset) in memory, the work is
being done by the client and not the server, and the client has NO
KNOWLEDGE of any index and will continually read ALL records in
memory secuentially which is very inafficient.

>
> You have to find a way to do a diferent select the detail records

to avoid this, and you have to figure out which is the best for your
needs.

>
> At some point in your first posts, you mentioned the use of the

AfterScroll event. My experience has been that it seems to executes
this event many times unnecesarrily. If you have your LOCATE int
this event, it will probably be executing several times instead of
once as you would expect. Under your circumstances this will GREATLY
MAGNIFY you problem. I Suggest trying the Datasource.OnDataChange
event instead checking that "Field=nil" to make sure it only
executes when changing records. AfteScroll make be executing every
time a field changes in the record, e.g. if the record has 15
fields, it may be executing 15 times on every record cjhange.

>
> Hope this helps.
> Jay
>
>
> ----- Mensaje original -----
> De: John Costanzo
> Para: IBObjects@yahoogroups.com
> Enviado: Miércoles, 16 de Agosto de 2006 08:04 a.m.
> Asunto: Re: [IBO] Problem using locate
>
>
> 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?
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>