On Tue, Sep 03, 2013 at 06:43:52PM -0600, Jared Albers scratched on the wall:
> On my machine, when using relatively short table names like
> `TABLE_{table #}`, creation of a database with 10,000 tables takes
> approximately 14 seconds. These table names vary from 7 to a max of 11
> characters.
>
> When using relatively long table names like `TABLE_{table #}_{some
> unique identifying name that adds 120 or so characters}`, creation of
> a database with 10,000 tables takes approximately 60 seconds.
>
> Creating the database with long table names took over 4 times longer!
>
> Why is this the case? Is this expected behavior or a bug?
You're asking SQLite to deal with 10x the data, and it takes 4x
longer. That seems like a win to me.
Table names are stored as plain-text strings in the sqlite_master
table. Making the names much, much bigger is going to add more data
to the table, including more database pages. While sqlite_master is
not ordered, so the insertion shouldn't require shuffling the
internal B-tree, you're still dealing with a lot more pages, and
syncing all those pages to disk is going to take longer.
Like any other large insert, try wrapping the whole thing in a
transaction. SQLite is one of the few databases that allows
transactions on DDL.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
