25 Mar
2015
25 Mar
'15
12:46 p.m.
Hi,
I would like to introduce new index for the SMS table.
I've run benchmarks using test database with about 8500 records in
both SMS and Subscriber tables. Introducing SMS(dest_addr) index
reduces execution time 4 times for this benchmark.
As far as I can see, db_sms_get_unsent() gets called frequently, so
real-life benefits might also be considerable, especially for the
large datasets.
Patch: https://gist.github.com/ati/5ef481954f5bd38d00fc
SQL queries benchmark script (python):
https://gist.github.com/ati/bd03be698a89e24d8a89
Regards,
Alexander
26 Mar
26 Mar
12:41 a.m.
On Wed, Mar 25, 2015 at 02:46:23PM +0300, Alexander Nikolaev wrote:
Hi,
Hey,
great! What is the benefit of using ";" to execute both
statements with a single sqlite3 command in db_prepare?
Any disadvantage to introduce a new key for the index?
SQL queries benchmark script (python):
https://gist.github.com/ati/bd03be698a89e24d8a89
What we would like to do is to avoid using libdbi. It has
known and unfixed memory access issues, for sqlite3 it
needs to execute pragma statements for each column we
query. Would you be interested to do more work on the
database interface?
holger
27 Mar
27 Mar
6:16 p.m.
Holger,
What is the benefit of using ";" to execute
both
statements with a single sqlite3 command in db_prepare?
I don't think there's a big difference, not in case of sqlite anyway,
since its -prepare and -exec calls support both single statements and
sql statemens list.
If separate SQL statements better match the approved code style, I've
created a patch that uses separate query for index creation --
https://gist.github.com/ati/3089e35080598495ca27
What we would like to do is to avoid using libdbi.
Do you have in mind some other abstraction layer or think that it is
better to just use native sqlite interface?
It would be great if it is possible to use asynchronous db calls, but
this requires major rewrite of the database interface.
Regards,
Alexander Nikolaev
fairwaves.co
On Thu, Mar 26, 2015 at 12:41 AM, Holger Hans Peter Freyther
<holger(a)freyther.de> wrote:
On Wed, Mar 25, 2015 at 02:46:23PM +0300, Alexander
Nikolaev wrote:
Hi,
Hey,
great! What is the benefit of using ";" to execute both
statements with a single sqlite3 command in db_prepare?
Any disadvantage to introduce a new key for the index?
SQL queries benchmark script (python):
https://gist.github.com/ati/bd03be698a89e24d8a89
What we would like to do is to avoid using libdbi. It has
known and unfixed memory access issues, for sqlite3 it
needs to execute pragma statements for each column we
query. Would you be interested to do more work on the
database interface?
holger
29 Mar
29 Mar
11:25 a.m.
On Fri, Mar 27, 2015 at 06:16:47PM +0100, Alexander Nikolaev wrote:
https://openbsc.osmocom.org/trac/wiki/Tasks/NITBAsyncDatabase
for a proposal.
holger
Holger,
If separate SQL statements better match the approved
code style, I've
created a patch that uses separate query for index creation --
https://gist.github.com/ati/3089e35080598495ca27
Have a look at
int db_prepare(void)
{
dbi_result result;
int i;
for (i = 0; i < ARRAY_SIZE(create_stmts); i++) {
result = dbi_conn_query(conn, create_stmts[i]);
if (!result) {
LOGP(DDB, LOGL_ERROR,
"Failed to create some table.\n");
return 1;
}
dbi_result_free(result);
}
so after you added the SCHEMA_SMS_IDX should be automatically
created. There should be no requirement to execute statement
from update_db_revision_3.
What we would
like to do is to avoid using libdbi.
Do you have in mind some other abstraction
layer or think that it is
better to just use native sqlite interface?
It would be great if it is possible to use asynchronous db calls, but
this requires major rewrite of the database interface.
3323
days inactive
3327
days old
3 comments
2 participants
participants (2)
-
Alexander Nikolaev -
Holger Hans Peter Freyther