MySQL Forums
Forum List  »  SQLite

INSERTS get progressively slower
Posted by: Dean Sadites
Date: July 22, 2005 05:01PM

I just installed MySQL 4.1.13 and the MySQL Connector Net 1.04. I've written a C# script to create a table and insert approximately 2.4 million records from a table in another database (Oracle). The problem is that the more records that are inserted, the slower it gets. This behavior persists whether I do all inserts as a single transaction, or whether I do a commit after each group of 10,000 or 100,000 inserts. Doesn't seem to matter -- just keeps getting slower and slower. I can do the exact same operation on a SQLite database under the exact same conditions (i.e. same script, new table, no indexes) and insert the entire 2.4 million records in about 16 minutes. I would have thought that MySQL would at least be able to match or exceed what SQLite can do.

Below is the log from my script. In this case, I'm doing a begin transaction and commit for each block of 10,000 rows. It starts off taking about 8 seconds for the first 10,000, but it's already up to almost 2.5 minutes for the 10,000 rows between 440,000 and 450,000. SQLite would have finished inserting the full 2.4 million records about 15 minutes earlier. (I realize that MySQL provides a bulk insert from file capability, but I'm really not interested in using this. If SQLite can do what I want, I think MySQL should be able to also.) Can anyone explain what's going on here and how performance might be improved (or at least be consistent for each group of inserts)?

2005-07-22 19:20:05 10,000 records written...
2005-07-22 19:20:13 20,000 records written...
2005-07-22 19:20:23 30,000 records written...
2005-07-22 19:20:31 40,000 records written...
2005-07-22 19:20:40 50,000 records written...
2005-07-22 19:20:51 60,000 records written...
2005-07-22 19:21:00 70,000 records written...
2005-07-22 19:21:09 80,000 records written...
2005-07-22 19:21:22 90,000 records written...
2005-07-22 19:21:34 100,000 records written...
2005-07-22 19:21:48 110,000 records written...
2005-07-22 19:22:01 120,000 records written...
2005-07-22 19:22:14 130,000 records written...
2005-07-22 19:22:30 140,000 records written...
2005-07-22 19:22:45 150,000 records written...
2005-07-22 19:23:04 160,000 records written...
2005-07-22 19:23:21 170,000 records written...
2005-07-22 19:23:39 180,000 records written...
2005-07-22 19:23:57 190,000 records written...
2005-07-22 19:24:16 200,000 records written...
2005-07-22 19:24:35 210,000 records written...
2005-07-22 19:24:55 220,000 records written...
2005-07-22 19:25:17 230,000 records written...
2005-07-22 19:25:46 240,000 records written...
2005-07-22 19:26:15 250,000 records written...
2005-07-22 19:26:46 260,000 records written...
2005-07-22 19:27:23 270,000 records written...
2005-07-22 19:28:06 280,000 records written...
2005-07-22 19:28:55 290,000 records written...
2005-07-22 19:29:47 300,000 records written...
2005-07-22 19:30:47 310,000 records written...
2005-07-22 19:31:55 320,000 records written...
2005-07-22 19:33:14 330,000 records written...
2005-07-22 19:34:46 340,000 records written...
2005-07-22 19:36:24 350,000 records written...
2005-07-22 19:38:05 360,000 records written...
2005-07-22 19:39:58 370,000 records written...
2005-07-22 19:41:58 380,000 records written...
2005-07-22 19:43:54 390,000 records written...
2005-07-22 19:46:01 400,000 records written...
2005-07-22 19:48:12 410,000 records written...
2005-07-22 19:50:29 420,000 records written...
2005-07-22 19:52:45 430,000 records written...
2005-07-22 19:55:53 440,000 records written...
2005-07-22 19:58:17 450,000 records written...

Options: ReplyQuote


Subject
Views
Written By
Posted
INSERTS get progressively slower
12438
July 22, 2005 05:01PM
11541
May 28, 2006 01:48AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.