Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 17 total
Thread Converting from Paradox: Performance problems
Wed, Jun 28 2006 7:39 AMPermanent Link

Georg Ledermann
Hi,

currently I'm trying to convert an old Delphi-5-Paradox-Application (using a lot of TTable
and some LocalSQL-Queries) to DBISAM (Trial of 4.24 Build 1). I have finished the most
parts (converting data, replacing TTable/TQuery to TDBISAMTable/TDBISAMQuery, adjusting
the SQL statements), so the application works fine as desktop application with data on the
local hard disk. So far, so good.

Now I'm looking for the best way to run it in the network, but got stuck with performance
problems. I have tried the following:

1) Using C/S-Mode (running dbsrvr.exe on a small W2K3 box as service)
- Session.RemoteType = stRemote
- Session.RemoteCompression = 6
- Session.RemoteAddress

Works very fast with SQL statements (great!), but is too slow with TDBISAMTable (slower
than with BDE).
Connecting using WLAN and 100MBit-Ethernet is nearly the same. The compression speeds up
slower network like WLAN a lot.

2) Using Local Mode
- Session.RemoteType = stLocal
- DB.Directory = '\\ServerName\\C$\DBISAM\MyData' (pointing to the same target as above)

Using 100MBit-Ethernet: Works fast with TDBISAMTable (like BDE), but is slower with SQL
queries than in C/S mode
Using WLAN: Works too slow with TDBISAMTable (because of missing compression?), speed of
SQL queries are the same like with Ethernet


So, what kind of optimization can be done? I prefer using the C/S mode, if it is possible.
I know that the application has a very bad design (all tables are opened at login, only
one large data module with a lot of master/detail datasets, nearly no encapsulation of
data access...). But perhaps there are some kind of DBISAM tricks for speeding up such a
beast. Hope someone can help me...

Kind regards,
Georg
Wed, Jun 28 2006 9:10 AMPermanent Link

Dave M
Georg Ledermann <ledermann@completho.de> wrote:
-----------------------------------------------------------------

Just a couple suggestions.

I don't think compression helps with (wired) local area networks. It may even slow things
down. This is from my foggy memory from some time ago.

If you are doing a lot of disk writes at one time, they must be in a transaction, or
performance will be bad. WinNt NTFS is slooow for disk writes. As you may be aware, the
BDE, depending on how it is set up may do delayed writes.

Dave M
Wed, Jun 28 2006 11:40 AMPermanent Link

Georg Ledermann
Dave, thank you for the hints. In my configuration the compression does really speed up a
lot, without compression reading from the database will slow down by factor 3 or
something. This is the main reason I want to use C/S mode, not Fileserver mode.

After further work I can give some more precise facts:

- Speed problems occur while reading and displaying the table in a grid. I have not made
any write test until now, so transaction handling does not help
- The speed problem can be seen in a grid: Scrolling from record to record takes a few
seconds each row. The same is while doing a "Locate"
- The computer is a Pentium D with 3GHz and 3GB RAM, with Windows XP SP2
- I'm using an ExpressQuantumGrid, but the same problem occurs with TDBGrid
- The grid is bound to a TDBISAMTable, the table contains only 200 records in my example
- There is no filter and no OnFilterRecord active
- The DBISAMTable has 50 fields, some of them are lookup fields to other small table,
there is no CalcField

I think the main problem is this:
- The DBISAMTable is referenced as master by a lot of detail tables and the details are
master of other details. All detail tables are open.

For a test I have closed all detail tables before displaying the grid. Now the speed is
much better. But I cannot close the other tables without rewriting a lot of the application.

Are there any other things to try?
Wed, Jun 28 2006 12:18 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Georg,

<< So, what kind of optimization can be done? I prefer using the C/S mode,
if it is possible. I know that the application has a very bad design (all
tables are opened at login, only one large data module with a lot of
master/detail datasets, nearly no encapsulation of data access...). But
perhaps there are some kind of DBISAM tricks for speeding up such a beast.
Hope someone can help me... >>

You've run into the main issue with file-sharing vs. client/server access.
They are both very good at one or the other aspect (navigational or SQL),
but not both.

What you should look at is the BlockReadSize property.  Set it to a larger
value than 1 when doing any type of while (not EOF)..Next processing.
Multi-row TDBGrid population is already optimized automatically by DBISAM to
grab records in chunks of the number of TDBGrid rows.  Also, try to keep the
number of lookup fields to a minimum in your application.  They cause a lot
of round-trip requests to the database server for every record navigation,
refresh, etc.  For example, with just one Next, having 3 lookup fields can
cause 3 more request/response cycles to the database server, with each
incurring the same network latency as requesting a 1 meg block of records in
one request/response cycle.

--
Tim Young
Elevate Software
www.elevatesoft.com

Wed, Jun 28 2006 12:26 PMPermanent Link

"Ralf Mimoun"
Georg Ledermann wrote:

[From Paradox to DBISAM]
....
> Works very fast with SQL statements (great!), but is too slow with
> TDBISAMTable (slower than with BDE).

What exactly is slower? Opening the table? A filter, and if, what's the
filter? SetRange? Locate?

