I used paradox for many years and had many SERIOUS problems when some tables got very large (1 million + records) that would get corrupted very frecuently for no real reason. It is a KOWN FACT that the BDE has bugs that corrupt paradox tables which is the main reason Borland advises aginst using paradox files.

Like you, I also has situations in which the paradox way of handling files seemed to be much faster and more practical. The problems I had with corrupted tables is what really forced me to search other alternatives. I was also reluctant to do it. However, as I started getting more familiar with SQL and Client/Server, it really is much better, faster, easier, flexible than the the BDE/Paradox way of doing thing on the long run. However, sometimes I still miss BDE/Paradox for certain cases (like the one you mention) that would make things easier.

I have found that these cases are minimal and worth working out a diferent solution. I also look back at a lot of work-arounds that I had to do with BDE/Paradox that now are so fast and easy to do now with SQL.

Regards
Jay

----- Original Message -----
From: John Costanzo
To: IBObjects@yahoogroups.com
Sent: Thursday, August 17, 2006 7:42 AM
Subject: Re: [IBO] Problem using locate


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





[Non-text portions of this message have been removed]