I've a local DBase table I'm trying to index with FoxPro index (*.CDX).
These are the settings:

DBEADMIN:  native driver -> DBase -> level 25

Query1:=TQuery.Create(self);
Query1.SQL.Add('CREATE INDEX ON N_ARCH_PAZ "mytable.dbf" (n_arch_paz)');
Query1.ExecSql;

The index is correctly created, but making SELECT ... WHERE for values of
the key, for some values of the key, results in more than 1 minute waiting.

This is a highly reproducible bug.

By using the DBase index (*.MDX) the problem seems not happen (not on the
same key values I tested and more ...).

Because I based my application on FoxPro format (because of a lot of other
tables with memos), I urgently need a fix.
I submitted a bug report to Inprise and received no answer also after two
more reports.
Finally, I have a ZIP file (2MB) with the sample, both data and code, for
the bug, but I can't submit to the bug report nor append here. If any is
interested, please reply a E-Mail address where I can send it or contract me
to the E-mail below.

Any suggestion will be greately appreciated.
Tank you
eugenio.cerves...@pnhosp.inet.it

On Thu, 7 Oct 1999 22:50:12 +0200, "Eugenio Cervesato"

What version of the BDE are you using that you have to adapt the level
of the dBase driver?  When you want to create the index using local
SQL, you need to use a FOXPRO alias.  When you create the index via
TTable.AddIndex you can just set the TTable.TableType to ttFoxPro.

I've seen a similar problem report here before (I think it was from
Leo Siefert).  AFAIK this is only a problem in BDE 5.x, not in BDE
4.51.  There was also a work-around, like adding an ORDER BY on the
field used in the WHERE-clause, together with another field.

HTH,

Jan

The problem exists in 4.51 as well - in fact it was first identified in
4.51 on our network then tested and found to persist in 5.01 and 5.10.
So far the only work-arounds I have turned up are:

1. Return a live result set from the query - equivalent to setting a
filter instead of returning a result set.  The down-side of this
approach is that if your result set is sparse it can be very slow to
navigate through it.

2. Remove the index.  This will result in all queries being slow, but
not as slow as the "problem' queries.

3. Add an additional record or two to the database that will be included
in the problem queries.  This will solve the problem even if the added
records are marked for deletion.  This was the actual interim solution
that I implemented while waiting for the arrival of a different database
engine.

 - Leo

Thanks to Jan and Leo for help.

Leo: more detailed tips

solution 1) do you mean that I only need to set Query1.RequestLive = True; ?
It sounds VERY interesting if so...

solution 2) obvious

solution 3) I don't understand what that mean. Should I add a record to the
table with the same key value as that I'm searching before every time I make
a search?
This sounds a large space and time wast.

Anyway, today I discovered that the corresponding .MDX index wors fine, but
this brings me in another bug. In fact the index generated by DBE is not
compatible with that generated by Clipper 5.2e DBFMDX driver (for Dbase IV
tables). Looking in hex into the two .MDX files, I discovered the
difference: The first report the following bytes: 4 byte=phisical record
number, 39, 29, 4byte=key value (numeric field);
the second reports: 4 byte=phisical record number, 39, 09, 4byte=key value.
It's quite surprising! I need full compatibility between DBE and Clipper!!

Any suggestion?
Eugenio Cervesato

The point 1) of your

37FE3894.401E4...@senate.state.mi.us...

On Fri, 8 Oct 1999 22:46:59 +0200, "Eugenio Cervesato"

[...]

Just a slight clarification. If there is a discrepancy between how the BDE
and Clipper produce dBASE indexes, the incompatibility is on the Clipper
side, not the BDE. Borland originates the dBASE table (and index)
specification. Others must comply with that standard or else their
*emulation* of a dBASE table will be out of whack.

==========================================================================
Steve Koterski                  "Computers are useless. They can only give
Technical Publications          you answers."
Borland                                       -- Pablo Picasso (1881-1973)
http://www.borland.com/techpubs/delphi

On Fri, 08 Oct 1999 14:31:48 -0400, Leo Siefert

Leo,

This is weird.  I'm using FoxPro queries on 4.51 for quite a while
now; never seen this problem.  But from your last work-around I
understand this is some kind of 'magic-number' problem?  IOW, it will
only show when you have a certain number of records?

Do you still have an example of such a problem file that you can email
to me?
(o Eugenio, se hai un esempio, puoi mandarmelo?)

