BorlandTalk.com

David Harper · Guest

I have POS systems that have been in the field at multiple sites for up to
10 years. These are multi-user systems that run on a Windows network. Some
of the larger clients are reporting a severe performance problem with the
Order file doing certain operations. These are dBase files (dBase for
Windows).

The Order file has 7 indexes: Customer ID, Order ID, Date, etc. One of the
indexes is STATSQDTTM. This indexes the Orders by:

Status Code (C 2)
Sequence Number (C 4)
Date (D)
Time (C 8)

One of these files I have seen has about 35,000 Order records. Most of the
Orders have a Status Code of 'CD' (Completed by Delivery). At any one time
there may be a 100 or so Orders with the Status of 'WD' (Waiting for
Delivery). There are about 6 other Status Codes that are much less
frequently used.

I establish a SetRange using the STATSQDTTM index like this:

CancelRange;
SetRangeStart;
FieldByName('STATUS').AsString := 'WD';
FieldByName('DELSEQ').AsString := '';
FieldByName('DELDATE').AsString := '';
FieldByName('DELTIME').AsString := '';
SetRangeEnd;
FieldByName('STATUS').AsString := 'WD';
FieldByName('DELSEQ').AsString := '~';
FieldByName('DELDATE').AsString := MAX_DATE;
FieldByName('DELTIME').AsString := '~';
ApplyRange;

This works quickly. As I said, this will select about a 100 Order records
'Waiting for Delivery'. These are almost always the most recent records
added to the end of Order file. After the SetRange I establish an OnFilter
(Woll2Woll TTable) to filter out some unwanted records.

However, three operations on this file, with the above SetRange and filter
in effect, take a *very* long time.

1) Doing a 'Last" takes about 3.5 seconds.

2) Doing a 'First" takes about 1.5 seconds.

3) Doing a 'Post' that changes the Status from 'WD' to 'CD' takes about 5
seconds.

What is going on here?

On my standalone, development system using a copy of the above client's
Order.DBF and MDX files I get the following results:

1) Doing a 'Last" takes about 30 milliseconds.

2) Doing a 'First" takes about 15 milliseconds.

3) Doing a 'Post' that changes the Status from 'WD' to 'CD' takes about 80
milliseconds.

What could be the problem(s) on several different clients' systems that
would cause this incredible performance hit? Other operations of the POS
system appear to be normal.

Things I have had one of my clients try:

1) Defrag the server. No improvement.

2) Re-build the indexes on the Order file on the server. Some improvement,
but still way too slow. After a while the performance slows down again. I
am not sure what this means, especially since using the original Order file
with un-rebuilt indexes gives me excellent performance on my machine.

Other info: Everybody is using the latest BDE.

Part of this problem I can attribute to the speed of the client's network,
but it is not *that* slow. Timing some basic dBase file operations on a
client's system show about what I would expect

Is there some issue with an index that contains 30,000+ duplicate keys, like
the Order Status code field? Do the client's workstations need more memory?
A BDE tweak?

Anybody got any ideas?

- David Harper

Rick Carter · Guest

You don't tell us what version of Delphi you're using. Assuming it
includes TClientDataSet (included in Pro and above since Delphi 6),
using a ClientDataSet to do your filtering would likely help with your
performance issues. There's a series of articles on using CDS by Cary
Jensen at bdn.borland.com

Rick Carter
carterrk (AT) despammed (DOT) com
Chair, Delphi/Paradox SIG, Cincinnati PC Users Group

--- posted by geoForum on http://delphi.newswhat.com

David Harper · Guest

"Rick Carter" <carterrk (AT) despammed (DOT) com> wrote in message
news:4592b392$1 (AT) newsgroups (DOT) borland.com...

Anthony J. Maske · Guest

David Harper wrote:

Anthony J. Maske · Guest

Rick Carter wrote:

Rick Carter · Guest

David Harper · Guest

Anthony and Rick, thanks for your comments.

To clarify some points:

In general, all database (dBase) operations of the program on client's
machines have acceptable performance. On average I would say they are about
1/5 as fast as on my standalone machine. That is perfectly okay and about
what I would expect for a file-based database in a network environment.

It is just this one set of operations on one particular dBase file that is
*extremely* slow. It doesn't seem to be related to the number of users on
the network. The file in question has about 35,000 records and is around
50 meg. There is a SetRange in effect on this file that limits what is
"seen" to about the last 100 records or so.

1) Doing a 'Last" takes about 3.5 seconds.
2) Doing a 'First" takes about 1.5 seconds.
3) Doing a 'Post' takes about 5 seconds.

I just don't understand this! These numbers are about 100 times slower than
on my standalone machine.

