5000 tables with 3000 records vs 1 table with 15000000 records

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

5000 tables with 3000 records vs 1 table with 15000000 records

Mike Zang-2
I have 5000 files and I want to converrt them to SQLite3 on iPad, now I
have a performance question, I am not sure which way is better for select
and insert data in SQLite3.

I have  two ideas for converting.

1. convert 1 file to 1 table, so that I will have about 5000 tables in
SQLIte3 database, and any file will have about 3000 records.

2. convert all 5000 files to 1 table, there will be 15000000 records.

Please give a suggestion before I start my programming.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 5000 tables with 3000 records vs 1 table with 15000000 records

Drake Wilson-3
Quoth Mike Zang <[hidden email]>, on 2010-08-22 17:51:05 +0900:
> I have  two ideas for converting.
>
> 1. convert 1 file to 1 table, so that I will have about 5000 tables in
> SQLIte3 database, and any file will have about 3000 records.
>
> 2. convert all 5000 files to 1 table, there will be 15000000 records.
>
> Please give a suggestion before I start my programming.

This doesn't say very much.  It depends a lot on what kind of files
these are.  In general, I would recommend picking whatever is most
semantically appropriate first, and then optimizing later.  SQLite
scales well to millions of records for the most part, but your data
may have unusual characteristics that you haven't mentioned.

Since you mention the possibility of placing the data from all the
files in a single table, does this imply that all the files contain
the same type of records with the same meaning?  If so, that may
indicate that a single table solution is more appropriate so that any
subset of the records can be selected at once.

   ---> Drake Wilson
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 5000 tables with 3000 records vs 1 table with 15000000 records

Mike Zang-2
Thanks for your reply.

In fact, the file is stock data and one file is for one stock, so they are
all in the same format.

Then, in most case, only one stock should be selected.

--- Drake Wilson <[hidden email]> wrote:

> Quoth Mike Zang <[hidden email]>, on 2010-08-22 17:51:05 +0900:
> > I have  two ideas for converting.
> >
> > 1. convert 1 file to 1 table, so that I will have about 5000 tables
> in
> > SQLIte3 database, and any file will have about 3000 records.
> >
> > 2. convert all 5000 files to 1 table, there will be 15000000
> records.
> >
> > Please give a suggestion before I start my programming.
>
> This doesn't say very much.  It depends a lot on what kind of files
> these are.  In general, I would recommend picking whatever is most
> semantically appropriate first, and then optimizing later.  SQLite
> scales well to millions of records for the most part, but your data
> may have unusual characteristics that you haven't mentioned.
>
> Since you mention the possibility of placing the data from all the
> files in a single table, does this imply that all the files contain
> the same type of records with the same meaning?  If so, that may
> indicate that a single table solution is more appropriate so that any
> subset of the records can be selected at once.
>
>    ---> Drake Wilson
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 5000 tables with 3000 records vs 1 table with 15000000 records

Gabor Grothendieck
In reply to this post by Mike Zang-2
On Sun, Aug 22, 2010 at 4:51 AM, Mike Zang <[hidden email]> wrote:

> I have 5000 files and I want to converrt them to SQLite3 on iPad, now I
> have a performance question, I am not sure which way is better for select
> and insert data in SQLite3.
>
> I have  two ideas for converting.
>
> 1. convert 1 file to 1 table, so that I will have about 5000 tables in
> SQLIte3 database, and any file will have about 3000 records.
>
> 2. convert all 5000 files to 1 table, there will be 15000000 records.
>
> Please give a suggestion before I start my programming.

Don't know how generalizable this is but I tried this with a single
10+ GB table made up of ~ 1000 similarly formatted tables of ~ 75k
records each.  This is running on a USB drive under Windows Vista and
SQLite version 3.6.18.  I tried adding two indexes.  The first "
create index " took a few minutes to an hour to create and I killed
the second " create index " after it was still going 24 hours later.
" select count(*) " takes 5 minutes to run but " select count(*) from
data where x = 'X' " returns immediately where x is the first column
in the index that succeeded.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 5000 tables with 3000 records vs 1 table with 15000000 records

Alexey Pechnikov-2
In reply to this post by Mike Zang-2
See tests for 100 millions records in single table:

http://book.mobigroup.ru/dir?name=web_project_DBMS/distributed_schema

And see
http://geomapx.blogspot.com/2009/11/postgresql-81-vs-sqlite-3620-in-real.html
http://geomapx.blogspot.com/2009/09/sqlite-3617-mobigroup2.html

Old versions of SQLite had degradation of indexing speed on big tables
http://geomapx.blogspot.com/2009/11/degradation-of-indexing-speed.html
Use WAL mode in SQLite from version 3.7.0 or/and SSD instead of HDD.
3.7.1 has SQLITE_FCNTL_CHUNK_SIZE option and may resolve this
problem but I don't test it yet.

--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 5000 tables with 3000 records vs 1 table with 15000000records

ve3meo-2
In reply to this post by Mike Zang-2

"Mike Zang" ...
> In fact, the file is stock data and one file is for one stock, so they are
> all in the same format.
>
> Then, in most case, only one stock should be selected.
>
Then, from a 'disk' operating system and memory management perspective,
wouldn't one file per stock continue to be faster and more efficient than
having all the stocks in one big file? A master database with pointers to
each of the stock databases, ATTACH as many as you want to view at one time,
maybe combine data into temporary table(s), create and drop indexes on the
fly thus keeping storage at a minimum.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 5000 tables with 3000 records vs 1 table with 15000000records

Simon Slavin-3

On 22 Aug 2010, at 6:34pm, ve3meo wrote:

> A master database with pointers to
> each of the stock databases, ATTACH as many as you want to view at one time,
> maybe combine data into temporary table(s), create and drop indexes on the
> fly thus keeping storage at a minimum.

I was going to suggest something else.  If you think you might ever want one application to report on two stocks in the same run, then use one huge file with all the data in.  If that makes no sense, and you would never compare/total/merge stocks on the same display, then use separate files.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users