sqlite performance with large and multiple attached databases

[Akbar Syed]

I have been trying to improve the performance and memory usage for my
application whereby i have maximum of 30 databases attached. In total I have
31 databases with 30 databases attached to the first one. Each database has
a single table with approx 65 columns and the records in each table may go
upto 50,000. I have 31 connections for 31 databases i.e. 1 connection for
one database. For each connection I have a cache size of 500 pages (1 Page =
1KB), temporary cache 500 pages and for each attached connection cache size
of 100 pages. My efforts to minimize memory usage as much as I can also the
speed of reading is tolerable. I dont mind the writing speed, but I do care
for reading speed. In one attempt, I would select all the records from all
the databases and thats the purpose I am using attached databases with a
single query.

In one attempt i tried to fetch just 250 records of 65 columns from 31
databases and I observed that I spend approx 1-5 seconds in the first call
to sqlite3_step() function and the subsequent calls to sqlite3_step() are
some microseconds.
Can anyone throw much light on this? I appreciate your suggestions to
further improve my speed and memory usage.

-Akbar

[Jay A. Kreibich]

Post by Akbar Syed
I have been trying to improve the performance and memory usage for my
application whereby i have maximum of 30 databases attached. In total I have
31 databases with 30 databases attached to the first one. Each database has
a single table with approx 65 columns and the records in each table may go
upto 50,000. I have 31 connections for 31 databases i.e. 1 connection for
one database. For each connection I have a cache size of 500 pages (1 Page =
1KB), temporary cache 500 pages and for each attached connection cache size
of 100 pages. My efforts to minimize memory usage as much as I can also the
speed of reading is tolerable. I dont mind the writing speed, but I do care
for reading speed. In one attempt, I would select all the records from all
the databases and thats the purpose I am using attached databases with a
single query.

If you want to improve utilization memory, just put everything in one
database. Each attached database in each connection has its own cache.
With that many data sources you're going to get a very uneven distribution
of cache utilization, and very high worst-case usage.

Post by Akbar Syed
In one attempt i tried to fetch just 250 records of 65 columns from 31
databases and I observed that I spend approx 1-5 seconds in the first call
to sqlite3_step() function and the subsequent calls to sqlite3_step() are
some microseconds.

"Just 250 records" implies a sort. Unless an index is available, a
sort requires doing the whole query and sorting the results. That's
the large lead time. After that it is just popping records off the
stack, so to speak.

-j

[Akbar Syed]

Hello,

Post by Jay A. Kreibich
If you want to improve utilization memory, just put everything in one
database. Each attached database in each connection has its own cache.
With that many data sources you're going to get a very uneven distribution
of cache utilization, and very high worst-case usage.

Unfortunately, my application restricts me to use independent
databases than to a single database
as each database exists on a different device and contains the info of
that device in the database.
Multiple devices are allowed to connect to my server app which instead
gathers the records from the
independent databases by attaching the databases together.
Unfortunately, I could not find a better
solution than attach to join these databases as after gathering the
information together i need to sort
the collected records and present them together. Thatswhy sometimes I
try to select lot of records
with a single query.

May be someone suggest a better option for this situation. Is there a
way to clean cache before I make
a select to ensure I have enough cache for the select. I do use SOFT
HEAP feature already.

Post by Jay A. Kreibich
"Just 250 records" implies a sort. Unless an index is available, a
sort requires doing the whole query and sorting the results. That's
the large lead time. After that it is just popping records off the
stack, so to speak.

I do have a primary index on my very first column which is nothing but
a rowid itself. I did not
use other indexes as it may be again overhead on memory. My
implementations for databases are simple
whereby i create a database with a single table of 65 columns and
write data into it. I attach all the
databases together and make selects with ofcourse using sortby on not
on the primary index but on some other
column of type TEXT. Do u think this will slow down the things?

Regards,
-Akbar

* I have been trying to improve the performance and memory usage for my