There has got to be something different about a client's workstation. I
will say this again: *ALL* other dBase operations from this same client's
workstation are fine, on this table and all other tables... that is, about 5
times slower than on my computer (using a copy of this client's data).

----
One thing to remember is that dBASE files are processed on the client side,
the server side just gives back chunks of files that the client decides
whether or not to use it. So when you have large
tables/indexes the performance on the client will start to slow down.
----

I would think that a SetRange would reduce the scope of what is set to
client significantly. I use them all the time on other files and
performance is acceptable.

The file in question has 7 indexes on it. I wonder if the 'SetRange',
'First' and 'Last' operations are pulling the entire index over the network.
The MDX file is 16 meg. What could possibly make a 'Last" take 3.5
seconds? What could make a 'Post" take 5 seconds? Sheesh!

----
It's my understanding that with dBASE tables, the entire table will be
brought across the network, whether it's all needed or not.
----

This is not true for the type of operations I am doing: 'Find', First',
Next', etc. As I said: all other operations on this and other tables have
no performance problems.

I have already had the client do a defrag on the server. It made no
difference.

The only thing that has made any improvement was rebuilding all the indexes
on this table. Things improved a little, but not enough. Over a short time
performance returned to being abysmal.

I would love to run something like FileMon on a client's machine to see what
is happening during one of those 5 second posts!

My ultimate fallback solution is to rewrite this part of the program to pull
all the records I need from the server in one initial slurp and work on them
in a MemTable. Then put them back when I am done. Not very multi-user
friendly...

However, I really want to understand what is going on with this performance
problem. To me, it just smells like a workstation configuration issue...
not enough memory, not enough free disk space, etc. I will look into that.

Any other things to look at, or profile, or probe, or scratch-n-sniff?

- David Harper

Jerry Von KOrff · Guest

I have noticed also that with setrange in effect, navigation upward and
downward seems to be slower than one would expect, on some tables. But
it happens so seldom on my application, I hadn't given it much thought;
behavior as if a filter were in effect.

How about trying to do a last-substitute using findnearest to the end of
the range and see if the timing is still the same! I'm in BDS 2006

I'm in the midst of a gradual reprogramming to MySQL . The MyDac
components seem to provide excellent results, although have a long long
way to go to make the conversion.

David Harper wrote:

David Harper · Guest

This is a follow-up to a message I posted in late December 2006. I thought
I would update everybody on what I found out.

To restate the gist of the problem:

I have POS systems that have been in the field at multiple sites for up to
10 years. These are multi-user systems that run on a Windows network. Some
of the larger clients are reporting a severe performance problem with the
Order file doing certain operations. These are dBase files (dBase for
Windows).

Most database (dBase) operations of the program on client's machines have
acceptable performance. It is just this one set of operations on one
particular dBase file that is *extremely* slow.

The file in question has about 35,000 records and is around 50 meg. There
is a SetRange in effect on this file that limits what is "seen" to about the
last 100 records or so. Then there is a Filter that tosses out a few
records.

1) Doing a 'Last" takes about 3.5 seconds.
2) Doing a 'First" takes about 1.5 seconds.
3) Doing a 'Post' takes about 5 seconds.

After a lot of further testing and having clients try different things, I
never found out what was causing this slow performance.

But, here is how I got around the problem:

1) Instead of doing 'First' or 'Last', I do this:

while not BOF do
Prior;

while not EOF do
Next;

This made a *huge* difference! Those 3.5 and 1.5 second times listed above
dropped to just a few milliseconds.

2) Before I do a 'Post', I turn off the filter. This made an *enormous*
difference. That 5 second time listed above dropped to just a few
milliseconds.

What used to take a handful of seconds now just takes a handful of
milliseconds... a 500 to 1000 fold improvement! ...but I don't understand
why. The compound index in effect while I am doing all this has a lot
(thousands) of duplicate primary keys. The SetRange uses the secondary key
to restrict the number of records to about 100. I suspect this may have
caused the problem.

- David Harper

John Herbster · Guest

David, Thanks for the followup. JohnH

Wayne Niddery [TeamB] · Guest

David Harper wrote:

BobW · Guest

I use Delphi 7, woll2woll components and dBase files frequently.
Not too long ago, I successively set a filter in a loop, as I
incremented a date ... I don't remember the particulars of it,
but that is close. It was "turtle slow"; which made no sense to
me. After looking closer at it, the setting of the filter the first
time was milliseconds (GOOD); but on subsequent setting of the
filter it was seconds (BAD). This made no sense to me - I figured
out another way around the issue, but it was one of those things
that never made sense. Just my 0.02. Thanks, Bob
__________________________________________________

"David Harper" <dharper (AT) houston (DOT) rr.com> wrote in message
news:45c9f013 (AT) newsgroups (DOT) borland.com...

David Harper · Guest

"Wayne Niddery [TeamB]" <wniddery (AT) chaffaci (DOT) on.ca> wrote in message
news:45ca478a$1 (AT) newsgroups (DOT) borland.com...