Re: [sqlite] Performance tuning question

[Arthur Hsu]

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]