On 6 Sep 2013, at 7:39pm, Jared Albers <jalb...@mymail.mines.edu> wrote:
> I really just wanted to understand the "why" in the table creation > performance. > > I can create a database where the table names are instead described as > rows, and the creation of the database is *much* faster. An order of > magnitude faster. The amount of data inserted into a row doesn't > affect performance nearly as much as the amount of data used to > describe a table name. This is the part I'm trying to understand. The internal organisation is completely different. SQLite splits a database file up into pages. Each page 'belongs' to one table or to one index. So if you create 1000 tables each with 1 index, you are writing to 2000 different pages. But if you put all your data in one table with 2 indexes, it might all fit into 3 tables. So using many small tables is inefficient: you have to keep swapping between different pages, and eventually you do so much swapping you fill up your cache. But writing lots of rows to one table you might fit all the pages you need into cache. And you write fewer pages back to proper storage when you're done. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
