Extremely slow insert from Delphi to Remote MySQL Database

Question

Having a major hair-pulling issue 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 have used batching and direct insert with ADO (using table access), and with Zeos I have used SQL insertion with a Query, then used Table direct mode and also cached updates Table mode using applyupdates and commit. With MyDAC I used table access mode, then direct SQL insert and then batched SQL insert

All technologies I have tried, I set compression on and off with no discernable difference.

So far I have seen a pretty much the same across the board 7.5 records per second!!!

Now, I would from this point assume that the remote server is just slow, but the MySQL Workbench is amazingly fast, and the Migration toolkit managed the initial migration very quickly (to be honest, I don't recall how quickly - which kind of means that it was quick)

Edit 1

It is quicker for me to write the sql to a file, upload the file to the server via ftp and then import it direct on the remote server - I wonder if they perhaps are throttling incoming MySQL traffic, but that doesn't explain why the MySQL Workbench was so quick!

Edit 2

At the most basic level, the code has been:

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;

I then 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 actually wrote back to the database) the speed was blisteringly fast!!

To be perfectly honest, I think it's the connection - I feel it's the connection... Just waiting for them to get back to me!

Thanks for all your help!

Answer 1

You can try AnyDAC and it Array DML feature. It may speedup a standard SQL INSERT for few times.

Answer 2

Sorry that this reply comes long after you asked the question.

I had a similar problem. BDS2006 to MySQL via ODBC across the network - took 25 minutes to run - around 25 inserts per second. I was using a TDatabase connection and attached the TTable Tquery to it. Prepared the SQL statements.

The major improvement was when I started starting transactions within the loop. A simple example, Memebrships have Member Period. Start a transaction before the insert of the Membership and Members, Commit after. The number of memberships was 01585 and before transactions it took 279.90 seconds to process all the Membership records but after it took 6.71 seconds.

Almost too good to believe and am still working through fixing the code for the other slow bits.

Maybe Mark you have solved your problem but it may help someone else.