Hi,

I just inherited a situation (mess) with a multi user Paradox application.
This application started in Delphi 1 and is currently in Delphi 3.  When the
company released the 32 bit version, many of the customers were disipointed
in the slower performance.  The process of starting up and opening the
tables was significantly slower.  As a result, there is a resistance to
upgrading to the current version.

Is there something I should look for in the way tables are managed?  Is
there any environmental changes I should consider, BDE replacements?  Anyone
else know of this and have any other advise?

Thanks in advance,

Cash

One thing that can cause a delay in opening tables is a virus checker
running on the server that checks all files as they are opened.

Bill

--

Bill Todd - TeamB
(TeamB cannot respond to questions received via email)

32 bit BDE is slower than 16bit BDE opening tables
You need to structure the application so that forms and tables are only opened
when they are first needed and not open everything on startup

there are BDE replacements that might give you better performance but not for
Paradox tables.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

About one week ago one guy said in borland.delphi.non-technical that querying
with Access database is much, much faster than with BDE/Paradox. He said that
opening and sorting a 250.000 record file only takes two minutes with
Access, and that was supposed to be extremely fast.

I did create 250.000 record test application also, and tried to open
and sort it both with D3/BDE4.51 and D1/BDE2.52. There were no indexes
on the table, and the simple SQL sentence to select and sort the table
according one field was:

 SELECT * FORM PARTS
 SORT BY A17             {A17 is the Field name}

Delphi version               Win95        NT4.0
--------------               --------     ---------
D3                           1:45 min     1.19 min
Database Explorer (32-bit)   1.47 min     1.24 min
D1                           0:59 min     0.44 min

The record count, 250.000, was the same, but otherwise the test material
of course was not exactly the same as with Access. But as far as this
rough test shows, BDE/Paradox was almost 50% faster than the Access
database.

But the strange and interesting part is that 16-bit BDE on D1 was
much faster than 32-bit BDE 4.51. D1 was faster even on NT4.0, on
a true 32-bit operating system.

When D2 came on the market, there were benchmarks that showed DB-accessing
on D2 to be much faster than on D1. I do not have D2, and I have
not run those tests, but my experience is that D1 DB-apps would
always feel 'snappier', be faster in use, than those 32-bit
BDE-equivalents.

Markku Nevalainen

Hello Everybody,

I repeat Bill's point that it's the OS which is causing the considerable
difference.  There maybe performance locks caused by the file systems
(FAT32, for example).  The users' systems might not be optimized.  And, the
BDE might not be optimally be configured.  Please refer to tbe Borland
technical documentation for the BDE32 optimizations.  Here is a very famou
sexample that I found on some borland newsgroup:

Without the BDE Optimizations, a query with an Access Table (2 million
records) & BDE 4 took 72 hours to complete.  But after the "BDE
Optimizations", it took only a few seconds to execute!

I don't know specifically about these optimizations!

But please let me know if you happen to come across that information!

Qamar Ali,

With my tests OS was not making any considerable differences. Usually
with BDE/Paradox networked databases NT server is slower than W95/98
servers. But in my single user tests I got NT always to be faster.

Also I did run my tests on FAT-16 partitions only. And everything was run
on the same computer, with same BDE settings, just rebooted it to W95
and NT4.0.

I did not do any special optimizations. But if pure Access environment
took about 2 minutes to sort the 250.000 record database, and all the
BDE versions did it much under 2 minutes, then the BDE optimization
can't be totally  wrong.

If anyone can show any numbers, where BDE-32 is faster than those 16-bit
BDE versions, I would be more than reliefed.

Also, if someone has some kind easily distributable standardized data
+ benchmark environment, I would be interested.

Markku Nevalainen

Tell me the best Optimizations for BDE!
I using Both TTable & Query with Paradox 7

Thanx!