There's something I don't understand about this.  When you have a
single-field index that accommodates the WHERE-clause, local SQL would
use this index to limit the records it returns from a live query.  How
can it make a difference then whether it filters out many records or
not?

Are you sure that all it took was using live queries?

Jan

On Fri, 08 Oct 1999 14:31:48 -0400, Leo Siefert

I've done some tests with an example file Eugenio sent me.  The
problem is pretty heavy: the slowdown on some queries is as big as
creating a completely new index file!  The same query using other
parameter values in the WHERE-clause returns almost immediately.  This
surely qualifies as a bug.

However, using exactly the same file, I couldn't reproduce the problem
with BDE 4.51.  Hence, this still seems the best workaround.

Now I understand what you mean with a 'sparse result set'.  The
problem is the same whether you have live queries or use a TTable with
a range.  Only you don't notice until you skip through the result set.
At some records, skipping becomes very, very slow.  When you add up
these slowdowns, you will very likely have the same net slowdown as
for non-live queries.  So the problem clearly is the index itself.

Indeed.  Or make it an expression index so that local SQL won't use
it.  Using an expression index didn't resolve the problem using a
TTable and SetRange though.

I've tried this, but after adding about ten records for the same key,
I gave up.  I can imagine that it is actually possible to use this
workaround, but then it would only be usable when the table is pretty
'static'.

Jan

Yes Jan
I verified today that DBE 4.51 works fine with my table. The bug has been
introduced later.

I summarize here for the others: I have a Dbase table about 12 MB, 330,000
records indexed on a numeric field. By using the FoxPro driver to create the
index (.CDX) and making a search 'SELECT * FROM <tablename> WHERE
<indexedfield>=<key>' for some values of the key (and always)  it takes more
than 1 minute to complete!
We now know that this happen for BDE versions 5.01 and 5.10.

I submitted the bug to Borland, but they answered they don't develop BDE any
more!! The only thing they can do is to publish the bug (but I wasn't able
to explain in the submitted form exactly what the bug was ... I'll try again
... with your help.

Best regards
Eugenio Cervesato

380222b6.1481550...@forums.inprise.com...

On Mon, 11 Oct 1999 21:50:00 +0200, "Eugenio Cervesato"

I guess you will be looking for a BDE replacement soon then (like Leo
and I already did).  I'm still using BDE 4.51 local SQL queries on
FoxPro at the moment though.

You can find a list of BDE replacements at:
   www.kylecordes.com

I'm using Advantage myself, so if you need more info on that, just
email me.

Buona Fortuna,

Jan

Yes, but do note the difference in performance this entails.  It is an
entirely different mechanism.  Also, RequestLive works only under
certain circumstances - there is a set of rules for when it works
somewhere in the documentation.

No.  In my case, I found that only a few specific queries were adversely
affected.  I simply added a deleted matching record for each of these
cases as they were identified.  This was only an interim solution for
us, as the database is continually growing.  We have since replaced BDE.

Could be...

I found this also, but did not explore too far, as our application
requires CDX indices.

Never had much luck with the Clipper DBFMDX driver.  If you do use this
you should be aware that there is a maximum size for DBT files which is
enforced under Clipper (32 Mb, I think).  Interestingly, it actually
builds larger DBT files without error, but when it tries to read them
the pointer wraps back to the beginning of the file and returns the
wrong memo for the record.

I would suggest that if RequestLive does not solve the BDE problem you
might look into BDE alternatives.  Currently I am trying out Advantage.

 - Leo

Definitely, though it seems unaffected by adding additional records to
the database, unless they are included in the "problem" query.

Will do.

Yes, but note that in the production app there is also a necessary
expression index on both of the fields in the where clause and a
necessary order by clause on yet another indexed field.  I believe that
it is the order by clause which results in the slow navigation speed in
the result set.  Creative reindexing could probably solve this, but we
also offer a free-form query generator in the app, and it is impossible
to optimize all queries in this manner, especially with "where notes
like '%MEAP%'".

 - Leo

Actually, I can't reproduce it in 4.51 now either with my test file.
The problem, however, was first reported by users with 4.51 running on
their systems and upgrading to 5.01 was the first solution I
attempted...

 - Leo

It seems this discussion came to the end.
Thaks for ccoperation
Eugenio Cervesato

38030484.1539355...@forums.inprise.com...