Hi Jungle:
I tried you suggestion by upgrading to version 3.8.7.4 and the following is
the new benchmark.
Metric average run1 run2 run3 run4 run5
count(1) 2.6208 2.480 2.745 2.770 2.628 2.481
doNothing 5.6458 5.039 5.429 5.850 5.948 5.963
do1Malloc 13.6982 13.511 13.954 13.919 13.558 13.549
do1MPrint 33.5408 31.524 33.056 33.476 34.469 35.179
* numbers are in elapsed seconds.
The previous benchmark is:
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
With the latest benchmark using version 3.8.7.4, if looks like it a touch
slower. I may have to run it several more times. Either way, the mprint() on
my latest benchmark jumps out as being way slower. I just want to check-in if
anyone else out there encounter something similar. In the larger scheme of
things where the processing is I/O bound, this is miniscule. I am interested
in the embedded tablet space.
My environment is:
Edward Lau@T60p ~/myCprograms
$ gcc --version
gcc.exe (GCC) 4.8.1
$ gcc -shared -O2 -o benchmark.dll benchmark.c
Edward Lau@T60p ~/myCprograms
$ sqlite3
SQLite version 3.8.7.4 2014-12-09 01:34:36
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.dll
sqlite> create virtual table bmark using benchmark;
Can anyone out there try the benchmark in their environment? Here is 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 );
}
The following are my queries that ran many times:
select count(1) from bmark;
select max(doNothing) from bmark;
select max(do1SQLiteMalloc) from bmark;
select max(do1SQLiteMPrint) from bmark;
Thanks.
-----Original Message-----
From: jungle Boogie <[email protected]>
To: General Discussion of SQLite Database <[email protected]>
Sent: Sun, Dec 28, 2014 12:16 pm
Subject: Re: [sqlite] SQLite3 internal performance
Hi Edward,
On 25 December 2014 at 19:33, Edward Lau <[email protected]> wrote:
>
> 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
<snip>
> Edward Lau@T60p ~/myCprograms
> $ sqlite3
> SQLite version 3.8.4.3 2014-04-03 16:53:12
I don't have anything to add but perhaps a suggestion to try a more
recent version of sqlite to see if to makes a difference.
You may think 3.8.4 is not that old but look at this time line to
indicate how many modifications are made to source and site:
http://www.sqlite.org/docsrc/timeline
And there have been seven releases since 3.8.4.3.
Best,
jungle
--
-------
inum: 883510009027723
sip: [email protected]
xmpp: [email protected]
_______________________________________________
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
