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
-----Original Message-----
From: Greg Obleshchuk [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 13, 2003 8:43 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Performance tuning question
Hi Arthur,
It's very quite here , for some reason (has been all week). Can you tell me
a bit more about you inserts are they in separate batches? or one big batch.
I have noticed performance issues with in-memory DB when doing lots on
little batches , but I would have thought it would be constant.
How much memory does you computer have? This may effect performance with
swapping. With MDAC which database type are you going to?
regards
Greg O
----- Original Message -----
From: Arthur Hsu
To: [EMAIL PROTECTED]
Sent: Friday, November 14, 2003 11:58 AM
Subject: [sqlite] Performance tuning question
Hello,
I'm new to sqlite and I'm using it in my MFC program. I compiled the
sqlite
lib myself. I'm using in-memory DB. My program has two threads, say
thread
A and B. Thread A is the producer, which inserts rows inside a table
named
'master'. Thread B is a consumer, which selects and updates rows from the
table 'master'. The SCUD operations are embedded into a singleton, which
is
responsible for critical section locks. The master table has a primary
key
composed of two columns, one is integer type and the other is string type.
What confuses me is a strange phoenomenon. The first 30 seconds thread A
can insert 6000 rows, and the next 30 seconds another 2000, and next 30
seconds 1900, 1800, ... steadily decaying. Meanwhile thread B keeps the
same pace to make 2 updates per 30 seconds. I've tried to tune the
MAX_PAGES to 1048576 and MAX_BYTES_PER_ROW to 17646. It helps boosting up
some performance but the phoenomenon of decaying is still there.
I have a rival program which has same functionality but it's written in
MDAC
(I can't use MDAC for some reasons). The first 30 seconds 8500 rows are
inserted, and the next is 17000 rows, 26500, ..., almost linear.
Where should I look into to tune my SQLite compilation in order that I can
match the MDAC version performance? Any cent is much appreciated.
TIA,
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]
