Hi Folks: I have read in many posting that SQLite is fast. How fast is fast? So over this holiday break I tried to take a reading on the VDBE performance to get a general idea. I wrote a quick module to enable me to exercise the engine simulating 32M rows without any IOs. For each iteration I just increment a counter and each call to xColumn() I pass the value of the counter back. Starting from a baseline, I notice the time increase dramatically depending what i am doing. The performance is still fast in terms of elapsed time but if measured by percentage degradation; it jumps out. Here is the formatted results from the logs below: Metric average run1 run2 run3 run4 run5 count(1) 2.4226 2.406 2.446 2.420 2.419 2.422 doNothing 5.1988 5.196 5.214 5.162 5.218 5.204 do1Malloc 10.7040 10.678 10.666 10.755 10.710 10.711 do1MPrint 23.5468 23.727 23.464 23.502 23.558 23.483 * numbers are in elapsed seconds.
METRIC DESCRIPTION: a) count(1) : count 33,554,432 rows from the simulation to get a baseline using virtual table. b) doNothing: xColumn() is called and returned the counter value to obtain the max value. c) do1Malloc: xColumn() is called and returned the counter value with sqlite3_malloc() and sqlite3_free() is invoked. d) do1Mprint: xColumn() is called and returned the counter value as text formatted by sqlite3_mprint(). The calling routine shall free the memory allocated in this call. >From my laptop (Lenovo T60p) the following is what I observed. - Invoking xColumn() is about twice (5.1988 / 2.4226) slower than the baseline count(1). - Invoking a single malloc() and free() is about twice (10.7040 / 5.1988) slower than the same call without memory allocation and freeing of it. - Using mprintf() is about twice (23.5468/10.7040) slower than not using mprintf(). I understand that there is always a cost to do work but in my environment I am seeing a high cost of 2X degradation. Can someone try out my experiment in their environment? The source is at the bottom. In a more typical implementation, one could use sqlite3_malloc() more than once. With each call we could be paying a high price especially in the embedded device environment. Should the SQLite API provide allocated memory to minimize these calls? Furthermore, should there be a new virtual table API that works with batches of rows i.e. xNextBatch()? sqlite3_mprintf() is a very nice utility but there is a cost for this convenience. This could be a performance trap for novice like me that was allured by its convenience. We should pay more attention to performance for methods that are called in a tight iteration loop like xNext() and xColumn(). Maybe I am doing something wrong or my expectation is not in line. As modern server CPU are fast, one may not care too much but how about low Gigahertz ARM processor for cell phone or embedded devices? If the processing is dominated by IOs this is miniscule by comparison. As we transition to SSD the IO latency drops and bottlneck shift back to code. Comments and suggestions? Merry Christmas everyone. THE RAW LOGS: ============= Edward Lau@T60p ~/myCprograms $ gcc --version gcc.exe (GCC) 4.8.1 Copyright (C) 2013 Free Software Foundation, Inc. This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. Edward Lau@T60p ~/myCprograms $ gcc -shared -O2 -o benchmark.dll benchmark.c Edward Lau@T60p ~/myCprograms $ 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> .header on sqlite> .timer on sqlite> .load benchmark.dll sqlite> create virtual table bmark using benchmark; The follow are the columns for the virtual table: doNothing INT, do1SQLiteMalloc INT, do1SQLiteMPrint TEXT Run Time: real 0.031 user 0.000000 sys 0.000000 sqlite> sqlite> select count(1) recCount FROM bmark; xOpen SQLite3 Memory used: -1224729856 xClose SQLite3 Memory used: -1224729864 recCount 33554432 Run Time: real 2.406 user 2.402415 sys 0.000000 sqlite> select count(1) recCount FROM bmark; xOpen SQLite3 Memory used: -1224729856 xClose SQLite3 Memory used: -1224729864 recCount 33554432 Run Time: real 2.446 user 2.418016 sys 0.000000 sqlite> SELECT count(1) recCount FROM bmark; xOpen SQLite3 Memory used: -1224729856 xClose SQLite3 Memory used: -1224729864 recCount 33554432 Run Time: real 2.420 user 2.418016 sys 0.000000 sqlite> SELECT count(1) recCount FROM bmark; xOpen SQLite3 Memory used: -1224729856 xClose SQLite3 Memory used: -1224729864 recCount 33554432 Run Time: real 2.419 user 2.386815 sys 0.000000 sqlite> SELECT count(1) recCount from bmark; xOpen SQLite3 Memory used: -1224729856 xClose SQLite3 Memory used: -1224729864 recCount 33554432 Run Time: real 2.422 user 2.433616 sys 0.000000 sqlite> SELECT MAX(doNothing) maxDoNothing FROM bmark; xOpen SQLite3 Memory used: -1224729856 xClose SQLite3 Memory used: -1224729864 maxDoNothing 33554431 Run Time: real 5.196 user 5.163633 sys 0.000000 sqlite> SELECT MAX(doNothing) maxDoNothing FROM bmark; xOpen SQLite3 Memory used: -1224729856 xClose SQLite3 Memory used: -1224729864 maxDoNothing 33554431 Run Time: real 5.214 user 5.179233 sys 0.000000 sqlite> SELECT MAX(doNothing) maxDoNothing FROM bmark; xOpen SQLite3 Memory used: -1224729856 xClose SQLite3 Memory used: -1224729864 maxDoNothing 33554431 Run Time: real 5.162 user 5.163633 sys 0.000000 sqlite> SELECT MAX(doNothing) maxDoNothing FROM bmark; xOpen SQLite3 Memory used: -1224729856 xClose SQLite3 Memory used: -1224729864 maxDoNothing 33554431 Run Time: real 5.218 user 5.194833 sys 0.015600 sqlite> SELECT MAX(doNothing) maxDoNothing FROM bmark; xOpen SQLite3 Memory used: -1224729856 xClose SQLite3 Memory used: -1224729864 maxDoNothing 33554431 Run Time: real 5.204 user 5.194833 sys 0.000000 sqlite> sqlite> SELECT MAX(do1SQLiteMalloc) maxDo1SQLiteMalloc FROM bmark; xOpen SQLite3 Memory used: -1224729856 xClose SQLite3 Memory used: -1224729864 maxDo1SQLiteMalloc 33554431 Run Time: real 10.678 user 10.623668 sys 0.000000 sqlite> SELECT MAX(do1SQLiteMalloc) maxDo1SQLiteMalloc FROM bmark; xOpen SQLite3 Memory used: -1224729856 xClose SQLite3 Memory used: -1224729864 maxDo1SQLiteMalloc 33554431 Run Time: real 10.666 user 10.608068 sys 0.000000 sqlite> SELECT MAX(do1SQLiteMalloc) maxDo1SQLiteMalloc FROM bmark; xOpen SQLite3 Memory used: -1224729856 xClose SQLite3 Memory used: -1224729864 maxDo1SQLiteMalloc 33554431 Run Time: real 10.755 user 10.748469 sys 0.000000 sqlite> SELECT MAX(do1SQLiteMalloc) maxDo1SQLiteMalloc FROM bmark; xOpen SQLite3 Memory used: -1224729856 xClose SQLite3 Memory used: -1224729864 maxDo1SQLiteMalloc 33554431 Run Time: real 10.710 user 10.701669 sys 0.000000 sqlite> SELECT MAX(do1SQLiteMalloc) maxDo1SQLiteMalloc FROM bmark; xOpen SQLite3 Memory used: -1224729856 xClose SQLite3 Memory used: -1224729864 maxDo1SQLiteMalloc 33554431 Run Time: real 10.711 user 10.654868 sys 0.000000 sqlite> sqlite> SELECT MAX(do1SQLiteMPrint) maxDo1SQLiteMPrint FROM bmark; xOpen SQLite3 Memory used: -1224729856 xClose SQLite3 Memory used: -1224729832 maxDo1SQLiteMPrint 0000000000000000000000033554431 Run Time: real 23.727 user 23.587351 sys 0.000000 sqlite> SELECT MAX(do1SQLiteMPrint) maxDo1SQLiteMPrint FROM bmark; xOpen SQLite3 Memory used: -1224729856 xClose SQLite3 Memory used: -1224729832 maxDo1SQLiteMPrint 0000000000000000000000033554431 Run Time: real 23.464 user 23.431350 sys 0.000000 sqlite> SELECT MAX(do1SQLiteMPrint) maxDo1SQLiteMPrint FROM bmark; xOpen SQLite3 Memory used: -1224729856 xClose SQLite3 Memory used: -1224729832 maxDo1SQLiteMPrint 0000000000000000000000033554431 Run Time: real 23.502 user 23.368950 sys 0.000000 sqlite> SELECT MAX(do1SQLiteMPrint) maxDo1SQLiteMPrint FROM bmark; xOpen SQLite3 Memory used: -1224729856 xClose SQLite3 Memory used: -1224729832 maxDo1SQLiteMPrint 0000000000000000000000033554431 Run Time: real 23.558 user 23.462550 sys 0.000000 sqlite> SELECT MAX(do1SQLiteMPrint) maxDo1SQLiteMPrint FROM bmark; xOpen SQLite3 Memory used: -1224729856 xClose SQLite3 Memory used: -1224729832 maxDo1SQLiteMPrint 0000000000000000000000033554431 Run Time: real 23.483 user 23.415750 sys 0.000000 sqlite> THE C SOURCE: ============= #include <fcntl.h> #include <stdio.h> #include "sqlite3ext.h" SQLITE_EXTENSION_INIT1 // Required by SQLite extension header. #ifndef traceAPI #ifdef DEBUG #define traceAPI(str) fprintf(stderr,"%s\n",str); #else #define traceAPI(str) #endif #endif 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 ) { traceAPI("In xConnect() ..."); myVTable *myVTab; *pzErrMsg = NULL; myVTab = sqlite3_malloc(sizeof(myVTable) ); *ppVTable =(sqlite3_vtab *)myVTab; return SQLITE_OK; } static int xDisconnect( sqlite3_vtab *pVTable ) { traceAPI("In xDisconnect() ..."); 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 ) { traceAPI("In xCreate() ..."); int rc = SQLITE_OK; rc = xConnect( db ,pAux ,pArgc ,pArgv ,ppVTable ,pzErrMsg ); if(SQLITE_OK == rc ) { rc = sqlite3_declare_vtab( db ,"CREATE TABLE _tablename( doNothing INT ,do1SQLiteMalloc INT ,do1SQLiteMPrint TEXT )" ); if(SQLITE_OK != rc ) *pzErrMsg = sqlite3_mprintf("Unable to declare virtual table!"); else{ fprintf(stderr,"The follow are the columns for the virtual table:\n"); fprintf(stderr,"\tdoNothing INT,\n\tdo1SQLiteMalloc INT,\n\tdo1SQLiteMPrint TEXT\n"); } } return rc; } static int xRename( sqlite3_vtab *pVTable ,const char *pzNewName ) { traceAPI("In xRename() ..."); return SQLITE_OK; } static int xDestroy( sqlite3_vtab *pVTable ) { traceAPI("In xDestroy() ..."); myVTable *myVTab= (myVTable *)pVTable; sqlite3_free( myVTab ); return SQLITE_OK; } static int xOpen( sqlite3_vtab *pVTable ,sqlite3_vtab_cursor **ppVTabCur ) { traceAPI("In xOpen() ..."); myVCursor *myVCur; myVCur = sqlite3_malloc(sizeof(myVCursor)); *ppVTabCur=(sqlite3_vtab_cursor *)myVCur; ROWID=0; fprintf( stderr ,"xOpen SQLite3 Memory used:\t%lld\n" ,sqlite3_memory_used()); return SQLITE_OK; } static int xClose( sqlite3_vtab_cursor *pVTabCur ) { traceAPI("In xClose() ..."); myVCursor *myVCur = (myVCursor *)pVTabCur; sqlite3_free( myVCur ); fprintf( stderr ,"xClose SQLite3 Memory used:\t%lld\n" ,sqlite3_memory_used()); return SQLITE_OK; } static int xEof( sqlite3_vtab_cursor *pVTabCur ) { return (ROWID >= MAX_ITERATION); } static int xNext( sqlite3_vtab_cursor *pVTabCur ) { traceAPI("In xNext() ..."); ROWID++; return SQLITE_OK; } static int xColumn( sqlite3_vtab_cursor *pVTabCur ,sqlite3_context *pContext ,int pColID ) { char *someHeapMem; switch( pColID ) { case -1: sqlite3_result_int64(pContext ,ROWID ); break; case 0: sqlite3_result_int64(pContext ,ROWID ); break; case 1: sqlite3_result_int64(pContext ,ROWID ); someHeapMem = sqlite3_malloc( 128 ); sqlite3_free( someHeapMem ); break; case 2: someHeapMem = sqlite3_mprintf("%031d" ,ROWID ); sqlite3_result_text( pContext ,someHeapMem ,32 ,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 ) { traceAPI("In xBestIndex() ..."); return SQLITE_OK; } static int xFilter( sqlite3_vtab_cursor *pVTabCur ,int pIdxNum ,const char *pIdxStr ,int pArgc ,sqlite3_value **ppArgv ) { traceAPI("In xFilter() ..."); 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 ); } _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users