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: [email protected] 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] -----------------------------------------------------------------------------
