Arthur, sqlite_bind and sqlite_reset are part of an "experimental" API in the latest SQLite source code in CVS, but it isn't part of an official release yet. However, I use them, and they work very well.
Tim > -----Original Message----- > From: Arthur Hsu [mailto:[EMAIL PROTECTED] > Sent: Friday, November 14, 2003 2: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] > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