*>* application whereby i have maximum of 30 databases attached. In total I have
*>* 31 databases with 30 databases attached to the first one. Each database has
*>* a single table with approx 65 columns and the records in each table may go
*>* upto 50,000. I have 31 connections for 31 databases i.e. 1 connection for
*>* one database. For each connection I have a cache size of 500 pages (1 Page =
*>* 1KB), temporary cache 500 pages and for each attached connection cache size
*>* of 100 pages. My efforts to minimize memory usage as much as I can also the
*>* speed of reading is tolerable. I dont mind the writing speed, but I do care
*>* for reading speed. In one attempt, I would select all the records from all
*>* the databases and thats the purpose I am using attached databases with a
*>* single query.
*
If you want to improve utilization memory, just put everything in one
database. Each attached database in each connection has its own cache.
With that many data sources you're going to get a very uneven distribution
of cache utilization, and very high worst-case usage.

* In one attempt i tried to fetch just 250 records of 65 columns from 31

*>* databases and I observed that I spend approx 1-5 seconds in the first call
*>* to sqlite3_step() function and the subsequent calls to sqlite3_step() are
*>* some microseconds.
*
"Just 250 records" implies a sort. Unless an index is available, a
sort requires doing the whole query and sorting the results. That's
the large lead time. After that it is just popping records off the
stack, so to speak.

-j

[Simon Slavin]

Post by Akbar Syed
Unfortunately, my application restricts me to use independent
databases than to a single database
as each database exists on a different device and contains the info of
that device in the database.
Multiple devices are allowed to connect to my server app which instead
gathers the records from the
independent databases by attaching the databases together.

Make one big 'working' database by reading all the data from the databases on the independent devices and putting it into one big table. Once you have sucked out the data you can disconnect from the device. Then do all your enquiries on this big table. There's a special convenient form of the INSERT command which gets its data directly from a SELECT command:

INSERT INTO bigTable SELECT * FROM attachedDB.myTable

So, in a simplified example, for each pluginnable device do this:

ATTACH "deviceFile.s3" AS attachedDB
INSERT INTO allDeviceTable SELECT * FROM attachedDB.myTable
DETACH attachedDB

Then just query allDeviceTable.

Simon.

[Akbar Syed]

Post by Simon Slavin
INSERT INTO bigTable SELECT * FROM attachedDB.myTable
ATTACH "deviceFile.s3" AS attachedDB
INSERT INTO allDeviceTable SELECT * FROM attachedDB.myTable
DETACH attachedDB

Then just query allDeviceTable.

Thanks Simon for your suggestions. Unfortunately, I cannot detach the
devices in my application.
The data to be inserted in each database could be live i.e, on every
device where a database is
created, there could be new live data which needs to be inserted in
the database as and when added
to the device and made available for a single select. Your suggestions
would have worked if it was not live data.
Sorry I forgot to mention abt this in my previous mail.
Unfortunately, I could not think about any other option than to keep
the devices attached all the time.I am trying to
find some optimum cache values for each connection. I tried not to use
many connections. I just have a single connection
open for a device and its database and one attach connection. As of
now I am using 500K of default cache for each open connection
with 100K for attached connections and 500k default temp-cache and
also I have temp-store set to a file. It might hamper my
performance, yet I dont have any other option thou.

Regards,
-Akbar

* Unfortunately, my application restricts me to use independent

*>* databases than to a single database
*>* as each database exists on a different device and contains the info of
*>* that device in the database.
*>* Multiple devices are allowed to connect to my server app which instead
*>* gathers the records from the
*>* independent databases by attaching the databases together.
*
Make one big 'working' database by reading all the data from the
databases on the independent devices and putting it into one big
table. Once you have sucked out the data you can disconnect from the
device. Then do all your enquiries on this big table. There's a
special convenient form of the INSERT command which gets its data
directly from a SELECT command:

INSERT INTO bigTable SELECT * FROM attachedDB.myTable

So, in a simplified example, for each pluginnable device do this:

ATTACH "deviceFile.s3" AS attachedDB
INSERT INTO allDeviceTable SELECT * FROM attachedDB.myTable
DETACH attachedDB

Then just query allDeviceTable.

Simon.

[Simon Slavin]

Post by Akbar Syed
Unfortunately, I could not think about any other option than to keep
the devices attached all the time.

I hope you don't have to handle many attachable devices, because SQLite can't handle more than 30 simultaneous ATTACHes. See section 11 of

<http://www.sqlite.org/limits.html>

Simon.