Devart Forums

Hello,

I use Delphi 2006 with MyDac 4.40.0.18.

I have a program created that works normally in LAN but the customer wants me to connect his different shops via Internet using only one database.

I set Compress=True and Pooling = True but it is still very slow.

Especially the "products" page, when every important data of the product is shown in one grid (15 columns) with their bar codes (1:n connection).
What is more the customer should search in it, when typing e.g. 'Old' in the search box, the program should filter the contents to Old Spice, Old Tobacco and so in.
When I type something in I wait minutes for the result.
(have a look at http://invitel.hu/csillagzs/slow.gif )

Is there any way to speed up the whole process (to optimize it for Internet)?

Thank you in advance

Make sure that the problem is with the network. Try to perform SELECT NULL from a table, then try to select a great amount of data (about 1 MB). If the problem is with the network connection speed, we can suggest you the following alternatives:
- apply all filter conditions before sending a SQL command to the server to decrease amount of transferred data;
- run MySQL Server on each client computer and set the replication with a main server;
- download all data from the server when starting client application. This may take a great time interval. From time to time perform synchronization while the program is working (pay attention on the RefreshQuick method).

Hello,

Thank you for the answer.

By "apply all filter conditions before sending a SQL command to the server" you mean :
1.Close the query
2.Apply the filters
3. Open the query ?

The main problem is the Grid display of the records.

It is very interesting that I rewrote a program that previously used Paradox database to MySql (with MyDac).
When scrolling the data the same program that used Paradox was much faster then MyDac with MySql (though the amount of data was small, about 500 records).
Maybe I set something wrong, something trivial. For instance the FetchAll and FetchRow parameters are not very clear to me - I tried to modify them, but I couldn't see any difference in the result.

Thank you in advance

Zsolt Csillag wrote:Hello,

Thank you for the answer.

By "apply all filter conditions before sending a SQL command to the server" you mean :
1.Close the query
2.Apply the filters
3. Open the query ?

The main problem is the Grid display of the records.

It is very interesting that I rewrote a program that previously used Paradox database to MySql (with MyDac).
When scrolling the data the same program that used Paradox was much faster then MyDac with MySql (though the amount of data was small, about 500 records).
Maybe I set something wrong, something trivial. For instance the FetchAll and FetchRow parameters are not very clear to me - I tried to modify them, but I couldn't see any difference in the result.

Thank you in advance

Try to use paramters in your sql.

ex: select * from custumer
the select return all rows from table.

select * from custumer where ID = 125.

using where your limit a number of records.

uses parametros in SQL script's and call only the registers necessary.

Zsolt Csillag wrote: By "apply all filter conditions before sending a SQL command to the server" you mean :
1.Close the query
2.Apply the filters
3. Open the query ?

I mean that you should not open queries that return all the records from a table. Just add some conditions to the WHERE clause to decrease number of returned records. This is shown in eduardosic's example above.

Zsolt Csillag wrote: For instance the FetchAll and FetchRow parameters are not very clear to me - I tried to modify them, but I couldn't see any difference in the result.

FetchAll mode allows you to enable fetch on demand. The FetchRows value determines how many records are retrieved by MyDAC from the network at the same time. Even if you limit the FetchRow value, the server will still proccess a request fror all the records in a table if you do not apply a WHERE condition to your initial query. For example, suppose you have a grid attached to a TMyQuery object. If the grid displays 10 records and FetchRows=25, at first 25 records will be fetched. If you try to access the 26-th record, another 25 record will be fetched from the network.

Thank you for the answers. I learned a lot.

However after several days I found the real problem of the slowness.

The TQuery contains 2 calculated fields:
- one calculates the gross value from the net value
- the other, more complicated calculates how many the customer sold from every product (here means every record) - this means another sql for each row

I thought that the calculated fields (at least for my first one) would run on the local Pc, but it seems they don't.

I tried to build the 2 calculated fields in the main Sql, it was slightly faster but still very slow.

If I omit these calculated fields completely, the search is quite fast.

Have you got some advice wiht calculated fields? I cannot omit them since the customer needs them.
(I can put the Gross value directly in the database, but I can't do with the other since it constantly changes).

Thank you

MyDAC should not perform a request to server on calculating field values. Try to check what commands are sent to server when working. You can do this by setting the Debug option of a dataset object (TMyTable, TMyQuery, etc.) to True and running the the DBMonitor application. Another way to check what commands are sent is MySQL Server logs. For more information about logs read the MySQL Reference Manual.