Out of curiosity, I wrote a loadable module to benchmark sqlite3 in a 32 million iterations loop doing nothing to gauge its performance. There is no I/O from querying any tables. The rows are artificially created from incrementing an integer counter. 32 million rows is about the normal size of my dataset and 25% of my columns are short text.
The following are the most optimistic benchmark timing taken: 1) No memory allocation performed in my module with no column processed: select count(1) recCount from vTable; Run Time: real 5.329 user 5.179233 sys 0.031200 Run Time: real 5.288 user 5.179233 sys 0.031200 Run Time: real 5.293 user 5.194833 sys 0.015600 2) No memory allocation performed but with one integer column processed: select max(rowid) maxID from vTable; Run Time: real 9.563 user 9.453661 sys 0.000000 Run Time: real 9.452 user 9.313260 sys 0.000000 Run Time: real 9.342 user 9.344460 sys 0.000000 3) One memory allocation performed per row via the sqlite3_mprintf() statement: select max(doMPrint1) maxC from vTable; Run Time: real 34.928 user 34.803823 sys 0.000000 Run Time: real 35.789 user 34.928624 sys 0.000000 Run Time: real 35.207 user 34.866223 sys 0.000000 4) Two memory allocation performed per row via the sqlite3_mprintf() statement: select max(doMPrint1) max1 ,max(doMPrint2) max2 from vTable; Run Time: real 70.041 user 69.124043 sys 0.015600 Run Time: real 70.031 user 69.545246 sys 0.000000 Run Time: real 70.296 user 69.280044 sys 0.015600 Looking at the above result, performing memory allocation and string processing is expensive as compared to no memory allocation operation. It is still fast but I am wondering if it can still be improved upon. As SSD becomes cheaper and better of in-memory database, performance bottleneck may shift back to CPU. ARM processors still do not and may not clock as high as Intel processors. A little here and a little there can add up significantly. Could anyone else out there publish their results? Can the sqlite team comment on this? Does a different memory allocator make a difference or the issue is somewhere else? The benchmark is ran on my IBM T60p laptop with the following specs: O/S : Windows 7 32-bit SP1 CPU : Intel Core 2 Duo T7600 @ 2.33 GHz RAM : 3 GB Dual-Channel DDR2 @332 MHz (5-5-5-15) SQLite: version 3.8.4.3 GCC: version 4.8.1 Sqlite is compiled using the following command: gcc -O2 -DSQLITE_THREADSAFE=0 shell.c sqlite3.c -ldl -o sqlite3 My module is compiled using the following command: gcc -O2 -shared benchmark.c -o benchmark.so The following is the terminal display of what I typed to start my benchmark: $ sqlite3 SQLite version 3.8.4.3 2014-04-03 16:53:12 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> sqlite> .timer on sqlite> .load benchmark.so sqlite> create virtual table vTable using benchmark; Run Time: real 0.001 user 0.000000 sys 0.000000 sqlite> select count(1) recCount from vTable; 33554432 Run Time: real 5.628 user 5.288434 sys 0.000000 sqlite> The following is the source code: #include <fcntl.h> #include "sqlite3ext.h" SQLITE_EXTENSION_INIT1 // Required by SQLite extension header. static sqlite_int64 ROWID=0; #define MAX_ITERATION 1024*1024*32 //* // my extended structures. // typedef struct myVTable_ { sqlite3_vtab base; } myVTable; typedef struct myVCursor_ { sqlite3_vtab_cursor base; } myVCursor; // SQLite3 Interface routines. static int xConnect( sqlite3 *db ,void *pAux ,int pArgc ,const char * const *pArgv ,sqlite3_vtab **ppVTable ,char **pzErrMsg ) { myVTable *myVTab; myVTab = sqlite3_malloc(sizeof(myVTable) ); *ppVTable =(sqlite3_vtab *)myVTab; return SQLITE_OK; } static int xDisconnect( sqlite3_vtab *pVTable ) { myVTable *myTab= (myVTable *)pVTable; sqlite3_free( myTab ); return SQLITE_OK; } static int xCreate( sqlite3 *db ,void *pAux ,int pArgc ,const char * const *pArgv ,sqlite3_vtab **ppVTable ,char **pzErrMsg ) { int rc = SQLITE_OK; rc = xConnect( db ,pAux ,pArgc ,pArgv ,ppVTable ,pzErrMsg ); if(SQLITE_OK == rc ) { rc = sqlite3_declare_vtab( db ,"CREATE TABLE _tablename( doMPrint1 TEXT ,doMPrint2 TEXT ,doMPrint3 )" ); if(SQLITE_OK != rc ) *pzErrMsg = sqlite3_mprintf("Unable to declare virtual table!"); } return rc; } static int xRename( sqlite3_vtab *pVTable ,const char *pzNewName ) { return SQLITE_OK; } static int xDestroy( sqlite3_vtab *pVTable ) { myVTable *myVTab= (myVTable *)pVTable; sqlite3_free( myVTab ); return SQLITE_OK; } static int xOpen( sqlite3_vtab *pVTable ,sqlite3_vtab_cursor **ppVTabCur ) { myVCursor *myVCur; myVCur = sqlite3_malloc(sizeof(myVCursor)); *ppVTabCur=(sqlite3_vtab_cursor *)myVCur; ROWID=0; return SQLITE_OK; } static int xClose( sqlite3_vtab_cursor *pVTabCur ) { myVCursor *myVCur = (myVCursor *)pVTabCur; sqlite3_free( myVCur ); return SQLITE_OK; } static int xEof( sqlite3_vtab_cursor *pVTabCur ) { return (ROWID >= MAX_ITERATION); } static int xNext( sqlite3_vtab_cursor *pVTabCur ) { ROWID++; return SQLITE_OK; } static int xColumn( sqlite3_vtab_cursor *pVTabCur ,sqlite3_context *pContext ,int pColID ) { switch( pColID ) { case -1: sqlite3_result_int64(pContext ,ROWID ); break; case 0: sqlite3_result_text( pContext ,sqlite3_mprintf("doMPrintf 1") ,11 ,sqlite3_free ); break; case 1: sqlite3_result_text( pContext ,sqlite3_mprintf("doMPrintf 2") ,11 ,sqlite3_free ); break; case 2: sqlite3_result_text( pContext ,sqlite3_mprintf("doMPrintf 3") ,11 ,sqlite3_free ); break; default: sqlite3_result_null( pContext ); } return SQLITE_OK; } static int xRowid( sqlite3_vtab_cursor *pVTabCur ,sqlite_int64 *pRowID ) { *pRowID= ROWID; return SQLITE_OK; } static int xBestIndex( sqlite3_vtab *pVTable ,sqlite3_index_info *pIdxInfo ) { return SQLITE_OK; } static int xFilter( sqlite3_vtab_cursor *pVTabCur ,int pIdxNum ,const char *pIdxStr ,int pArgc ,sqlite3_value **ppArgv ) { return SQLITE_OK; } // End of SQLite3 Interface routines. // // A virtual table module that scan structure text file(s). // static sqlite3_module myVModule = { 1 // iVersion // Table instance functions. ,xCreate // xCreate - Required. Called when a virtual table instance is first created with the CREATE VIRTUAL TABLE command. ,xConnect // xConnect - Required, but frequently the same as xCreate(). This is called when a database with an existing virtual table instance is loaded. Called once for each table instance. ,xBestIndex // xBestIndex - Required. Called, sometimes several times, when the database engine is preparing an SQL statement that involves a virtual table. ,xDisconnect // xDisconnect - Required. Called when a database containing a virtual table instance is detached or closed. Called once for each table instance. ,xDestroy // xDestroy - Required, but frequently the same as xDisconnect(). This is called when a virtual table instance is destroyed with the DROP TABLE command. // Cursor functions. ,xOpen // xOpen - Required. Called to create and initialize a table cursor. ,xClose // xClose - Required. Called to shut down and release a table cursor. ,xFilter // xFilter - Required. Called to initiate a table scan and provide information about any specific conditions put on this particular table scan. ,xNext // xNext - Required. Called to advance a table cursor to the next row. ,xEof // xEof - Required. Called to see if a table cursor has reached the end of the table or not. This function is always called right after a call to xFilter() or xNext(). ,xColumn // xColumn - Required. Called to extract a column value for the current row. Normally called multiple times per row. ,xRowid // xRowid - Required. Called to extract the virtual ROWID of the current row. // ,0 // xUpdate // Transaction control functions. ,0 // xBegin ,0 // xSync ,0 // xCommit ,0 // xRollback // ,0 // xFindFunction ,xRename // xRename - Required. Called when a virtual table is renamed using the ALTER TABLE...RENAME command. ,0 // xSavepoint ,0 // xRelease ,0 // xRollbackTo }; // Extension load function entry point. // int myVirtualTable_init(sqlite3 *db ,char **ppzErrMsg ,const sqlite3_api_routines *pApi ) { int i; char *moduleName = "benchmark"; SQLITE_EXTENSION_INIT2( pApi ); return sqlite3_create_module( db ,moduleName ,&myVModule ,NULL ); } // Default entry point which SQLite3 will look for when this module is loaded. int sqlite3_extension_init( sqlite3 *db ,char **ppzErrMsg ,const sqlite3_api_routines *pApi ) { return myVirtualTable_init( db ,ppzErrMsg ,pApi ); } -----Original Message----- From: Warren Young <war...@etr-usa.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Wed, May 28, 2014 11:38 am Subject: Re: [sqlite] New DLLs and sources. Was: SQLite version 3.8.5 beta On 5/28/2014 12:26, Warren Young wrote: > On 5/28/2014 11:20, jose isaias cabrera wrote: >> >> I would rather have the speed >> then the size. > > in today's L1/L2/L3 world, size *is* speed. Also, there is a pretty hard limit on how much micro code optimizations can help a DBMS. It's a fundamentally I/O limited problem. Disk is many (4ish?) orders of magnitude slower than main RAM, and the CPU caches are orders of magnitude faster than that. http://www.eecs.berkeley.edu/~rcs/research/interactive_latency.html That is to say, if you made every code path in SQLite zero length, it would do approximately *squat* to improve the time it takes to get your query results. Only intelligent algorithms matter here, not micro-optimizations. Better indexes, smarter query planners, etc. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users