I already grasp the reasoning for why the creation of tables can be expensive and inefficient. What I don't understand is why the length of a table name affects performance so greatly, while the length of data in a row has almost no affect on performance. Does a longer table name somehow require more pages? This is what I mean to point out.
On Stack Overflow, Richard Hipp made the argument that "it should come as no surprise that SQLite takes 4x longer to write 4x as much content." This argument doesn't make sense to me when 4x the data in a row has a negligible affect on performance (when inserting the row). -Jared On Fri, Sep 6, 2013 at 1:07 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
