The problem was transactions Thanks all
-----Message d'origine----- De : Andre du Plessis [mailto:[EMAIL PROTECTED] Envoyé : vendredi 7 septembre 2007 10:25 À : [email protected] 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: [email protected] 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] -----------------------------------------------------------------------------
