What you are doing is a very bad idea indeed. a) you are circumventing the intended interface b) you are breaking encapsulation, because columnMem returns a pointer to an internal type, which is useless to you, unless you have made public all the SQLite internals c) you are assuming that type conversion of a Mem type will conserve the original fields d) you are duplicating work by calling sqlite3_column_text() twice, unless the type is numerical e) you are duplicating work by calling sqlite3_column_type() twice, instead of using the value returned from the first call f) you are using the result of a function call that is documented to be undefined in the exact context you are using it in
IMHO, you would be much better off attempting to master the official SQLite API. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Barone Ashura Gesendet: Dienstag, 30. Juli 2019 13:14 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] sqlite3_exec without ubiqitous text conversions 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 ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
