Arthur, For pre-compiled execution in SQLite, try using the sqlite_reset() function. It allows you to avoid having to re-compile your SQL every time you execute it. You can use sqlite_create_function() to do the equivalent of "parameterized" queries by creating a function which takes a parameter position and and returns the parameter value. I use this a lot in my code since I do a lot of inserts and want them to run fast without invoking the query compiler for each one.
-----Original Message----- From: Arthur C. Hsu [mailto:[EMAIL PROTECTED] Sent: Friday, November 14, 2003 3:50 AM To: 'Greg Obleshchuk'; [EMAIL PROTECTED] Subject: RE: [sqlite] Performance tuning question Hello Greg, The insert is not in transaction. I do the insert and update like CCriticalSection cs; void CDB::insert() { cs.Lock(); sqlite_exec_printf(pDB, "insert into db values(%d, %q, null, null, 0, null", 0, 0, 1, "A"); cs.Unlock(); } void CDB::update() { cs.Lock(); sqlite_exec_printf(pDB, "update db set str=%q where id=%d", 0, 0, "A", 1); cs.Unlock(); } As described in my previous mail, I have two threads that simultaneously accessing this in-mem DB. Thus I have to use locks to be sure of thread-safety. I keep pumping data into DB via insert from one thread. My sqlite version is 2.8.6 and I didn't compile it with -DTHREAD_SAFE=1. I have 1G bytes of memory in my development machine and it's hard to believe that I didn't have enough memory for a 50M database :) I use MDAC via .Net framework. I use ADO.Net in-memory table (System.Data.DataTable) and does not connect to any data source. BTW, I use Intel VTune and try to find out the bottleneck of my program. Execution of sqlite vbe is the most time-consuming (55%). However, yyparser of sqlite contributes 30% of load. I tend to believe this explains why the first 30 seconds the ADO.Net is faster than sqlite. SQL statements in ADO.Net always run precompiled, thus it saves time for parsing SQL commands. I'm trying to do precompiled execution in sqlite, and getting frustrated about that. The decaying phoenomenon is quite weird. My wild guesses are A. Issues of memory indexing: page index table grows bigger and bigger, thus indirection of memory tends to slow down B. SQLite try to do "safe" memory allocations, that is, malloc new memory block, memcpy old data, then free old memory block. These are just guesses. I'm trying to dig out why. Maybe you guys who are more familiar with sqlite internals can show me the answer :) Regards, Arthur --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
