Suggestion Acknowledged :)
Il giorno mar 30 lug 2019 alle ore 18:56 <be...@gensis.com.br> ha scritto:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
