ADS performance

[Rafael Clemente]

I am trying ADS (evaluation version for 2 users). I am using 3 PCs connected throug a 100 Mb LAN. One of them holds the data; the other two read from these data.

My test consists of applying a SET FILTER for "TALLER"$CLIENTE on a 7000 register table, and show the results on a TxBrowse(). Here are the results:

Without ADS:
One user. Access time: 3 secs (very good)
Two users - 40 SECS!!!

With ADS:
One user: 14 secs.
Two users: 14 secs.

Certainly, ADS improves the response time whith two simultaneous users but even so, I feel that it is still quite slow, specially considering that the table is not very big (7000 recs, 4.5 Mb). ¿Anybody has experience with ADS and can comment on these results? ¿Is it possible to achieve better times and how? ¿Perhaps the commercial version offers better performance?

Thanks,
Rafael

[reinaldocrespo]

Rafael;

Are you using a Data Dictionary? If you are not, then seriously consider creating a data dictionary. It enhances functionality and many times speed to data access.

To begin debugging try to execute the same from ARC32 using an SQL statement:

SELECT * FROM [dbftablename] WHERE contains( fieldname, "taller" )

ARC32 shows the time of execution on the status line. A similar request on my test Linux server yields 0.375 secs on a dbf populated with 734,000 records. It also depends on how many records are returned.

Advantage supports a better type of filter named AOF(). These are optimized indexes that perform much faster.

Don't give up on learning how to use ADS. Advantage has opened much larger markets for my product. I can now install my system on LANS with more than 250 users. I'm able to have multiple satellite stores with 50 or so workstations and all satellite servers connect to a single central server via an internet connection. The central server contains all the data from all the satellite servers. --A user can not delete (not even on purpose) a dbf table nor can it be taken on an USB flash stick. Data is secure. --I have never done a reindex. On one of my customers a single table is 95 GB. --Triggers, --fast text search, --Referential Integrity, --Replication, --SQL and ISAM access to data, -- .NET integration.

Finally, using ADS I'm now writing PHP code that runs on a browser that accesses dbf/cdx data on a Linux server running ADS and Apache. It flies. You could use PHP as I am, or Java, or Pearl, or ASP .net to write your web pages and still use xharbour for your win32 apps sharing the very same data (dbf/cdx or dbf/ntx or adt/adi).

The biggest problem we face is that Advantage does not know that we exist. They support Delphi, C#, VB, .Net, Visual Studio, even Clipper, but harbour is not in their minds. Notice how there is not even an xharbour forum in their ng. We need collaboration from the harbour/xharbour people, including Antonio, to work with Sybase ADS and get better tools for xharbour to have access to ADS, specially having all the API functions added to the ads.lib. Currently less than one half are included.


Reinaldo.

[Rafael Clemente]

Reinaldo:

Thank you for your comments which really sound encouraging. The truth is that I am just beggining with ADS in an attemp to solve a problem of slow response in an application already installed in an customer's office.

In these tests, I just installed ADS and followed the basic procedures to test my old program (such a modifying the filters syntax). No data dictionary, so no SQL.

However, the response is still slow. Therefore, I am investigating other causes. Maybe there is a fault in the logic of my program. I still don't know and the application is far to big to post it here for discussion.

The only thing that seems clear is that the program performs very well when there is only one user accessing the main table. Two simultaneous users make the system very slow. Three just collapse it making it almost impossible to use. I am always talking about performing simple filters such as "TALLER PEREZ" $ CLIENTE on a 7000 records table...

Again, thank you for your comment. It seems clear that ADS performs very well, so I'll have to look somewhere in my program to find the cause of the slow response.

Thank you very much

Rafael

[nageswaragunupudi]

I am really surpised how could you make an ADS progrm so slow to run. My experience with huge tables of several hundred thousand rows and more than hundred users work quite fast even on slower wide area network connections.

