SQLite slow when lots of tables

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

SQLite slow when lots of tables

mzzdvd
Dear,

I developed an application that need to create 1 table with thousand of rows every time when a certain event occours.

This works in a good way, but when the number of the tables become huge (about 15000/20000 tables) the first DataBase reading query, after Database open, is very slow (about 4sec.) while next reading operations are faster.

How can I speed up?

Thanks.

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

Re: SQLite slow when lots of tables

Richard Hipp-3
On 1/28/19, [hidden email] <[hidden email]> wrote:
> Dear,
>
> I developed an application that need to create 1 table with thousand of rows
> every time when a certain event occours.
>
> This works in a good way, but when the number of the tables become huge
> (about 15000/20000 tables) the first DataBase reading query, after Database
> open, is very slow (about 4sec.) while next reading operations are faster.

When the database first opens, SQLite has to load the schema for the
database (how else is it suppose to know the structure of the
database?) which in your case involves reading and parsing 20,000
CREATE TABLE statements.

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

Re: SQLite slow when lots of tables

Chris Locke-3
In reply to this post by mzzdvd
Why do you need to create a new table each time?  Its easier to create a
relational database.  This means create one main table that might (for
example) have a 'tableID' field, which points to just one other table.
This means you only need two tables.  Not 20,000+
Just an idea.  Depends on what you're trying to achieve.

On Mon, Jan 28, 2019 at 4:18 PM <[hidden email]> wrote:

> Dear,
>
> I developed an application that need to create 1 table with thousand of
> rows every time when a certain event occours.
>
> This works in a good way, but when the number of the tables become huge
> (about 15000/20000 tables) the first DataBase reading query, after Database
> open, is very slow (about 4sec.) while next reading operations are faster.
>
> How can I speed up?
>
> Thanks.
>
>      Davide
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite slow when lots of tables

Simon Slavin-3
In reply to this post by mzzdvd
On 28 Jan 2019, at 4:17pm, [hidden email] wrote:

> when the number of the tables become huge (about 15000/20000 tables) the first DataBase reading query, after Database open, is very slow (about 4sec.) while next reading operations are faster.
>
> How can I speed up?

Put all the data in the same table.

At the moment, you pick a new table name each time you write another set of data to the database.  Instead of that, create just one big table, and add an extra column to the columns which already exist called "dataset".  In that you put the string you previously used as the table name.

SQL is not designed to have a variable number of tables in a database.  All the optimization is done assuming that you will have a low number of tables, and rarely create or drop tables.

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

Re: SQLite slow when lots of tables

R Smith-2
In reply to this post by mzzdvd
On 2019/01/28 6:17 PM, [hidden email] wrote:
> I developed an application that need to create 1 table with thousand of rows every time when a certain event occours.
>
> This works in a good way, but when the number of the tables become huge (about 15000/20000 tables) the first DataBase reading query, after Database open, is very slow (about 4sec.) while next reading operations are faster.
>
> How can I speed up?


This is because SQLite has to parse 15000/20000 tables when it connects
to that DB, it's a laborious process and is actually very fast at 4
seconds.

The problem here is a design one.

Accessing table data is fast, accessing tables are slow. You should have
more data, less tables.  It's like storing a few boxes of your goods in
a million warehouses around the World. It's slow to retrieve. Rather
have one warehouse big enough to hold all your boxes.

Put your data in a single table. One table with 10 million rows will be
fast as lightening in SQLite, while 10,000 tables with 1,000 rows each
will work excessively slow. (They are slow in all RDBMS systems but you
won't notice it much in client-server systems because the table schema
remains cached in the server).

In stead of having tables like "MyData_1", "MyData_2", "MyData_3", ... ,
"MyData_n" - rather add an extra indexed field to one table called only
"MyData" that carries the 1, 2, 3 ... n etc.

That way you would end up with one table having millions of rows and not
millions of tables having few rows.

Cheers!
Ryan


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

Re: SQLite slow when lots of tables

R Smith-2
In reply to this post by Chris Locke-3
When the same solution is offered in rapid intervals, it must be the
correct one then! :)

To elaborate on what Chris mentioned regarding relational database
methodology and related tables, here is why and a good description of
how to do that correctly:
https://en.wikipedia.org/wiki/First_normal_form

And to add to the slew of "Few-tables-many-rows rather than
Many-tables-few-rows" solutions offered, one thing to note:  After a
single table contains a few million rows, INSERTing will become slightly
slower, but the difference will still be measured in milliseconds rather
than seconds. This really is by far the fastest (and correctest) way.


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

Re: SQLite slow when lots of tables

Igor Korot
In reply to this post by mzzdvd
Hi,

On Mon, Jan 28, 2019 at 10:17 AM <[hidden email]> wrote:
>
> Dear,
>
> I developed an application that need to create 1 table with thousand of rows every time when a certain event occours.