I have written two simple sql timing apps one 16 bit and one 32 bit (from
Delphi2 - version 3.0 I think ???) and run them on the same machine,
specifically because:
a) the same sql syntax can generate different results on 16 bit and 32 bit
(particularly outer joins with where clauses)
b) an outer join with where clauses (replace 'and' with 'where' in 32 bit to
get the same results) the speed difference was 0.14 secs on the 16 bit and
16 secs to 20 secs on the 32 bit. This is joining a 600 record table with a
400 record table (paradox).
The 32 bit BDE is therefore unusable to me under these conditions (for a
commercially distributed app). Particularly as after porting the app many
queries returned different results.
Incidentally in SIMPLE sql  'select * from . . ' type operations on paradox
tables of this size the 32 bit typically takes 25% to 50% longer than the 16
bit.

Any answers on any or all of the questions below would be very welcome:
Where can I find a resource on optimising the 32bit BDE ?
Does it make much of a difference ?
Are later BDE versions 4 & 5 any quicker than version 3 ?
Are there inconsistencies in sql syntax/results between versions 3 & 4 or 4
& 5 to be wary of.

Thanks

John Olliver
Olliver Communications UK

The 16-bit BDE used QBE (in other words translated the SQL
internally to QBE) for all SQL queries. QBE is Paradox's
native query format and for which it was optimised. Therefore
32-bit queries do take longer to run.

The different results between 16-bit and 32-bit BDE will be
due to the 32-bit SQL engine conforming to the SQL92 standard
as regards treatment of Nulls. The QBE engine would by
default return Zero for Null. This would affect the results
of all queries with joins. If you are using BDE 3 you ought
to move to BDE 3.5 but there's no reason why you should not
move to the latest BDE 5.1.

Optimisation efficiency depends a lot on the target systems.
You will benefit from increasing MinBufSize to 1024 (1
megabyte) and MaxBufSize to 4096 as this increases the cache
available to BDE. Speed is also a function of network
configuration.

Nick Spurrier (MoDESoft, UK)

Here's something I don't quite get. What I read from Help files is
that QBE (Query By Example) was a tool to get data from real SQL
databases, not from dBase or Paradox databases.

  ALL the BDE versions are ALWAYS supposed to be Native tools when
accessing Paradox tables!
With those tests in this thread, both the BDE versions were using
exactly the same SQL statements. And then BDE parsed and translated
them so natively to Borland Database Engine data retrieval commands,
as it ever could.

BDE is a bunch of .DLL files, that do get loaded to workstations
RAM memory. Even with QBE, there is never any part of BDE (or QBE)
that would be residing on the server's RAM memory, and doing some
of the work there.
In this combination, either in stand alone PC or networked environment,
where exactly could that "QBE layer" reside, that could make accessing
Paradox files so much faster with 16-bit BDE code?

If there was that quick way, if the quick cource code available
during 16-bit era, where did the code disappear when compiled under
32-bit compiler?

As you can see, I really don't understand anything in here. Really
nothing about the idea of QBE or it's Native Query Format.

Markku Nevalainen

On Fri, 27 Aug 1999 01:27:53 +0200, Markku Nevalainen <m...@iki.fi>
wrote:

As I understand it the difference between 16/32-bit BDE is the
following:

With 16-bit BDE, there is a QBE engine that translates QBE to native
table calls for local tables.  This is a very efficient way of dealing
with local tables.  For SQL databases, there is an option to translate
the QBE to SQL, but not all QBE queries can be translated to SQL.

SQL queries in 16-bit BDE for local tables are translated to QBE, so
they are pretty efficient too.  The drawback is again that not all SQL
queries can be translated to QBE, so there are some serious
limitations on the SQL syntax.

The 32-bit BDE still has this QBE engine, though it is normally not
used anymore via Delphi.  Local SQL is now a separate engine that
doesn't rely on QBE anymore.  It complies better to the SQL-92 syntax,
but somehow lacks performance because it is not optimized.

