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

I'm not sure about ZeosLib, but using ADO with ODBC driver, you will not get the fastest way to insert the records, here few step that may make your insertion faster:

1. Use Mydac for direct access, they work without the slow ODBC > ADO > OLEDB > MySqlLib to connect to Mysql.

2. Open the connection at first before the insertion.

3. if you have large insertion such as 1000 or more, try use transaction and commit after 100 record or more depend on number of records.

Point 3 may makes your insertion faster even with ZeosLib or ADO.

Answer 2

Are you using query parameters? The fastest way to insert should be using plain queries and parameters (i.e. INSERT INTO table (field) VALUES (:field) ), preparing the query and then assigning parameters and executing as many times as required within a single transaction - committing at the end (don't use any flavour of autocommit)

That in most databases avoids hard parses each time the query is executed, which requires time. Parameters allow the query to be parsed only once, and then re-executed many times as needed.

Use the server facilites to check what's going on - many offer a way to inspect what running statements are doing.

Answer 3

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

Answer 4

You've got two separate things going on here. First, your Delphi program is creating Insert statements and sending them to the DB server, and then the server is handling them. You need to examine both ends to find the bottleneck. I'm not to familiar with MySql tools, but I bet you could find a SQL profiler for it easily enough. Use it to profile your inserts from the Delphi app, and compare it to running inserts from the Workbench tool and see if there's a significant difference.

If not, then the slowdown is in your app. Try hooking it up to Sampling Profiler or some other profiling tool that understands Delphi, and it'l show you where you're spending lots of time on. Once you know that, then you can work on attacking the problem, or maybe come back here to ask a more specific question. But until you know where the problem is coming from, any answers you get here are just gonna be educated guesses at best.

Answer 5

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.