Network speed of application too slow

[EricDraven]

Hello all, problem time again!

I have written my application which is accessing several large tables (10,000 + records) and a host of smaller tables (all of them are .dbf files). The application is installed on several machines in an office and the datapath is changed on all machines bar one so that they all use the same data. The application works fine on a stand alone machine but when being run over a network is begins to slow down once the main table holds a larger number of records. I always access the tables either by SQL in a TQuery, or using the FindKey method. Table access is being handled by the BDE.

I have streamlined the code as much as possible (I think anyway, you may be able to add some pointers for me) and as stated the application doesnt even flinch when accessing the tables locally which has led me on a fruitless search for information on network access! Am I missing something blatently obvious yet again???

Help me Tek-Tips, your my only hope!


When your feeling down and your resistance is low, light another cigarette and let yourself go

 

[TonHu]

There is only one good solution, redesign into a client-server app.
Now, using TQuery, you'r processing all data on the client, meaning all records are fetched over the network, evaluated, and either included, or discarded for inclusion into the result-set. This means every workstation pulls all data over the network(, for every query). Changing to Advantage Database Server should enlighten the networkload as all database(d) actions would be handled by the server, the application logic is still handled by the workstation.
Switching to real client-server (similar to Windows Terminal Server) would place all processing on the server.
In your case the ADS solution will be the most likely, cost-effective solution, as the app is already deployed, so technical impact would be 'minimal'.

HTH
TonHu

 

[EricDraven]

I had a feeling that Client-Server would be the suggested solution. Are there any good FAQ's or white papers on this topic? I have never had to approach this before and everything I have read on the internet about it over the past couple of days just seems to contradict the previous article I had read!

Also, what is Advantage Database Server? Where can I read about it? How much does it cost? Is it reliable? Where can I download it?




When your feeling down and your resistance is low, light another cigarette and let yourself go

 

[TonHu]

For ADS have a look at

http://www.advantagedatabase.com/ADS/default.htm

It's quite stable (because of maturity) and very well tested.

On client/server there are multi-part books written, so...

HTH
TonHu

 

[svanels]

You also can use Interbase from Borland. In a Client-Server application a TTable component is the least effective way to process data. Use TQueries which let all the data manipulation be done on the server. Whith TTables, all the data is send over the network to the client, and back.

Steven van Els
SAvanEls@cq-link.sr

 

[EricDraven]

Svanels
I need to get this done quite quickly so would it be any quicker if I setup Interbase and change to using the Queries from the interbase tab instead of TTables, but instead of using complex SQL for the time being, I just use something like :

Select * from Table1

and then query the results using my existing code, i.e.

while (Query1.FieldByName('MYFIELD').ASInteger = MyVariable) and (not Query1.EOF) do
begin
  
  //My code

  Query1.Next;
end;

From what I have been able to read so far, I would imagine that this will carry on taking all data from the server over to the workstation to perform the necessary calculations but it will allow me to change over the procedures (there are a lot of them) gradually as I already have people using the system!

Would doing this work in the interim or is it a bad idea?


When your feeling down and your resistance is low, light another cigarette and let yourself go

 

[svanels]

About the complex sql, with the SQL-Builder you can build your queries graphically and it will store the text in the SQL property of the query.

You can do Select * from, but if you let Interbase do some of the processing, you will gain in speed and performance.

Here is a link with a lot of Interbase Information

http://www.mers.com

Steven van Els
SAvanEls@cq-link.sr

 

[Oppenhiemer]

Think you will find that the changing of the code should not be that bad and not too different from what you are using now. Here is an example :

Place a TIBQuery (and associated components) on a form. Set the SQL for the query as something like ..

SELECT * from MyTable WHERE MyField =:Something;

In the code..

IBQuery1.close;
IBQuery1.ParamByName('Something').AsInteger := MyVariable;
IBQuery1.prepare;
IBQuery1.open;

while not IBQuery1.eof do
begin
// Do Something..
end;

There will be some overhead with regards managing some other related compnents like TIBDatdbase and Transactions, but you would (should) see benefits quicker.

Opp.

 

[dilettante]

BTW:

Using Terminal Services has nothing to do with application architecture. This is a remote access technology.

At best it changes your application from being desktop-based to host-based (like the old terminal to mainframe model, only less efficient).