Extremely slow insertion from Delphi to a remote MySQL database

Question

Having a serious hair problem with extremely slow inserts from Delphi 2010 to a remote MySQL 5.09 server.

So far I have tried:

• ADO using MySQL ODBC driver
• Zeoslib v7 Alpha
• Mydac

I used batch and direct insertion with ADO (using table access), and with Zeos I used SQL insertion with Query, then I used Table direct mode, and also cached updates in table mode using applyupdates and commit. With MyDAC, I used table access mode, then inserted the SQL insert and then inserted the SQL insert

All the technologies I tried turned on and off compression without any distinguishable differences.

Until now, I have seen almost the same thing on a board of 7.5 entries per second !!!

Now, from this point, I would suggest that the remote server is slow, but MySQL Workbench is amazingly fast, and the migration toolkit handled the initial migration very quickly (to be honest, I don’t remember how fast - which means it was fast)

Change 1

It’s faster for me to write sql to a file, upload the file to the server via ftp, and then import it directly to the remote server - I wonder if they can throttle incoming MySQL traffic, but this does not explain why MySQL Workbench was so fast!

Edit 2

At the most basic level, the code was:

while not qMSSQL.EOF do
begin
  qMySQL.SQL.Clear;
  qMySQL.SQL.Add('INSERT INTO tablename (fieldname1) VALUES (:fieldname1)');
  qMySQL.ParamByName('fieldname1').asString:=qMSSQL.FieldByName('fieldname1').asString;
  qMySQL.ExecSQL;
  qMSSQL.Next;
end;

Then i tried

qMySQL.CachedUpdates:=true;
i:=0;
while not qMSSQL.EOF do
begin
  qMySQL.SQL.Clear;
  qMySQL.SQL.Add('INSERT INTO tablename (fieldname1) VALUES (:fieldname1)');
  qMySQL.ParamByName('fieldname1').asString:=qMSSQL.FieldByName('fieldname1').asString;
  qMySQL.ExecSQL;
  inc(i);
  if i>100 then
  begin
    qMySQL.ApplyUpdates;
    i:=0;
  end;
  qMSSQL.Next;
end;
qMySQL.ApplyUpdates;

Now, in this code with CachedUpdates:=False(which, obviously, never wrote back to the database), the speed was growing fast!

, , - ... , !

!

Answer 1

? (, INSERT INTO table (field) VALUES (: field)), , , - ( - autocommit)

, , , . , .

, , - , .

Answer 2

AnyDAC Array DML. SQL INSERT .

Answer 3

, , .

. BDS2006 MySQL ODBC - 25 - 25 . TDatabase TTable Tquery. SQL.

, . : Memebrships Member. , . 01585, 279,90 , 6,71 .

, , .

, , , - .

Answer 4

ZeosLib, ADO ODBC, , , :

• Mydac , ODBC > ADO > OLEDB > MySqlLib Mysql.

• .

• , 1000 , 100 .

3 ZeosLib ADO.

Answer 5

. -, Delphi Insert , . , . MySql, , SQL . Delphi Workbench , .

If not, then the slowdown occurs in your application. Try connecting it to a Sampling Profiler or other profiling tool that Delphi understands, and it will show you where you spend a lot of time. Once you know this, you can work on a problem attack or perhaps come back here to ask a more specific question. But until you find out where this problem comes from, any answers you get here will be educated guesses at best.