Not to mention having to check each new table to see if it's already in the database and the associated physical reads that might be associated with that.
-----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich Sent: Wednesday, September 04, 2013 8:47 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Table name length in sqlite affects performance. Why? 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 This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