....
> 2) Using Local Mode
> - Session.RemoteType = stLocal
> - DB.Directory = '\\ServerName\\C$\DBISAM\MyData' (pointing to the
> same target as above)
>
> Using 100MBit-Ethernet: Works fast with TDBISAMTable (like BDE), but
> is slower with SQL queries than in C/S mode

Of course. In C/S mode, the server can access the tables including the index
files on harddisk. Via LAN, the computer must request every byte from a file
server and get it through the line, with latency etc.

> Using WLAN: Works too slow with TDBISAMTable (because of missing
> compression?), speed of SQL queries are the same like with Ethernet

Again, what is slow?

> So, what kind of optimization can be done? I prefer using the C/S
> mode, if it is possible. I know that the application has a very bad
> design (all tables are opened at login, only one large data module
> with a lot of master/detail datasets, nearly no encapsulation of data
> access...). But perhaps there are some kind of DBISAM tricks for
> speeding up such a beast. Hope someone can help me...

DBISAM simply needs the right indexes. It does not cache that much data like
DBE/Paradox because it don't need to with a good db design. But you can
expand the cache buffers by yourself, see "Buffering and Caching" and
"Customizing the Engine" in the online help of DBISAM. And something almost
everyone forgot at least once: if you have something like "WHERE
CharField1='ABC'", you will need a case sensitive index for CharField1. For
"WHERE UPPER(CharField1)=UPPER('ABC')", you will need a case insensitive
index.

Ralf
Wed, Jun 28 2006 1:33 PMPermanent Link

Chris Erdal
Georg Ledermann <ledermann@completho.de> wrote in
news:81CBBBE7-0747-4DE3-95B2-E91B59BE5064@news.elevatesoft.com:

> I think the main problem is this:
> - The DBISAMTable is referenced as master by a lot of detail tables
> and the details are master of other details. All detail tables are
> open.
>
> For a test I have closed all detail tables before displaying the grid.
> Now the speed is much better. But I cannot close the other tables
> without rewriting a lot of the application.
>
> Are there any other things to try?
>
>

I seem to remember this problem in one application using SQLServer 6.5, and
I improved it by closing any child tables and resetting a timer in
OnBeforeScroll, and then reopening them with the timer set to a bit longer
than the master table's scroll time.

--
Chris
Wed, Jun 28 2006 6:56 PMPermanent Link

Georg Ledermann
Thanks to all for helping. It seems that because of the horrible TTable architecture
(development has started in 1995) my app cannot be easily converted to C/S mode. But in
fileservermode the application is now working with acceptable performance. The greatest
performance boost was the elimination of some lookup fields and setting LookupCache for
some other lookup fields to TRUE (hm, it seems that I should try this in the BDE version,
too...)

The migration is not finished yet, there is a lot of work to be done - but DBISAM seems to
be the best BDE replacement for me Smile
Thu, Jun 29 2006 7:21 AMPermanent Link

"Ralf Mimoun"
Georg Ledermann wrote:
> Thanks to all for helping. It seems that because of the horrible
> TTable architecture (development has started in 1995) my app cannot
> be easily converted to C/S mode.

Huh? I have a project that use TDBISAMTables everywhere. Mostly because tt's
faster for some scnearios, eg. searching on the fly via Locate while the
user types in the search keyword. It runs fast as hell in local and C/S
mode. No problems at all. But you should avoid while not eof/next in C/S
mode. In local mode too, btw.

Ralf
Thu, Jun 29 2006 8:32 AMPermanent Link

> What you should look at is the BlockReadSize property.  Set it to a
> larger value than 1 when doing any type of while (not EOF)..Next
> processing.

This is most interesting, as I've not heard of this before. The
documentation on it is sparse (it being part of the base VCL classes and
not yours). Could you tell me (us?) what it actually does please? Is it
essentially a buffering system to speed up looping through the table?

/Matthew Jones/
Thu, Jun 29 2006 10:13 AMPermanent Link

Georg Ledermann
"Ralf Mimoun" <nospam@rad-on.de> wrote:

> Huh? I have a project that use TDBISAMTables everywhere. Mostly because tt's
> faster for some scnearios, eg. searching on the fly via Locate while the
> user types in the search keyword. It runs fast as hell in local and C/S
> mode. No problems at all. But you should avoid while not eof/next in C/S
> mode. In local mode too, btw.

Perhaps the performance problems of my app in C/S mode are not caused simply by using
TTables, but the "special" architecture of the app: All TTables are opened at login, all
TTables are in a big datamodule and dependent on each other (master/detail and lookup
fields). It's really a very bad design and the best solution would be: Write it new from
scratch - but this is another project I'm working on. The goal for the old app is: Leave
the BDE behind and convert the app to DBISAM - but with *minimal* changes of the design.
For example, if I would close/re-open some tables during runtime (a great performance
boost, I think), the whole design has to be changed and this would be much work.

I agree with you that DBISAM makes it possible to develop fast C/S application with
TDBISAMTable, but converting an old-school-app is another story...
Page 1 of 2Next Page »
Jump to Page:  1 2
Image