Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Paradox vs ALS , very slow

2 posts in Networking Last posting was on 2004-12-20 17:18:35.0Z
user Posted on 2004-12-17 19:45:21.0Z
Date: Fri, 17 Dec 2004 11:45:21 -0800
From: user@domain.invalid
User-Agent: Mozilla Thunderbird 0.7.3 (Windows/20040803)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: Advantage.Networking
Subject: Paradox vs ALS , very slow
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 24.109.93.188
Message-ID: <41c33870@solutions.advantagedatabase.com>
X-Trace: 17 Dec 2004 12:50:08 -0700, 24.109.93.188
Lines: 57
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!24.109.93.188
Xref: solutions.advantagedatabase.com Advantage.Networking:493
Article PK: 1131916

We are in the process of moving our Delphi aplication from Paradox to
Advantage. Most customers will be going to ADS but some of the smaller,(
under 4 workstations) with use ALS. I have noted some apparent
anomolies, for example the LOCATE funstion with TADSTable is very slow,
but SetKey and SetRange work very quickly. I am stuck on one problem
that I cannot seem to solve

The client has 3 workstations, all windows XP and Server 2003 running on
the server. All NICs are 1gbs and running at that speed. All TCP/IP only
as the protocol.

I have a function that sums up a list from 2 tables (a primary and
dependant and both are TADSTables) and creates a temporary table to
display the information. The temporary table is created and stored on
the local workstation. The function does the following.

Opens both tables.
Creates the temporary table.
Sets the index on both tables.
Uses SetRange on the Primary ADSTable. This results in approx 23000 records.
Iterates through the primary table, summing the information into an array.
At each record of the primary table, set range on the secondary table
using the dependant field . There are approx 3 secondary record for each
primary record.(total of 60000)
Then iterate through the secondary field, adding these figures to
another array.
When complete, go to the next primary record and repeat the process
until EOF is reached on the primary table.

Once this is done, iterate through the array and add each record to the
temporary table. The array is used because it is faster to search for
duplicate records to add to rather than having to use a SetKey on the
local table.

No Filters are used.

On the previous Paradox version on the same network (I can run them
togther)it takes 15 seconds to complete this function. The Advantage
version takes 6 minutes. Can anyone explain a possible reason for this
enormous difference in performance? (i.e. some local setting in
ADSLOCAL.CFG) I do note that as I shrink the size of the record set
(this is a date range function) so if I look at the last 2 months for
example the performance is almost identical, but when I am out to a
years worth of financial info, then I note this huge gap. 6 minutes is
obviously unacceptable to a client used to receiving info in seconds, in
fact they were are first halting the application thinking it had hung.
They are one of our beta sites, but we are hoping to roll this out to
all our clients in the new year.

Sorry, maybe this should be in the Delphi forum, but I do not think it
is a specifically Delphi issue .

Cheers
Peter


Lee Stigile Posted on 2004-12-20 17:18:35.0Z
Date: Mon, 20 Dec 2004 10:18:35 -0700
From: Lee Stigile <lees@extendsys.com>
User-Agent: Mozilla Thunderbird 0.7.1 (Windows/20040626)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: Advantage.Networking
Subject: Re: Paradox vs ALS , very slow
References: <41c33870@solutions.advantagedatabase.com>
In-Reply-To: <41c33870@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 192.168.31.45
Message-ID: <41c70a3a@solutions.advantagedatabase.com>
X-Trace: 20 Dec 2004 10:22:02 -0700, 192.168.31.45
Lines: 108
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!192.168.31.45
Xref: solutions.advantagedatabase.com Advantage.Networking:494
Article PK: 1131918


user@domain.invalid wrote:

> We are in the process of moving our Delphi aplication from Paradox to
> Advantage. Most customers will be going to ADS but some of the smaller,(
> under 4 workstations) with use ALS. I have noted some apparent
> anomolies, for example the LOCATE funstion with TADSTable is very slow,
> but SetKey and SetRange work very quickly. I am stuck on one problem
> that I cannot seem to solve
>
> The client has 3 workstations, all windows XP and Server 2003 running on
> the server. All NICs are 1gbs and running at that speed. All TCP/IP only
> as the protocol.
>
> I have a function that sums up a list from 2 tables (a primary and
> dependant and both are TADSTables) and creates a temporary table to
> display the information. The temporary table is created and stored on
> the local workstation. The function does the following.
>
> Opens both tables.
> Creates the temporary table.
> Sets the index on both tables.
> Uses SetRange on the Primary ADSTable. This results in approx 23000
> records.
> Iterates through the primary table, summing the information into an array.
> At each record of the primary table, set range on the secondary table
> using the dependant field . There are approx 3 secondary record for each
> primary record.(total of 60000)
> Then iterate through the secondary field, adding these figures to
> another array.
> When complete, go to the next primary record and repeat the process
> until EOF is reached on the primary table.
>
> Once this is done, iterate through the array and add each record to the
> temporary table. The array is used because it is faster to search for
> duplicate records to add to rather than having to use a SetKey on the
> local table.
>
> No Filters are used.
>
> On the previous Paradox version on the same network (I can run them
> togther)it takes 15 seconds to complete this function. The Advantage
> version takes 6 minutes. Can anyone explain a possible reason for this
> enormous difference in performance? (i.e. some local setting in
> ADSLOCAL.CFG) I do note that as I shrink the size of the record set
> (this is a date range function) so if I look at the last 2 months for
> example the performance is almost identical, but when I am out to a
> years worth of financial info, then I note this huge gap. 6 minutes is
> obviously unacceptable to a client used to receiving info in seconds, in
> fact they were are first halting the application thinking it had hung.
> They are one of our beta sites, but we are hoping to roll this out to
> all our clients in the new year.
>
> Sorry, maybe this should be in the Delphi forum, but I do not think it
> is a specifically Delphi issue .
>
> Cheers
> Peter
>
>
>

Hello,

I'll make the first attempt to help and hopefully others will jump in
with comments.

Although Paradox does some aggressive caching that boosts performance
(and causes data corruption), 15 seconds versus 6 minutes seems extreme.

I suspect that there may be some other factors besides the caching.
Please check out the following:

1. Enable Opportunistic Lock. See ADS KB #030626-1551 and Microsoft
KB#296264. Some machines (ghosting) have oplocks disabled. I've seen
this cause similar time disparities.

2. Also, temporary disable your firewall/Anti-Virus on the local
workstation and server. I especially suspect MacAfee on the local XP
workstation. It's possible that it's scanning every write of the
temporary table.


Beyond that, here's a standard list of issues that can cause Paradox to
perform faster than ADSLOCAL server.
1. Make sure that you use version 7.1 -- which has more aggressive
local server caching.
2. ADS is built to handle multiple users. Also, Paradox won't cache as
aggressively with multiple users. Try testing with more than one user.
3. Check the Table Options on your TAdsTable component. Verify that
AdsFilterOptions is set to IGNORE_WHEN_COUNTING

Also, see "Optimizing Performance" under Advantage TDataSet Descendant |
Developing and Distributing Applications in the Advantage Help.

Finally, from your brief description of your function, it seems like the
entire process could be re-written in an SQL statement (Primary table
LEFT OUTER JOIN dependent table). If you were to scale to REMOTE
server, this would provide a tremendous performance boost. Looping
through records doesn't really exploit the full power of the database.

From a Delphi perspective, I believe that LOCATE doesn't necessarily
use an index, whereas findkey requires an index. If locate is slow, I
suspect that you may need to build an index on the appropriate field.

I hope this helps.

Regards,
Lee Stigile
Advantage Support