Re: [sqlite] Big number of tables
On Fri, 29 Nov 2013 13:33:59 + Carlos Ferreira wrote: > If I have to create more than 1000 empty tables to initialize my > application document it takes a while.. Why ship an application with logic to create 1000 empty tables? Why not ship 1000 empty tables in a predefined database? --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Big number of tables
Thank you guys. I am going to try it. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Stephan Beal Sent: sexta-feira, 29 de Novembro de 2013 14:17 To: General Discussion of SQLite Database Subject: Re: [sqlite] Big number of tables On Fri, Nov 29, 2013 at 2:37 PM, Igor Tandetnik wrote: > On 11/29/2013 8:33 AM, Carlos Ferreira wrote: > >> Any of you know how to speed up the creation of empty tables in SQlite? >> >> If I have to create more than 1000 empty tables to initialize my >> application document it takes a while.. >> > > Make sure you run all CREATE TABLE statements within a single transaction. > My guess is you don't, and then most of the time is spent in committing an > implicit transaction after every statement. Here's a simple test which shows that in action: [stephan@host:~/tmp]$ i=0; while [ $i -lt 1000 ]; do echo "create table t$i (a,b,c);"; i=$((i + 1)); done > foo.sql [stephan@host:~/tmp]$ wc -l foo.sql 1000 foo.sql [stephan@host:~/tmp]$ echo 'begin;' > bar.sql [stephan@host:~/tmp]$ cat foo.sql >> bar.sql [stephan@host:~/tmp]$ echo 'commit;' >> bar.sql [stephan@host:~/tmp]$ time sqlite3 x.db < foo.sql real 2m25.208s user 0m0.380s sys 0m0.468s [stephan@host:~/tmp]$ rm x.db [stephan@host:~/tmp]$ time sqlite3 x.db < bar.sql real 0m0.344s user 0m0.148s sys 0m0.000s BIG difference. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf ___ 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
Re: [sqlite] Big number of tables
On 29 Nov 2013, at 1:33pm, Carlos Ferreira wrote: > If I have to create more than 1000 empty tables to initialize my application > document it takes a while.. > > > > > > Is there any workaround? Do these tables have different columns ? If not, then create one table and use a column instead of different table names. Not only will it be faster but it'll use less space in the database file. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Big number of tables
As others have suggested, transactions, create a single file and just copy,
or throw it in memory (And as an extra thought to creating it in memory is
once created, throw it to disk via the Backup API then --if needed-- work
off that).
Another option, but probably not the best idea, is each time a table is
going to be accessed, use a "CREATE TABLE {tablename} IF NOT EXISTS".
This way the table exists when the call is made to do something with that
table. You'd probably have to comb through your code to find out when the
tables are to be accessed. Perhaps a function that calls the above SQL
statement, keep a list/collection variable hanging around that will keep
tabs of when a table was made during that session so you're not trying to
create the table EVERY call. The perk is that you might not have to have
all 1000 tables so the DB would probably be a tiny bit smaller? Ok, not a
BIG perk if you're dealing with 1000 tables.
My suggestion is a* last ditch option*. It is a lot of work to go through
code and re-test what already should be known to work with this new method,
but I figured I'd throw out another option.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Big number of tables
Hi Carlos, On 29/11/2013 9:33 PM, Carlos Ferreira wrote: Any of you know how to speed up the creation of empty tables in SQlite? If I have to create more than 1000 empty tables to initialize my application document it takes a while.. Is there any workaround? Workaround: 1. I would probably create the database with all the empty tables once and store it as a file. Whenever a new one is needed, I'd make a copy of it and use that. The copy may be a file or a blob in memory within your program or a blob in a database that you're using for your program, etc. This is assuming that the SQLite3 table creation is indeed slow. I have never tried with 1000 empty tables, so I'm not sure that it is. 2. If it is an option, do a lazy creation of tables - create only the tables that are needed when they are needed. Frankly, if you often read and write from the database, this is a bit painful since you have to add quite a few more checks As always, some of the other things that can give you a bit more speed: * Transactions * The correct kind of journal mode (including moving it to memory) etc. Best Regards, Mohit. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Big number of tables
On Fri, Nov 29, 2013 at 2:37 PM, Igor Tandetnik wrote: > On 11/29/2013 8:33 AM, Carlos Ferreira wrote: > >> Any of you know how to speed up the creation of empty tables in SQlite? >> >> If I have to create more than 1000 empty tables to initialize my >> application >> document it takes a while.. >> > > Make sure you run all CREATE TABLE statements within a single transaction. > My guess is you don't, and then most of the time is spent in committing an > implicit transaction after every statement. Here's a simple test which shows that in action: [stephan@host:~/tmp]$ i=0; while [ $i -lt 1000 ]; do echo "create table t$i (a,b,c);"; i=$((i + 1)); done > foo.sql [stephan@host:~/tmp]$ wc -l foo.sql 1000 foo.sql [stephan@host:~/tmp]$ echo 'begin;' > bar.sql [stephan@host:~/tmp]$ cat foo.sql >> bar.sql [stephan@host:~/tmp]$ echo 'commit;' >> bar.sql [stephan@host:~/tmp]$ time sqlite3 x.db < foo.sql real 2m25.208s user 0m0.380s sys 0m0.468s [stephan@host:~/tmp]$ rm x.db [stephan@host:~/tmp]$ time sqlite3 x.db < bar.sql real 0m0.344s user 0m0.148s sys 0m0.000s BIG difference. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Big number of tables
On 11/29/2013 8:33 AM, Carlos Ferreira wrote: Any of you know how to speed up the creation of empty tables in SQlite? If I have to create more than 1000 empty tables to initialize my application document it takes a while.. Make sure you run all CREATE TABLE statements within a single transaction. My guess is you don't, and then most of the time is spent in committing an implicit transaction after every statement. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
