 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Glenn Alcott
Guest
|
 Posted: Wed Feb 14, 2007 10:16 pm Post subject: Performance problem with Advantage database |
 |
|
This is a question regarding Advantage database, which may not belong in
this forum. But I have posted it on the Advantage forum and received no
responses so I thought it was worth a try.
I am the developer of a vertical market application using Delphi 5.0 and
Advantage Local Server 8.0. We have recently seen a drastic slowdown in
performance in a particular function of the program when more than one user
is accessing the major table (named Receipts) used by that function at the
same time. The function simply allows the user to select from a list and
based on that selection, it appends a record to that table and populates
some of the fields. When only one user is in the program it takes about one
second, regardless of the size of the Receipts table. In the most severe
case, when the table has 125,000 records, it is taking 95 seconds from the
remote workstation. With 5,000 records it takes about 4 seconds and with 300
records about 1.5 seconds. By inserting some statements in the code to
record the timing of each part of the function, I found the following
breakdown when using the 125,000 record file (which is about 27MB in size):
1) Append a record (one line of code with an Append command): 20 seconds
2) Set the table to edit mode and populate 3 fields in this record (without
posting the record): 30 seconds
3) Execute a function that does some simple calculations: 10 seconds
4) Populate some more fields and post the record: 35 seconds
I have never seen a performance degradation like this with any database and
would really like to know what Advantage is doing to cause it. Just to
clarify, this does not happen if the first user does not have the Receipts
table open at the same time; in that case the function only takes a couple
of seconds. But it is clearly directly related to the size of the table.
Glenn |
|
| Back to top |
|
 |
Bill Todd
Guest
|
| Posted: Wed Feb 14, 2007 11:01 pm Post subject: Re: Performance problem with Advantage database |
|
|
The short answer is, do not use ALS for multi-user applications. If you
want good performance with multiple users use Advantage Database
Server. File server databases are always a poor choice for multi-user
applications.
--
Bill Todd (TeamB) |
|
| Back to top |
|
|
Wayne Niddery [TeamB]
Guest
|
| Posted: Thu Feb 15, 2007 6:53 am Post subject: Re: Performance problem with Advantage database |
|
|
Glenn Alcott wrote:
| Quote: |
I am the developer of a vertical market application using Delphi 5.0
and Advantage Local Server 8.0. We have recently seen a drastic
slowdown in performance in a particular function of the program when
more than one user is accessing the major table (named Receipts) used
by that function at the same time.
|
[snip]
Presumably something has changed recently. Has the table definition changed
(new or modifed fields)? Any new or changed indexes? Have you tried
rebuilding the indexes?
Generally ALS does quite well even for a few users, but with these kind of
volumes, as Bill says, you really should look into using Advantage server
edition instead. Each client is competing to download all those records from
the database - which also means there might be a network bottleneck - has
anything changed there? Do some network monitoring.
Do the users really need to see all 125000 receipts on their screen to
scroll through? Look at forcing a range or maximum number of records or
search criteria so they can only ever see a small subset. If you go to the
server edition, change this part of the app to use query components instead
of tables. Advantage's SQL engine is pretty good.
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"In a tornado, even turkeys can fly." - unknown |
|
| Back to top |
|
|
Sam Larson
Guest
|
| Posted: Fri Feb 16, 2007 10:45 pm Post subject: Re: Performance problem with Advantage database |
|
|
Bill Todd wrote:
| Quote: | The short answer is, do not use ALS for multi-user applications. If you
want good performance with multiple users use Advantage Database
Server. File server databases are always a poor choice for multi-user
applications.
|
Bill,
But that means he will have to increase the price of his vertical
market app by at least $500 to pay for each ADS (5 user?) server. I'm
not sure his clients will be agreeable. :)
If he wants to keep prices down then might I suggest looking at
Firebird, DbISAM C/S, or NexusDb which do not have royalties.
Sam |
|
| Back to top |
|
|
Bill Todd
Guest
|
| Posted: Fri Feb 16, 2007 11:02 pm Post subject: Re: Performance problem with Advantage database |
|
|
Sam Larson wrote:
| Quote: | Bill Todd wrote:
The short answer is, do not use ALS for multi-user applications. If
you want good performance with multiple users use Advantage Database
Server. File server databases are always a poor choice for
multi-user applications.
Bill,
But that means he will have to increase the price of his vertical
market app by at least $500 to pay for each ADS (5 user?) server. I'm
not sure his clients will be agreeable. :)
If he wants to keep prices down then might I suggest looking at
Firebird, DbISAM C/S, or NexusDb which do not have royalties.
Sam
|
If he is willing to change databases and price is important he should
certainly consider PostgreSQL as well.
--
Bill Todd (TeamB) |
|
| Back to top |
|
|
Rick Carter
Guest
|
| Posted: Thu Feb 22, 2007 1:31 am Post subject: Re: Performance problem with Advantage database |
|
|
Wayne Nidderly wrote:
| Quote: | Do the users really need to see all 125000 receipts on their screen to
scroll through? Look at forcing a range or maximum number of records or
search criteria so they can only ever see a small subset. If you go to
the
server edition, change this part of the app to use query components
instead
of tables. Advantage's SQL engine is pretty good.
|
Agreed that there's probably no need to have that table with 125000
records open, when a query to poplulate the visible rows of the grid
should be sufficient. But shouldn't the use of queries be just as
practical for ALS, without switching to ADS?
He should also look at how using ClientDataSets to cache and manipulate
the data can help.
Rick Carter
carterrk (AT) despammed (DOT) com
Chair, Delphi/Paradox SIG, Cincinnati PC Users Group
--- posted by geoForum on http://delphi.newswhat.com |
|
| Back to top |
|
|
Wayne Niddery [TeamB]
Guest
|
| Posted: Fri Feb 23, 2007 1:05 am Post subject: Re: Performance problem with Advantage database |
|
|
Rick Carter wrote:
| Quote: | Agreed that there's probably no need to have that table with 125000
records open, when a query to poplulate the visible rows of the grid
should be sufficient. But shouldn't the use of queries be just as
practical for ALS, without switching to ADS?
|
No because ALS, like Paradox or Dbase, runs on each machine. So to perform a
search on a table to find the records wanted, the *entire* table must be
downloaded to the client - every client. Even in the case of only searching
on indexed fields, the selected index must be downloaded in full to be
searched, and then the client must access the table across the network to
pick off each of the records to be returned.
There may be some optimizations that can be done, but essentially that is
how it has to work.
With the server edition, like any server-based database, the server does the
work on that side and the only thing going across the network is the query
text from the client, and the small set of desired records going back from
the server. The client never touches the index or table files at all.
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"It is error alone which needs the support of government. Truth can
stand by itself." - Thomas Jefferson |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
