Re-posting due to message size too long.
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 run2run3run4run5
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 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_vtabbase;
} 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);
