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 <[email protected]>
To: General Discussion of SQLite Database <[email protected]>
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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