Are you coming from the FoxBase/ForPro world?

Thank you.

>
> This works in a good way, but when the number of the tables become huge (about 15000/20000 tables) the first DataBase reading query, after Database open, is very slow (about 4sec.) while next reading operations are faster.
>
> How can I speed up?
>
> Thanks.
>
>      Davide
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite slow when lots of tables

James K. Lowden
In reply to this post by Simon Slavin-3
On Mon, 28 Jan 2019 16:28:41 +0000
Simon Slavin <[hidden email]> wrote:

> SQL is not designed to have a variable number of tables in a
> database.  All the optimization is done assuming that you will have a
> low number of tables, and rarely create or drop tables.

This.

The table name should not be meaningful to your application; nothing in
your application should conjure up a table name.  It's an external
factor your application is configured to use.  

If you're generating table names based on application data, you're
insinuating data in the metadata.  When Simon says "SQL is not
designed" for that, he's referring to the fact that the schema is
expected to be relatively stable because it *describes* the data.  The
system is designed to search for data in the tables, not among their
names.  

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

Re: SQLite slow when lots of tables

Warren Young
In reply to this post by mzzdvd
On Jan 28, 2019, at 9:17 AM, [hidden email] wrote:
>
> I developed an application that need to create 1 table with thousand of rows every time when a certain event occours.
>
> This works in a good way, but when the number of the tables become huge (about 15000/20000 tables) the first DataBase reading query, after Database open, is very slow (about 4sec.) while next reading operations are faster.

This sounds like a “table per client” sort of architecture.  If so, and the others’ advice to put all the data in a single table won’t work for you, you can easily use SQLite to create a separate DB file for each client.  Each file can have the same schema, or not.

If some operations will need to work with data from multiple instances of this table, SQLite makes it easy to attach multiple DB files to a single connection and cross-query them.

Doing it this way may end up faster than the “everything in one table” approach, since SQLite’s performance is roughly proportional to the log2(n) of the number of rows in the tables you’re working with.  20000 DB files with one table per and N rows each might be faster than one DB file with N*20000 rows in a single table.

There are speed hits in that architecture, too.  

For one, the disk directory file lookup might begin to take significant amounts of time at some point.  Usually this begins to happen at about this “tens of thousands” area, so if you’re looking at going into the hundreds of thousands or millions of DB files, you might want to make the directory structure hierarchical in some way.

Another speed trap risk here is that each opened DB takes some memory of its own.  If you’ve got RAM to burn, this might be great architecture, but if you run the system into swapping, the single-table architecture would probably be better.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite slow when lots of tables

Chris Locke-3
In reply to this post by James K. Lowden
> The table name should not be meaningful to your application; nothing in
> your application should conjure up a table name.

I can't get my head around this advice.  Is this just for this occasion, or
for every application?  What if I'm writing a customer address book?  Am I
allowed a table called 'customers' ?  Thats meaningful to my application.
Not sure what your trying to advise here.

On Mon, Jan 28, 2019 at 6:59 PM James K. Lowden <[hidden email]>
wrote:

> On Mon, 28 Jan 2019 16:28:41 +0000
> Simon Slavin <[hidden email]> wrote:
>
> > SQL is not designed to have a variable number of tables in a
> > database.  All the optimization is done assuming that you will have a
> > low number of tables, and rarely create or drop tables.
>
> This.
>
> The table name should not be meaningful to your application; nothing in
> your application should conjure up a table name.  It's an external
> factor your application is configured to use.
>
> If you're generating table names based on application data, you're
> insinuating data in the metadata.  When Simon says "SQL is not
> designed" for that, he's referring to the fact that the schema is
> expected to be relatively stable because it *describes* the data.  The
> system is designed to search for data in the tables, not among their
> names.
>
> --jkl
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite slow when lots of tables

Warren Young
On Jan 28, 2019, at 2:44 PM, Chris Locke <[hidden email]> wrote:
>
>> The table name should not be meaningful to your application; nothing in
>> your application should conjure up a table name.
>
> I can't get my head around this advice.  Is this just for this occasion, or
> for every application?  What if I'm writing a customer address book?  Am I
> allowed a table called 'customers' ?  Thats meaningful to my application.
> Not sure what your trying to advise here.

He’s saying that if your customer is called Bill The Cat, you should not call the table holding that customer’s records “Bill the Cat”.

How else do you end up with 20000 tables in a single database, each created on the fly based on some unspecified event, as the OP talks about?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite slow when lots of tables

Simon Slavin-3
In reply to this post by Chris Locke-3
On 28 Jan 2019, at 9:44pm, Chris Locke <[hidden email]> wrote:

>> The table name should not be meaningful to your application; nothing in your application should conjure up a table name.
>
> I can't get my head around this advice.  Is this just for this occasion, or for every application?  What if I'm writing a customer address book?  Am I allowed a table called 'customers' ?  Thats meaningful to my application.

The text you quote is unclear, and I see why you questioned it.

Your database has a schema.  A schema comprises tables, indexes, triggers, views, etc..  The names of those things mean something to the programmer.  The schema doesn't change unless the programmer changes their idea about what the application does.

Inside the tables you put your data.  The data changes, as the application may have to look inside a table, and examine what it finds, to figure out what data it has and what it means.

Programmers change schemas.  Just like they change the software.
Applications change data, not schemas.

There are occasional reasons to violate these rules.  But the people who write SQL libraries assume that programmers will follow those rules, and their optimizations for execution time and memory used assume you will follow those rules.

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

Re: SQLite slow when lots of tables

Chris Locke-3
In reply to this post by Warren Young
I don't know - that's why I asked.  Thanks for the clarification.


On Mon, Jan 28, 2019 at 10:10 PM Warren Young <[hidden email]> wrote:

> On Jan 28, 2019, at 2:44 PM, Chris Locke <[hidden email]> wrote:
> >
> >> The table name should not be meaningful to your application; nothing in
> >> your application should conjure up a table name.
> >
> > I can't get my head around this advice.  Is this just for this occasion,
> or
> > for every application?  What if I'm writing a customer address book?  Am
> I
> > allowed a table called 'customers' ?  Thats meaningful to my application.
> > Not sure what your trying to advise here.
>
> He’s saying that if your customer is called Bill The Cat, you should not
> call the table holding that customer’s records “Bill the Cat”.
>
> How else do you end up with 20000 tables in a single database, each
> created on the fly based on some unspecified event, as the OP talks about?
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite slow when lots of tables

mzzdvd
In reply to this post by Simon Slavin-3
Dear all,

what happens if I put all data in a single table and this table become very huge (for example millions of rows)?

Will I have same performace problems?

Thanks.


Regards.

>
>     Il 28 gennaio 2019 alle 17.28 Simon Slavin <[hidden email]> ha scritto:
>
>     On 28 Jan 2019, at 4:17pm, [hidden email] wrote:
>
>         > >
> >         when the number of the tables become huge (about 15000/20000 tables) the first DataBase reading query, after Database open, is very slow (about 4sec.) while next reading operations are faster.
> >
> >         How can I speed up?
> >
> >     >
>     Put all the data in the same table.
>
>     At the moment, you pick a new table name each time you write another set of data to the database. Instead of that, create just one big table, and add an extra column to the columns which already exist called "dataset". In that you put the string you previously used as the table name.
>
>     SQL is not designed to have a variable number of tables in a database. All the optimization is done assuming that you will have a low number of tables, and rarely create or drop tables.
>
>     Simon.
>
>     _______________________________________________
>     sqlite-users mailing list
>     [hidden email]
>     http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite slow when lots of tables

Chris Locke-3
Ryan Smith has already covered this scenario.

"And to add to the slew of "Few-tables-many-rows rather than
Many-tables-few-rows" solutions offered, one thing to note:  After a single
table contains a few million rows, INSERTing will become slightly slower,
but the difference will still be measured in milliseconds rather than
seconds. This really is by far the fastest (and correctest) way."

On Tue, Jan 29, 2019 at 11:01 AM <[hidden email]> wrote:

> Dear all,
>
> what happens if I put all data in a single table and this table become
> very huge (for example millions of rows)?
>
> Will I have same performace problems?
>
> Thanks.
>
>
> Regards.
>
> >
> >     Il 28 gennaio 2019 alle 17.28 Simon Slavin <[hidden email]>
> ha scritto:
> >
> >     On 28 Jan 2019, at 4:17pm, [hidden email] wrote:
> >
> >         > >
> > >         when the number of the tables become huge (about 15000/20000
> tables) the first DataBase reading query, after Database open, is very slow
> (about 4sec.) while next reading operations are faster.
> > >
> > >         How can I speed up?
> > >
> > >     >
> >     Put all the data in the same table.
> >
> >     At the moment, you pick a new table name each time you write another
> set of data to the database. Instead of that, create just one big table,
> and add an extra column to the columns which already exist called
> "dataset". In that you put the string you previously used as the table name.
> >
> >     SQL is not designed to have a variable number of tables in a
> database. All the optimization is done assuming that you will have a low
> number of tables, and rarely create or drop tables.
> >
> >     Simon.
> >
> >     _______________________________________________
> >     sqlite-users mailing list
> >     [hidden email]
> >     http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite slow when lots of tables

Simon Slavin-3
In reply to this post by mzzdvd
On 29 Jan 2019, at 11:00am, [hidden email] wrote:

> what happens if I put all data in a single table and this table become very huge (for example millions of rows)?
>
> Will I have same performace problems?

I used to use a 43 Gigabyte SQLite database of which 42 Gigabytes was taken up with a single table.  Worked fine.  And fast.

That was several versions of SQLite ago.  SQLite is faster now.

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

Re: SQLite slow when lots of tables

Andy Bennett
In reply to this post by mzzdvd
Hi,

> what happens if I put all data in a single table and this table
> become very huge (for example millions of rows)?
>
> Will I have same performace problems?

The INSERTing profile has been covered by others but I'll just add
something about SELECT as well.

It depends on how you query it. i.e. what you put in the WHERE clause. If
it's by primary key ("WHERE <primary-key> <something> <something>") then it
will remain fast. If it's by other columns then you may need to add an
index. When you need to do this will depend on the exact nature of the
query.

If a query becomes slow you can prefix it with "EXPLAIN QUERY PLAN " to see
what it is doing. You can use that information to add the correct index.

Of course, when that happens, we can help you with interpretation of the
query plan if you want it.




Regards,
@ndy

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

Re: SQLite slow when lots of tables

Rob Willett
In reply to this post by mzzdvd
Millions of rows is not even large, never mind huge or very huge :)

We have tables with hundreds of millions of rows, we got to billions of
rows in a single table before we changed the logic. From memory we had
67GB for a single database and I reckon 60GB was one table. Not many
issues at all with inserting or searching. One of our data mining
queries searched the entire table and it still only took 90 secs, though
all of the query used indexes.

We only changed what we store as the management of a circa 60GB database
was too much and we worked out we only needed 1% of it. We were using a
virtual private server and we had issues with disk IO when we copied the
database around using Unix cp. This wasn't a SQLite problem at all.
However I have no doubt that SQLite was more than capable of handling
even more data.

Rob

On 29 Jan 2019, at 11:00, [hidden email] wrote:

> Dear all,
>
> what happens if I put all data in a single table and this table become
> very huge (for example millions of rows)?
>
> Will I have same performace problems?
>
> Thanks.
>
>
> Regards.
>
>>
>>     Il 28 gennaio 2019 alle 17.28 Simon Slavin <[hidden email]>
>> ha scritto:
>>
>>     On 28 Jan 2019, at 4:17pm, [hidden email] wrote:
>>
>>         > >
>>>         when the number of the tables become huge (about 15000/20000
>>> tables) the first DataBase reading query, after Database open, is
>>> very slow (about 4sec.) while next reading operations are faster.
>>>
>>>         How can I speed up?
>>>
>>>     >
>>     Put all the data in the same table.
>>
>>     At the moment, you pick a new table name each time you write
>> another set of data to the database. Instead of that, create just one
>> big table, and add an extra column to the columns which already exist
>> called "dataset". In that you put the string you previously used as
>> the table name.
>>
>>     SQL is not designed to have a variable number of tables in a
>> database. All the optimization is done assuming that you will have a
>> low number of tables, and rarely create or drop tables.
>>
>>     Simon.
>>
>>     _______________________________________________
>>     sqlite-users mailing list
>>     [hidden email]
>>     http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite slow when lots of tables

mzzdvd
In reply to this post by Simon Slavin-3
thanks a lot for fast answers. Now I'll try to modify my DB structure to use one table.

Thanks to all.

Bye.

>
>     Il 29 gennaio 2019 alle 12.29 Simon Slavin <[hidden email]> ha scritto:
>
>     On 29 Jan 2019, at 11:00am, [hidden email] wrote:
>
>         > >
> >         what happens if I put all data in a single table and this table become very huge (for example millions of rows)?
> >
> >         Will I have same performace problems?
> >
> >     >
>     I used to use a 43 Gigabyte SQLite database of whto useich 42 Gigabytes was taken up with a single table. Worked fine. And fast.
>
>     That was several versions of SQLite ago. SQLite is faster now.
>
>     Simon.
>
>     _______________________________________________
>     sqlite-users mailing list
>     [hidden email]
>     http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite slow when lots of tables

James K. Lowden
In reply to this post by mzzdvd
On Tue, 29 Jan 2019 12:00:49 +0100 (CET)
[hidden email] wrote:

> what happens if I put all data in a single table and this table
> become very huge (for example millions of rows)?

Big tables are your friend, actually.  

A binary search on  1 million rows requires at most 20 operations.  

A binary search on  2 million rows requires at most 21 operations.  

A binary search on 10 million rows requires at most 24 operations.  

Provided there are appropriate indexes, the power of O(log n) means
that as the data grow, they are incrementally easier to search.  

I had a friend who told me once that he was working on his 2nd million
dollars.   He gave up on the first milliion because it was too hard.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12