Filters like "TALLER PEREZ" $ CLIENTE are inherently the most inefficient. Even such filters give good performance even on huge tables. By the way itis better to use CONTAINS( CLINEENTE, 'TALLER PEREZ') instead. If we have an FTS index on CLEINTE column the results are instantaneous

[Rafael Clemente]

NageswaraRao,

I am not blaming ADS; on the contrary, I am convinced that it is a great tool. I am just stating the problem I am experiencing:

LAN with three users
Table of 7000 records
ADS with no Data Dictionary. Just the basic configuration.
Browser: TxBrowse()
oBrw:bChanged := {|| ::LoadDetail()} // This Method just loads and shows the fields such as Name, Address, etc... every time the Browse selects another record
Filter : "TALLER PEREZ"$CLIENTE. About 10 records fulfill than condition.
With just one user, it responds very fast. Two or more users degrade the response time up to the point of making it almost unusable.

By the way: I have tested the same program with and without ADS. With ADS it shows a better response time, but still much slower that what Reinaldo and yourself and many other ADS users are getting.

Therefore, I must conclude that something else is wrong either with my program or the LAN configuraction (although, remember that with just one single user it goes lightning fast). Maybe the bChange block?

Thanks for your coments. Best regards

Rafael

[nageswaragunupudi]

1) Are you using Server version or Local ADS server on lan ?
2) To simplify the tests, please temporarliy remove the bchanged block. ( Note: TXBrowse calls bchanged block even when a column is changed.)

[Rafael Clemente]

NageswaraRao,

1) Are you using Server version or Local ADS server on lan ?

Yes. I installed ADS in the PC that holds the data. Every other PC has one copy of my program installed in its own directory, includinf the three ADS DLLs.

2) To simplify the tests, please temporarliy remove the bchanged block. ( Note: TXBrowse calls bchanged block even when a column is changed.)

I removed the bChanged. Still, no noticeable improvement. However, I noticed something:
I placed a seconds counter like this:

Code: Select all

.....
nSecons0 := Seconds()
Set Filter...
? Seconds()-nSeconds
Browse()
....

The filter itself is now very fast. The problem seems to be in loading the Browse which takes forever. Also, there is heavy LAN traffic while loading the browse() even when only one record fill the filter condition. In fact, every record in the main table is related to four other tables and it seems as if all the relations are being processed, regardless the filter is satisfied or not... Obviously there is something wrong with my code. I'll have to look at it in depth.

The bottom line is: The filter in itself works well. Now, let's see what am I doing wrong...

Thank you for your suggestions and help

Rafael

[nageswaragunupudi]

To further simplify, please remove browse also.

run this small simple code

open the table
set filter
go top
skip each row and print a value
close table

measure seconds lapsed at each step

pl let me know if you are on any instant messenger

[Rafael Clemente]

NageswaraRao,
Done the test:

Code: Select all

nSeconds := Seconds()
Do While !Eof()
     dbSkip(1)
Enddo
? Seconds()-nSeconds0

Results: From the main PC (the one with the data): 0.02 secs
From the remote PC: 7.83 seconds

If we were working in the old MS-DOS, I woud say that I had I problem with SET BUFFERS=. But, of course, in Windows XP there is no such command. I wonder why the LAN is runing so slow

Rafael

[Otto]

Could the problem be the antivirus software?
Regards,
Otto

[nageswaragunupudi]

ADS 8.1 (server version) when executed on the server where ads server and data is installed is made to work faster than accessing from network. So the difference always exists.

Now about your benchmark: On local area network I got a speed of 1.4 secs for skipping 7000 records in a 500,000 records table. Ofcourse this is very highspeed lan.

can you send me your simple source code to my personal email?

[Rafael Clemente]

Otto:

Could the problem be the antivirus software?

I doubt it for the problem appears both in my development LAN and in the customer LAN (which, by the way, has a dedicated server)

NageswaraRao:

can you send me your simple source code to my personal email?

It is difficult to send you the whole app. However, I will try to prepare an small sample and send it to you ASAP.

Thanks to you both
Rafael