The sqlite_bind() function is brand new as far as I can tell from looking at CVS. You would have to get the latest sources. It looks like it would also be simpler to use sqlite_bind() than to use the way I am doing it which is to use sqlite_create_function() and call the function in my sql to get parameter values.
>From what I can tell, the sqlite_bind() will allow you to just set your parm values between calls to sqlite_step and sqlite_reset. -----Original Message----- From: Arthur Hsu [mailto:[EMAIL PROTECTED] Sent: Friday, November 14, 2003 3:04 PM To: Andrew Shakinovsky; [EMAIL PROTECTED] Subject: Re: [sqlite] Performance tuning question Hello Andrew, I'm a little confused about the precompiled SQL. According to Wiki, there should be something like sqlite_bind() that I can use. However, I can't find sqlite_bind() in my sqlite source ... I'll try the sqlite_create_function(). Thanks. Regards, Arthur ----- Original Message ----- From: "Andrew Shakinovsky" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, November 14, 2003 7:24 AM Subject: RE: [sqlite] Performance tuning question > 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] > > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
