Hi!
I use a simple trick:
A clone of the sqlite3_exec that passes the sqlite3_stmt as an argument
to the callback function.
So no conversion is made and we can use the sqlite3_column... functions
directly on the retrieved row.
Happy coding!
On 2019-07-30 05:00, sqlite-users-requ...@mailinglists.sqlite.org wrote:
Send sqlite-users mailing list submissions to
sqlite-users@mailinglists.sqlite.org
6. sqlite3_exec without ubiqitous text conversions (Barone Ashura)
------------------------------
Message: 6
Date: Tue, 30 Jul 2019 13:13:52 +0200
From: Barone Ashura <bar0n3ash...@gmail.com>
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] sqlite3_exec without ubiqitous text conversions
Message-ID:
<cad3lth+nfostqjf50dgsdzuts0exvk7n89amtsjpygx_6b+...@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Hello,
I have been working on an application based on SQLite for 2 years now,
and
recently we started running some performance profiling to check if
there
are areas where we can squeeze some extra performance.
SQlite query execution is used almost exclusively through sqlite3_exec,
and
the implementation of callbacks.
One of the areas of interest we identified is that every column is
always
passed to the callback as a c-char-string. Then we need to convert that
string back to the intended data type. Apparently a consistent amount
of
time is spent doing this.
I started looking into SQLite code and realized that int and real
values
retrieved from database, is actually fecthed as binary value, then
converted to string, passed to the callback, and in the callback we
convert
it back to its original data type (as we keep track of 'true' datatype
for
each column).
I decided to try skipping these conversions, by creating a custom
implementation of sqlite3_exec, which does the following in the inside
'step' loop:
if (rc == SQLITE_ROW) {
azVals = azCols;
for (i = 0; i < nCol; i++) {
azVals[i] = (char *)sqlite3_column_text(pStmt,
i);
int col_type = sqlite3_column_type(pStmt, i);
switch (col_type)
{
case SQLITE_INTEGER:
case SQLITE_FLOAT:
{
azVals[i] = (char*)columnMem(pStmt, i);
columnMallocFailure(pStmt);
break;
}
default:
{
azVals[i] = (char
*)sqlite3_column_text(pStmt, i);
break;
}
}
if (!azVals[i] && sqlite3_column_type(pStmt, i)
!=
SQLITE_NULL) {
sqlite3OomFault(db);
goto exec_out;
}
}
}
Instead of ALWAYS converting to text, in case of INTEGER or FLOAT
columns
types, I fetch the address of the data through columnMem, put it in
the
azVals array, and go on. The callback knows which is the expected data
type
for the column, reads and copies data in the destination variable
through a
simple assignment.
I am aware that forcing a 'typed' pointer into a generic char* pointer
(azVals[x]), rings a looooot of alarm bells; I am, as well, aware that
I
could avoid sqlite3_exec, and call sqlite3_step myself.
As of now the application is running smoothly and faster as far as
satabase
access is concerned.
I am writing here to get opinions about other potential pitfalls or
oversights in this approach, as well as reason why I should not proceed
on
this path.
Thanks in advance
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
