RE: [sqlite] Performance tuning question
Say, are you testing inserts? Allan -Original Message- From: Arthur Hsu [mailto:[EMAIL PROTECTED] Sent: Friday, November 14, 2003 6:29 PM To: Andrew Shakinovsky; [EMAIL PROTECTED] Subject: Re: [sqlite] Performance tuning question Hello, I managed to download new CVS versions (by hand ...) and compile them on Win32 platform (finally). Following are my test results: Time elapsedADO.NetSQLite 2.8.6SQLite CVS 0 0 0 0 30 7419 57297920 60 14176 801310711 90 20760 986913147 120 26623 11033 14944 150 32862 12633 16598 180 38783 13044 17878 210 44472 13098 19609 240 49873 14120 20711 The CVS version is quite linear after the first 30 seconds. I'm still digging the reason for the performance difference between 0-30 and 30-60. Any ideas? -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]
Re: [sqlite] Performance tuning question
Hello, I managed to download new CVS versions (by hand ...) and compile them on Win32 platform (finally). Following are my test results: Time elapsedADO.NetSQLite 2.8.6SQLite CVS 0 0 0 0 30 7419 57297920 60 14176 801310711 90 20760 986913147 120 26623 11033 14944 150 32862 12633 16598 180 38783 13044 17878 210 44472 13098 19609 240 49873 14120 20711 The CVS version is quite linear after the first 30 seconds. I'm still digging the reason for the performance difference between 0-30 and 30-60. Any ideas? -Arthur - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Performance tuning question
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]
RE: [sqlite] Performance tuning question
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]
[sqlite] case sensitive select
Hello,
I know that this topic has been discussed many times but I'm about to change my
database structure to solve my pb so I
prefer to be sure that this is the good solution.
This is my table for the moment...
create table Test ( Key INTEGER PRIMARY KEY, Name VARCHAR(260) );
create index idx_testname on Test(Name);
insert into Test (Name) values ( 'FooD' );
As the select is case sensitive:
select Key from Test where name='FooD'; returns 1
select Key from Test where name='food'; returns null
select Key from Test where name='FOOD'; returns null
...
if I use a like comparator everything is ok
select Key from Test where name like 'FooD';returns 1
select Key from Test where name like 'food';returns 1
select Key from Test where name like 'FOOD';returns 1
But this select doesn't use the Index Table and is therefore a lot slower...
I have to keep the case sensitive string for further use...
So, for me, the only solution to keep speed will be to duplicate the name field in
idxName...
create table Test ( Key INTEGER PRIMARY KEY, idxName VARCHAR(260), Name VARCHAR(260)
);
create index idx_testname on Test(idxName);
insert into Test (idxname,Name) values ('food', 'FooD' );
and then
select Key from Test where idxname=lower('Food'); returns 1
select Key from Test where idxname=lower('food'); returns 1
select Key from Test where idxname=lower('FOOD'); returns 1
So it works, but is there any better solution ?
My goal is to have maximum speed...
Perhaps by using a shorter idxname ( idxName VARCHAR(60))
and a select in a select ?
Thx for any advice...
Regards
Barx
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
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]
RE: [sqlite] SQLite .NET Wrappers
My company will ship a commercial one in late Dec/ early Jan. It won't be open source but it will be managed C#. Thanks, Allan -Original Message- From: Luciano [mailto:[EMAIL PROTECTED] Sent: Friday, November 14, 2003 8:09 AM To: [EMAIL PROTECTED] Subject: [sqlite] SQLite .NET Wrappers Hi, all ! Is there any SQLite wrapper to .NET Compact Framework, in C#? Thanx for all! Luciano. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Removes hole's in ROWID
Hi, I have a table 'myData'. I insert 10 records into 'myData'. Now ROWID's = 1,2,3,4,5,6,7,8,9,10 I delete record where ROWID=5 in 'myData' table. Now ROWID's = 1,2,3,4,6,7,8,9,10 ROWID column have a 'hole' at 5. How I can reindex column ROWID, like this: ROWID's = 1,2,3,4,5,6,7,8,9 Thanks a lot, Alexey. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] SQLite .NET Wrappers
Hi, all ! Is there any SQLite wrapper to .NET Compact Framework, in C#? Thanx for all! Luciano.
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
-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]
