RE: [sqlite] SQLite or MS Access
Yeah I have on many more than one occasion in MSAccess lost information when the program is terminated, that is why so many people hate it and don't want to go near it for any important data. I have heard however that MSAccess from 2000 onwards is based on the SQL server codebase so it is more stable, but apparently not designed to be very durable, once again I could be totally wrong but just a rumour I have heard. SQL Server on the other hand should be ACID by default?, and remains fast even if you commit on each insert, however when I say fast probably as fast as SQLite doing the same thing, but when SQLite is batched in transactions it becomes exponentially faster, where as SQLServer and other giants the speed is much closer to eachother whether you do many in one transaction or one at a time per transaction. However once again you never no how much system and disk resources are taken by things like SQLServer that helps them to do faster commits, im not sure. However nothing can get away from the fact that at some point disk buffers have to be flushed and that is up to hardware, I think many programmers are probably oblivious to how ACID like their transactions are and have probably simply not experienced such critical failure that their 'cached' inserts was never committed. Im assuming that SQL server probably keeps open logfiles that obviously can be written to much faster and have separate processes/threads that commit those to disk, SQLite does not create more processes or threads that I know of and I think many who are looking for fast lightweight solutions prefer this. SQLite however does get a lot faster when turning the synchronous off and not flushing buffers, but then again do you want to take that gamble. -Original Message- From: Nuno Lucas [mailto:[EMAIL PROTECTED] Sent: 07 September 2007 02:46 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQLite or MS Access On 9/7/07, Andre du Plessis <[EMAIL PROTECTED]> wrote: [..] > 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... MS Access has several modes of operation, but the default one is not ACID, so it seems fast. There are ways to set the transaction mode to be ACID (which I don't recall what/how they are), which will make the performance suffer behind any hope, making the comparison more fair. I don't recall now, but I seem to remember the default mode doesn't even guarantee the data is on disc when finished using it, which makes inserting a single record an order of magnitude slower on SQLite (because it waits for the data to get to the disk controller) than for Access (which just gives the data to the OS, not caring if it goes to disk or not). In a nutshell, benchmarks are not easy... Regards, ~Nuno Lucas - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite or MS Access
On 9/7/07, Andre du Plessis <[EMAIL PROTECTED]> wrote: [..] > 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... MS Access has several modes of operation, but the default one is not ACID, so it seems fast. There are ways to set the transaction mode to be ACID (which I don't recall what/how they are), which will make the performance suffer behind any hope, making the comparison more fair. I don't recall now, but I seem to remember the default mode doesn't even guarantee the data is on disc when finished using it, which makes inserting a single record an order of magnitude slower on SQLite (because it waits for the data to get to the disk controller) than for Access (which just gives the data to the OS, not caring if it goes to disk or not). In a nutshell, benchmarks are not easy... Regards, ~Nuno Lucas - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLite or MS Access
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] -
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] -
Re: [sqlite] SQLite or MS Access
have you try this on usb key ? i've very bad benchmark on this, try in hard disk in this case Sylko Zschiedrich a écrit : Do all insert's in one transaction and it will be done in 1 second or less. Begin transaction 1..1000 insert into table Commit transaction Ciao Sylko -Ursprüngliche Nachricht- Von: Michael Martin [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 7. September 2007 10:06 An: sqlite-users@sqlite.org Betreff: [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] -
RE: [sqlite] SQLite or MS Access
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] -
