Transactions are the main thing to speed this up but there are others such as the various Pragma settings. If you search in this group for slow insert you will find them.
RBS > The problem was transactions > > Thanks all > > -----Message d'origine----- > De : Andre du Plessis [mailto:[EMAIL PROTECTED] > Envoyé : vendredi 7 septembre 2007 10:25 > À : sqlite-users@sqlite.org > Objet : RE: [sqlite] SQLite or MS Access > > Well here are my test results (im using Delphi for this one) > > This is my insert statement: > > INSERT INTO TEST (TEST_ID_NO_IDX, NO_INDEX, TEST_DATA, TEST_ID) values > (%d, %s, %s, %d) > This table deliberately has NO index. > > > 1000 inserts took: > Inserting MS Access - 4,043.273 ms > Inserting SQLite - 249.329 ms > > In my sample the key is doing the following > > BEGIN TRANSACTION > Loop inside transaction: > Do inserts > > COMMIT TRANSACTION > > I'm suspecting that you are falling into the trap of not doing most of > your work in a transaction, in SQLite that is BAD, sqlite tries to be as > durable as possible, so it writes to the journal and flushes file > buffers each time a transaction commits, if you don't specify one, then > this will automatically happen on each insert and will kill your speed. > > > What I don't understand is how Access, and other DB's are able to still > operate much faster (maybe not as fast), and still flush file buffers to > disk, is beyond me. Maybe it really still caches it, I would not be > surprised if you pull the plug from an MS access db you may end up with > missing records even if you committed, I could be wrong... > > Used correctly SQlite should be the fastest there is, obviously for more > single user (desktop db) style operations not multiuser. > > Ive done Inserts up to the 40-50K/ sec in sqlite with the right kind of > tweaks. > > > > -----Original Message----- > From: Michael Martin [mailto:[EMAIL PROTECTED] > Sent: 07 September 2007 10:06 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] SQLite or MS Access > > Hi All, > > > > I've done some benchmarks tests and I wonder where I've made a mistake. > > > > In C# code with SQLite.NET.2.0.1 wrapper with sqlite 3.0: 1000 inserts > in a table of two columns -> 168 seconds > > > > In C# code with Jet.Oledb.4.0 with MS Access: 1000 inserts in a table > of two columns -> 1.14 seconds > > > > Could someone help me please > > > > Thanks in advance > > > > > ----------------------------------------------------------------------------- > To unsubscribe, send email to [EMAIL PROTECTED] > ----------------------------------------------------------------------------- > > > ----------------------------------------------------------------------------- > To unsubscribe, send email to [EMAIL PROTECTED] > ----------------------------------------------------------------------------- > > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
