Xavier Noria said: > I have a simple schema and a sql loader that fills a table with > initial values: > > delete from foo; > insert into foo ...; > insert into foo ...; > ... about 50 inserts ... > > To my surprise, the execution of these inserts took a few seconds > (SQLite is 3.3.3). However, if I wrapped the entire loader in a > transaction: > > begin transaction; > delete from foo; > insert into foo ...; > insert into foo ...; > ... about 50 inserts ... > commit transaction; > > then it was immediate. Why?
When you didn't wrap it in a transaction, you really had about 50 little transactions. That gets expensive in a hurry. So when you're doing bulk inserts remember to make transactions. When I had to insert a 800k records into a database I found that is changed insertion time from hours to a few minutes. Clay Dowling -- Simple Content Management http://www.ceamus.com