The code that translated SQL to QBE is probably not present anymore in
32-bit BDE.

Jan

Yes, this explanation makes some sense, and yet?..  
If 32-bit BDE, and the SQL queries there, does not use native BDE table
calls to access Paradox tables, then what is called that means they
use? What is that layer called, through what the SQL commands have to
go, when accessing Paradox data then?
It can't be ODBC layer or something?

Or maybe there is some "Open and standard SQL Layer" or something
inside BDE. And that's why the BDE developers had to make SQL queries
to go through it, and it would eat some of the performance?

BDE is the most native way, and the only way, to access for instance
Paradox 5.0 or higher tables. So everything inside BDE, also the SQL
part there, should be native, always use those native calls to access
data, or how?  If accessing is not native, then what is it?

Markku Nevalainen

On Fri, 27 Aug 1999 13:20:31 +0200, Markku Nevalainen <m...@iki.fi>
wrote:

Yes, sure, in the end everything is native calls.  And as a matter of
fact, it wouldn't be illogical to suppose that SQL -> QBE -> native
access (16-bit BDE) would be slower than SQL -> native access (32-bit
BDE) because it requires one extra step.  Native access supposingly
being something that is very close to a TTable cursor.

There is one thing I should have stressed a bit more, though.  Local
SQL is not optimized and in fact does some very stupid things from the
point of view of a programmer that would do the same using native BDE
calls.  One example is that it doesn't use existing indexes as optimal
as possible.  Another is that a non-live 'select * from table' seems
to result in a complete batchmove of the table.  There are numerous
examples of this (ever tried a subquery?).

QBE OTOH, takes better advantage of indexes.  Also, the extra step
from SQL to QBE can also be seen as an optimization because it roughly
forces the SQL engine to translate to a more low-level view of the
data.  But one has to bear in mind that this sometimes comes at the
cost of non-conformance to the SQL standard.

Nevertheless, when local SQL would be properly optimized, I see no
reason why it couldn't be at least as fast as 16-bit SQL.

Jan

No, with my test it couldn't. I did run my tests with Paradox tables
with 250.000 records, and there were no indexes at all.

Well, but how do you optimize anything, when the SQL sentence
is this simple:
    SELECT * FROM PARTS
    ORDER BY FIELD_17

With as simple test as this, one really can't put much blame on
developer. There's really nothing a developer can do, if Borland
BDE developers first failed in writing a good 32-bit DB engine.

Yet, if someone knows some general optimizing tricks, and even
better, does have some benchmark test results to show where querying
with 32-bit BDE is faster than 16-bit, I'm really interested to hear.

Markku Nevalainen

On Fri, 27 Aug 1999 18:53:02 +0200, Markku Nevalainen <m...@iki.fi>
wrote:

I'm not sure whom you have in mind when you say 'developer': us mere
mortals or the local SQL engine developers?  If the former, I agree,
we have little options.

In the latter case, however, there are a few considerations, even if
there are no indexes available.

If no cursor stability is required, the user could request a live
query.  As stated, you have no index available on the Paradox table,
but BDE could still create a non-maintained temporary index by just
requesting a read lock on the table.  According to the local SQL
documentation, however, this is never done by local SQL and the slower
path of a temporary result table (non-live query) is always taken in
this case.

Then even for non-live queries, there are several options for creating
the result table, each with different performance:
1) Use DbiSortTable to create an ordered copy of the original
2) Use DbiCopyTable to create a copy, then create an index on this
3) Use DbiBatchMove to create a copy, then create an index on this

All options need a read lock on the table.  My guess is that local SQL
always chooses for 3) which is more than likely the slowest option in
this case.

Whether 16-bit BDE was better optimized than 32-bit BDE or not remains
an open question, of course, since we have no insight in the actual
query process.  But based on the benchmarks you did, no-one can argue
that there is a significant difference between the two.

Yep, me too.

Jan