SQLite In-Memory Database (VERY FAST )
Posted: Sun May 20, 2007 3:01 pm
The code below is basically the same as I posted before with a few very minor differences, first it uses an in-memory database instead of a file database, and second it writes a few thousand rows without a transaction to compare speed performance. The difference is impressive
It's fast enough that you could use this just because you wanted to use SQL statements on data that you have in an array or whatever, you could use SQLite as your array of structures and have access to the rich sql language to query it and get very good performance. It won't be as quick as arrays if your not doing much with them or just looping through them but if you have complex searches and queries to do it might be worth trying this to dump to an in-memory database and do some SQL queries.
Definately worth playing around with if you needs get a bit complex
Code: Select all
#SQLITE3_ISO8859 = 1 ;
#SQLITE3_OK = 0 ;'*/ Successful result */
#SQLITE3_ERROR = 1 ;'*/ SQL error or missing database */
#SQLITE3_INTERNAL = 2 ;'*/ An internal logic error in SQLite */
#SQLITE3_PERM = 3 ;'*/ Access permission denied */
#SQLITE3_ABORT = 4 ;'*/ Callback routine requested an abort */
#SQLITE3_BUSY = 5 ;'*/ The database file is locked */
#SQLITE3_LOCKED = 6 ;'*/ A table in the database is locked */
#SQLITE3_NOMEM = 7 ;'*/ A malloc() failed */
#SQLITE3_READONLY = 8 ;'*/ Attempt to write a readonly database */
#SQLITE3_INTERRUPT = 9 ;'*/ Operation terminated by sqlite3_interrupt() */
#SQLITE3_IOERR = 10 ;'*/ Some kind of disk I/O error occurred */
#SQLITE3_CORRUPT = 11 ;'*/ The database disk image is malformed */
#SQLITE3_NOTFOUND = 12 ;'*/ (Internal Only) Table or record not found */
#SQLITE3_FULL = 13 ;'*/ Insertion failed because database is full */
#SQLITE3_CANTOPEN = 14 ;'*/ Unable to open the database file */
#SQLITE3_PROTOCOL = 15 ;'*/ Database lock protocol error */
#SQLITE3_EMPTY = 16 ;'*/ (Internal Only) Database table is empty */
#SQLITE3_SCHEMA = 17 ;'*/ The database schema changed */
#SQLITE3_TOOBIG = 18 ;'*/ Too much data for one row of a table */
#SQLITE3_CONSTRAINT = 19 ;'*/ Abort due to contraint violation */
#SQLITE3_MISMATCH = 20 ;'*/ Data type mismatch */
#SQLITE3_MISUSE = 21 ;'*/ Library used incorrectly */
#SQLITE3_NOLFS = 22 ;'*/ Uses OS features not supported on host */
#SQLITE3_AUTH = 23 ;'*/ Authorization denied */
#SQLITE3_ROW = 100 ;'*/ sqlite3_step() has another row ready */
#SQLITE3_DONE = 101 ;'*/ sqlite3_step() has finished executing */
Global LastMessage.s
Global LibIsInitializedWithIncludedDLL.l
Global sqlite3_close.l
Global sqlite3_exec.l
Global sqlite3_open.l
Global sqlite3_errmsg.l
Global sqlite3DLL.l
Declare ProcMain()
Declare CallBack_ReturnRows(NullPtr.l,ArgCount.l,*Values.l, *ColNames.l)
; ==============================================================================
; Program Entry Point
ProcMain()
; ==============================================================================
Procedure.l ProcMain()
SQLiteDLLPath.s = "sqlite3.dll"
DataBaseFile.s = ":memory:" ;"test0001.dat" ;
;Load SQLite DLL
sqlite3DLL = LoadLibrary_(@SQLiteDLLPath)
sqlite3_close = GetProcAddress_(sqlite3DLL, "sqlite3_close")
sqlite3_exec = GetProcAddress_(sqlite3DLL, "sqlite3_exec")
sqlite3_open = GetProcAddress_(sqlite3DLL, "sqlite3_open")
sqlite3_errmsg = GetProcAddress_(sqlite3DLL, "sqlite3_errmsg")
;Prep SQL statements
CreateDB.s = "CREATE TABLE 'MyTable' (" + #CRLF$
CreateDB = CreateDB + "'ID' INTEGER Not NULL PRIMARY KEY AUTOINCREMENT," + #CRLF$
CreateDB = CreateDB + "'StringCol1' TEXT NULL," + #CRLF$
CreateDB = CreateDB + "'StringCol2' TEXT NULL," + #CRLF$
CreateDB = CreateDB + "'IntCol1' INTEGER NULL," + #CRLF$
CreateDB = CreateDB + "'IntCol2' INTEGER NULL" + #CRLF$
CreateDB = CreateDB + ")"
CreateDB = ReplaceString(CreateDB, "'", #DOUBLEQUOTE$)
SQLAddRow.s = "Insert into MyTable(StringCol1, StringCol2, IntCol1, IntCol2) values('Hello', 'NextColumn', 100, 12345678) "
CurrentTime.f = ElapsedMilliseconds()
;Open Database
If CallCFunctionFast(sqlite3_open, DataBaseFile, @dbHandle)
CallCFunctionFast(sqlite3_errmsg, @RetVal)
Debug = PeekS(RetVal)
Else
; If successful send queries
; Send Created DB SQL, table may exist
QueryRet.l = CallCFunctionFast(sqlite3_exec, DBHandle, CreateDB, @CallBack_ReturnRows() , #Null, @ReturnValue)
If QueryRet
CallCFunctionFast(sqlite3_errmsg, @ReturnValue)
Debug = PeekS(ReturnValue)
EndIf
; Send Insert Row SQL, table may exist
For i.l = 1 To 5000
QueryRet.l = CallCFunctionFast(sqlite3_exec, DBHandle, SQLAddRow, @CallBack_ReturnRows() , #Null, @ReturnValue)
If QueryRet
CallCFunctionFast(sqlite3_errmsg, @ReturnValue)
Debug = PeekS(ReturnValue)
EndIf
Next
;Send arbitrary Query
QueryRet.l = CallCFunctionFast(sqlite3_exec, DBHandle, "Select * from MyTable", @CallBack_ReturnRows() , #Null, @ReturnValue)
If QueryRet
CallCFunctionFast(sqlite3_errmsg, @ReturnValue)
Debug = PeekS(ReturnValue)
EndIf
;Close Database
CallCFunctionFast(sqlite3_close, DBHandle)
EndIf
Debug ElapsedMilliseconds() - CurrentTime
;Unload DLL on program end
FreeLibrary_(sqlite3DLL)
EndProcedure
;==============================================================================
ProcedureC.l CallBack_ReturnRows(NullPtr.l,ArgCount.l,*Values.l, *ColNames.l) ;argcount = column count
Dim ValuePts.l(ArgCount)
Row.s = ""
For ValLoop.l = 0 To ArgCount -1
ValuePts(ValLoop) = PeekL(*Values + (ValLoop * 4))
Row = Row + PeekS(ValuePts(ValLoop) ) + ","
Next
Debug Row
ProcedureReturn = 0 ;Proceed with next row
EndProcedure
;======================================================================================
Another point to note, since the PBOSL version of SQLite does a file check before creating or opening a database...
