starting speed

teunis · Post by **teunis** » Fri 12 May 2006 01:04

Dear,
I made a program using D6 MyDac 4.3 MySQL 5

There are many tables and 2 are BIG > 100.000 records
For the tables I used TMyTable for ease of programming
The program works fine in a WAN 100 Mb/s Making the connection and opening tables
takes about 20 seconds.

Now I am trying out the INTERNET 256 Kb/s and the startup takes 10 minutes !!

If I execute the query SELECT * FROM tablename WHERE id = 1234 I expect the query to be executed on the Server and only the result send back to me.
Changing FetchAll to FALSE doesn't change the behaviour.
Any suggestion on how to get only the result ?
Thanks Teunis

Antaeus · Post by **Antaeus** » Fri 12 May 2006 08:37

Most possibly this happens because of your network configuration. Try starting your MySQL server with option skip-name-resolve. Note, if you use this option, you can use only IP-address and localhost as MyConnection.Server. For more information read MySQL Reference Manual.

> If I execute the query ... I expect the query to be executed on the Server
This is true. MyDAC sends the query to the server and gets from it only result set.

teunis · Post by **teunis** » Fri 12 May 2006 13:19

Thanks Anteus,
Can you explain the following:
Program with the above query

Query.fetchAll is FALSE
Startup time 12 sec total committed memory 32 KB
Query.Active:= TRUE I see the result after 10 sec total c. memory 114 KB

Query.fetchAll is TRUE
Startup time 12 sec total committed memory 32 KB
Query.Active:= TRUE I see the result after 1 sec total c. memory 114 KB

What is the use/benefit of FetchAll is FALSE
The same amount of memory is used the same startup time but a much slower response from the SERVER
Teunis

Antaeus · Post by **Antaeus** » Fri 12 May 2006 14:12

If FetchAll is set to False, additional connection to the server is established. As we can see, in your case a lot of time is spent on connection establishment.
FetchAll=False is useful when your query requests large amount of data from the server and initial response time is important. You should check yourself if FetchAll=False has advantages for your application.
For more information read MyDAC help.

teunis · Post by **teunis** » Fri 12 May 2006 22:41

Sorry,
As I wrote in my program the startup time and memory use are not changed but stay the same no matter what the value is of FetchAll.
The only difference I see is that Execution of the Query is much quicker with TRUE.
The table has 26.000 records each 700 byte long
Is the meaning of "to fetch" the same as "get them, in this case get as much information from the table as possible" to speed up further use. (sorry english not my native tonge)
I will put FetchAll on TRUE.
I am pleased with your MyDAC components
Any suggestion to speed up are wellcome.
The startup of my program is now 10 minutes on a 256 Kb/s connection.
It should go down to below 5 minutes for acceptance by the user
Thanks in advance Teunis

GEswin · Post by **GEswin** » Sun 14 May 2006 21:20

Set compress option to true at the TMyConnection is first thing to change to speedup over WAN. Other issues are basically optimize query's, just select the minimal information that you need (If for example you have a table with 20 fields, and only need 3, select only those 3), also drill down the number of rows returned to the value you really need (Make as much as you can with querys instead of local delphi code). Other more advanced tricks are setting up for example a local replication server (Data is replicated to another server that can be on the other side of WAN), for this i recommend taking a look at mysql website.

Regards

teunis · Post by **teunis** » Sun 14 May 2006 22:39

Thanks for the tips.
Especially for programmers using Delphi I can advice don't use TMyTable for big
tables. As soon as you command MyTable.Active:= TRUE you get the whole table sent to you by the server. Unfortunate that means a lot of rewriting
Teunis