Hi! > I am putting finishing touches on quite a large application that uses Sqlite > for the single user edition. My experience has been that inserting data is > quite slow in comparison to most other dbs. All other operations are faster > than any other db. It appears that Sqlite is highly optimized for SELECT, > UPDATE, and DELETE, but INSERT is a bit slow. > > Although the insertion speed of Sqlite is plenty fast for a single user app > or a medium sized web app. > > If my instincts and research is correct, most other databases simply cache > the transactions in memory to give the appearance that they are inserting > very quickly, but the truth is they are probably not quite as safe as Sqlite > since it writes the data to disk immediately upon receipt. This appears to > be the bottleneck. No, all DBMSs that support transactions ensure that the data has been written to disk when a commit is performed. The technical details are very different of course, but all systems ensure that commited data is on the disk. SQLite doesn't write data to the disk when you insert rows, but when you commit the changes - so it shouldn't be slower than other systems.
DRH wrote in his answer that other DBMSs don't really write data to the disk (fsync()) as you guessed. But I cannot believe that - and I am aware of some discussions on the Firebird and PostgresSQL mailing lists months (or years?) ago about this issue... These systems are carefully designed to guarantee atomic commits and to ensure that commited data is safe (to the degree that the operating system and the disk driver can guarantee). I also cannot believe that major systems like Oracle or Informix which I think are designed very well don't really save the data correctly... BTW: You say that an update query is fast, but an insert is not. That is strange, because both statements require a commit. And when the commit is the bottleneck it should be present in both cases... If your inserts are slow, my first guess is that you did not put them in a single large transaction. This has been discussed on this list some thousand times, but anyhow - this seems to be the most often made error... DRH's speed comparision shows that insert statements are faster than with other dbmss when a transaction is used and slower if not... What I don't understand is that other systems don't show this effect. DRH tested inserts with and without transactions on several DBMSs. PostgreSQL and MySQL where faster _without_ explicit transactions. That looks a bit strange to me... The effect in PostgreSQL isn't very large and maybe you can explain that with the complex transaction handling in PostgrelSQL. But MySQL looks really strange to me (I have never used MySQL - now I know why ;-))... > I would be interested in knowing more about the internals of Sqlite that > make it perform without having to do a major study of the code. I have read > the architecture books on most other major databases, but not on Sqlite. : -) Read the code - it is very, very well documented - and short enough to be understood! - Danny -- Danny Reinhold Reinhold Software & Services --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
