Re: [sqlite] In Mem Query Performance
--- RaghavendraK 70574 <[EMAIL PROTECTED]> wrote: > I have configured the sqlite in the following mode (see below), and > performance > marginally improved from 2500 records/sec to 8000/sec. 1.05 times faster is marginal. Three times faster is quite good. Try compiling sqlite library using the sqlite3.c amalgamation: http://www.sqlite.org/cvstrac/wiki?p=TheAmalgamation with these flags: -O3 -fomit-frame-pointer > Pls help,if anything else need to be done to get at least 3records/sec > with each column being 128Bytes,10 column table,1 records. Make sure you're using sqlite 3.4.0 and run VACUUM and ANALYZE on the database prior to querying. A 30M database (with indexes I'm guessing) is tiny. Something may be wrong with your schema, index, or key column design. Make sure your database file and temp store is on a local disk and not on the network. Post a typical SELECT statement and the output of EXPLAIN QUERY PLAN SELECT ... Park yourself in front of a world of choices in alternative vehicles. Visit the Yahoo! Auto Green Center. http://autos.yahoo.com/green_center/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] In Mem Query Performance
Hi Joe,
I have configured the sqlite in the following mode (see below), and performance
marginally improved from 2500 records/sec to 8000/sec.
Pls help,if anything else need to be done to get at least 3records/sec
with each column being 128Bytes,10 column table,1 records.
regards
ragha
Compile Options:
./configure --enable-threadsafe
Makefile:
#BCC = gcc -g -O2
BCC = gcc -O2
# C Compile and options for use in building executables that
# will run on the target platform. (BCC and TCC are usually the
# same unless your are cross-compiling.)
#
#TCC = gcc -g -O2 -I. -I${TOP}/src
TCC = gcc -O2 -I. -I${TOP}/src
# Define -DNDEBUG to compile without debugging (i.e., for production usage)
# Omitting the define will cause extra debugging code to be inserted and
# includes extra comments when "EXPLAIN stmt" is used.
#
TCC += -DNDEBUG=1 -DSQLITE_DEFAULT_CACHE_SIZE=32768
-DSQLITE_DEFAULT_PAGE_SIZE=8192 -DSQLITE_DEFAULT_TEMP_CACHE_SIZE=32768
-DTEMP_STORE=3
**
This email and its attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed above.
Any use of the information contained herein in any way (including, but not
limited to, total or partial disclosure, reproduction, or dissemination) by
persons other than the intended recipient(s) is prohibited. If you receive this
e-mail in error, please notify the sender by phone or email immediately and
delete it!
*
- Original Message -
From: RaghavendraK 70574 <[EMAIL PROTECTED]>
Date: Tuesday, June 26, 2007 10:30 pm
Subject: Re: [sqlite] In Mem Query Performance
> Ok.
> Will notify u once i complete the test.
>
> regards
> ragha
>
> **
> This email and its attachments contain confidential information
> from HUAWEI, which is intended only for the person or entity whose
> address is listed above. Any use of the information contained
> herein in any way (including, but not limited to, total or partial
> disclosure, reproduction, or dissemination) by persons other than
> the intended recipient(s) is prohibited. If you receive this e-mail
> in error, please notify the sender by phone or email immediately
> and delete it!
>
> *
> - Original Message -
> From: Joe Wilson <[EMAIL PROTECTED]>
> Date: Wednesday, June 27, 2007 0:48 am
> Subject: Re: [sqlite] In Mem Query Performance
>
> > :memory: databases only have a page size of 1024.
> >
> > Try various page_size settings for a file based database file and
> > see what happens.
> >
> > I have no other suggestions.
> >
> > --- RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> > > It is a server platform,Linux SuSE9 enterpraise edition.
> > > 4 CPU machine,8GB ram.
> > > We want load all the tables in to mem db of Sqlite.Achieve
> > > read performance of upto 5records/sec for the table data i
> > had mentioned earlier.
> > >
> > > "so it would have to be file based."
> > > I could not get it. Does it mean even increasing the page size
> > there would be
> > > no effect on the performance?
> >
> >
> >
> >
> >
> >
> It's here! Your new message!
> > Get new email alerts with the free Yahoo! Toolbar.
> > http://tools.search.yahoo.com/toolbar/features/mail/
> >
> > --
> -
> > --
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > --
> -
> > --
> >
> >
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] In Mem Query Performance
Ok. Will notify u once i complete the test. regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - Original Message - From: Joe Wilson <[EMAIL PROTECTED]> Date: Wednesday, June 27, 2007 0:48 am Subject: Re: [sqlite] In Mem Query Performance > :memory: databases only have a page size of 1024. > > Try various page_size settings for a file based database file and > see what happens. > > I have no other suggestions. > > --- RaghavendraK 70574 <[EMAIL PROTECTED]> wrote: > > It is a server platform,Linux SuSE9 enterpraise edition. > > 4 CPU machine,8GB ram. > > We want load all the tables in to mem db of Sqlite.Achieve > > read performance of upto 5records/sec for the table data i > had mentioned earlier. > > > > "so it would have to be file based." > > I could not get it. Does it mean even increasing the page size > there would be > > no effect on the performance? > > > > > > It's here! Your new message! > Get new email alerts with the free Yahoo! Toolbar. > http://tools.search.yahoo.com/toolbar/features/mail/ > > --- > -- > To unsubscribe, send email to [EMAIL PROTECTED] > --- > -- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] In Mem Query Performance
:memory: databases only have a page size of 1024. Try various page_size settings for a file based database file and see what happens. I have no other suggestions. --- RaghavendraK 70574 <[EMAIL PROTECTED]> wrote: > It is a server platform,Linux SuSE9 enterpraise edition. > 4 CPU machine,8GB ram. > We want load all the tables in to mem db of Sqlite.Achieve > read performance of upto 5records/sec for the table data i had mentioned > earlier. > > "so it would have to be file based." > I could not get it. Does it mean even increasing the page size there would be > no effect on the performance? It's here! Your new message! Get new email alerts with the free Yahoo! Toolbar. http://tools.search.yahoo.com/toolbar/features/mail/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] In Mem Query Performance
Hello Joe,
It is a server platform,Linux SuSE9 enterpraise edition.
4 CPU machine,8GB ram.
We want load all the tables in to mem db of Sqlite.Achieve
read performance of upto 5records/sec for the table data i had mentioned
earlier.
"so it would have to be file based."
I could not get it. Does it mean even increasing the page size there would be
no effect on the performance?
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed above.
Any use of the information contained herein in any way (including, but not
limited to, total or partial disclosure, reproduction, or dissemination) by
persons other than the intended recipient(s) is prohibited. If you receive this
e-mail in error, please notify the sender by phone or email immediately and
delete it!
*
- Original Message -
From: Joe Wilson <[EMAIL PROTECTED]>
Date: Tuesday, June 26, 2007 11:57 pm
Subject: Re: [sqlite] In Mem Query Performance
> --- RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> > Your input is valuable. I will increase the pg size to 4KB and
> check.
> :memory: databases only use 1024 byte pages if I remember
> correctly,
> so it would have to be file based.
>
> > We just have a set of tables which is to be read on startup.No
> complex Query is involved.
> > I find Sqlite to be most powerful given the size and complexity
> it handles.
> >
> > I use the following apis to create the
> >
> > int ret = sqlite3_open(":memory:",_sqliteDb1);
> > also use for all temp tables,
> > pragma PRAGMA temp_store = MEMORY
>
> That looks fine. It ought to be fast. Although "fast" is a
> relative term.
>
> Is this an embedded platform or a PC that you're using?
>
>
>
>
> Sick sense of humor? Visit Yahoo! TV's
> Comedy with an Edge to see what's on, when.
> http://tv.yahoo.com/collections/222
>
> ---
> --
> To unsubscribe, send email to [EMAIL PROTECTED]
> ---
> --
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] In Mem Query Performance
--- RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> Your input is valuable. I will increase the pg size to 4KB and check.
:memory: databases only use 1024 byte pages if I remember correctly,
so it would have to be file based.
> We just have a set of tables which is to be read on startup.No complex Query
> is involved.
> I find Sqlite to be most powerful given the size and complexity it handles.
>
> I use the following apis to create the
>
> int ret = sqlite3_open(":memory:",_sqliteDb1);
> also use for all temp tables,
> pragma PRAGMA temp_store = MEMORY
That looks fine. It ought to be fast. Although "fast" is a
relative term.
Is this an embedded platform or a PC that you're using?
Sick sense of humor? Visit Yahoo! TV's
Comedy with an Edge to see what's on, when.
http://tv.yahoo.com/collections/222
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] In Mem Query Performance
Hi Joe,
Your input is valuable. I will increase the pg size to 4KB and check.
We just have a set of tables which is to be read on startup.No complex Query is
involved.
I find Sqlite to be most powerful given the size and complexity it handles.
I use the following apis to create the
int ret = sqlite3_open(":memory:",_sqliteDb1);
also use for all temp tables,
pragma PRAGMA temp_store = MEMORY
PLs let me know if this is correct.
regrads
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed above.
Any use of the information contained herein in any way (including, but not
limited to, total or partial disclosure, reproduction, or dissemination) by
persons other than the intended recipient(s) is prohibited. If you receive this
e-mail in error, please notify the sender by phone or email immediately and
delete it!
*
- Original Message -
From: Joe Wilson <[EMAIL PROTECTED]>
Date: Tuesday, June 26, 2007 10:58 pm
Subject: Re: [sqlite] In Mem Query Performance
> --- RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> > Thanks for the suggestion. But with that performance went down
> by 25% further.
> > Pls suggest an alternative. Mr DRH says it is possible we can
> reach up to a million,if there is
> > a way pls notify.
> ...
> > We are using Sqlite in "in Memory Mode" and we have around 200
> tables.> Each table has 10 columns of type text.
> > Each table has around 1 records each column has around
> 128bytes data.
> > Select performance is around 2000records/sec. Pls suggest if
> there is a way
> > to improve further.
> >
> > Table structure,Query style is as below,
> >
> > create table test1 ...200
> > (
> > key0 text,
> > key1 text,
> > key2 text,
> > key3 text,
> > key4 text,
> > nonKey0 text,
> > nonKey1 text,
> > nonKey2 text,
> > nonKey3 text,
> > nonKey4 text,
> > primary key(key0,key1,key2,key3,key4,key5)
> > );
> >
> > Query Used..
> >
> > SELECT * FROM TABLE136 WHERE
> >
> key0='kk>
> kk490' AND
> >
> key1='kk>
> kk491' AND
> >
> key2='kk>
> kk492' AND
> >
> key3='kk>
> kk493' AND
> >
> key4='kk>
> kk494'
>
> If your tables have 10 columns of 128 bytes each, then each table
> row is
> over 1280 bytes, which exceeds a memory page size (1024), so
> overflow
> pages are used. You might try a file-based database with a bigger
> page_size,say 8192.
>
> Judging by you example queries, your keys vary only after the
> 120th byte
> or so. That may play a role in the lack of speed. Try putting the
> differentiating characters first in the key strings.
>
> Are your slow query really only looking at a single table, or do
> they do
> a multiple table joins?
>
> How do you create your memory database?
> Maybe you're not making a memory database as you think you are.
>
>
>
>
>
> Be a better Globetrotter. Get better travel answers from someone
> who knows. Yahoo! Answers - Check it out.
> http://answers.yahoo.com/dir/?link=list=396545469
>
> ---
> --
> To unsubscribe, send email to [EMAIL PROTECTED]
> ---
> --
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] In Mem Query Performance
--- RaghavendraK 70574 <[EMAIL PROTECTED]> wrote: > Thanks for the suggestion. But with that performance went down by 25% further. > Pls suggest an alternative. Mr DRH says it is possible we can reach up to a > million,if there is > a way pls notify. ... > We are using Sqlite in "in Memory Mode" and we have around 200 tables. > Each table has 10 columns of type text. > Each table has around 1 records each column has around 128bytes data. > Select performance is around 2000records/sec. Pls suggest if there is a way > to improve further. > > Table structure,Query style is as below, > > create table test1 ...200 > ( > key0 text, > key1 text, > key2 text, > key3 text, > key4 text, > nonKey0 text, > nonKey1 text, > nonKey2 text, > nonKey3 text, > nonKey4 text, > primary key(key0,key1,key2,key3,key4,key5) > ); > > Query Used.. > > SELECT * FROM TABLE136 WHERE > key0='kk > kk490' AND > key1='kk > kk491' AND > key2='kk > kk492' AND > key3='kk > kk493' AND > key4='kk > kk494' If your tables have 10 columns of 128 bytes each, then each table row is over 1280 bytes, which exceeds a memory page size (1024), so overflow pages are used. You might try a file-based database with a bigger page_size, say 8192. Judging by you example queries, your keys vary only after the 120th byte or so. That may play a role in the lack of speed. Try putting the differentiating characters first in the key strings. Are your slow query really only looking at a single table, or do they do a multiple table joins? How do you create your memory database? Maybe you're not making a memory database as you think you are. Be a better Globetrotter. Get better travel answers from someone who knows. Yahoo! Answers - Check it out. http://answers.yahoo.com/dir/?link=list=396545469 - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] In Mem Query Performance
Hi, Thanks for the suggestion. But with that performance went down by 25% further. Pls suggest an alternative. Mr DRH says it is possible we can reach up to a million,if there is a way pls notify. regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * --- Begin Message --- Don't use sqlite_get_table. Use sqlite3_prepare(_v2), sqlite3_step and sqlite3_finalize/sqlite3_reset. Mike -Ursprüngliche Nachricht- Von: RaghavendraK 70574 [mailto:[EMAIL PROTECTED] Gesendet: Montag, 25. Juni 2007 13:48 An: sqlite-users@sqlite.org Betreff: [sqlite] In Mem Query Performance Hi, We are using Sqlite in "in Memory Mode" and we have around 200 tables. Each table has 10 columns of type text. Each table has around 1 records each column has around 128bytes data. Select performance is around 2000records/sec. Pls suggest if there is a way to improve further. Table structure,Query style is as below, create table test1 ...200 ( key0 text, key1 text, key2 text, key3 text, key4 text, nonKey0 text, nonKey1 text, nonKey2 text, nonKey3 text, nonKey4 text, primary key(key0,key1,key2,key3,key4,key5) ); Query Used.. SELECT * FROM TABLE136 WHERE key0='kk kk490' AND key1='kk kk491' AND key2='kk kk492' AND key3='kk kk493' AND key4='kk kk494' API used sqlite_get_table... regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - --- End Message --- - To unsubscribe, send email to [EMAIL PROTECTED] -
AW: [sqlite] In Mem Query Performance
Don't use sqlite_get_table. Use sqlite3_prepare(_v2), sqlite3_step and sqlite3_finalize/sqlite3_reset. Mike -Ursprüngliche Nachricht- Von: RaghavendraK 70574 [mailto:[EMAIL PROTECTED] Gesendet: Montag, 25. Juni 2007 13:48 An: sqlite-users@sqlite.org Betreff: [sqlite] In Mem Query Performance Hi, We are using Sqlite in "in Memory Mode" and we have around 200 tables. Each table has 10 columns of type text. Each table has around 1 records each column has around 128bytes data. Select performance is around 2000records/sec. Pls suggest if there is a way to improve further. Table structure,Query style is as below, create table test1 ...200 ( key0 text, key1 text, key2 text, key3 text, key4 text, nonKey0 text, nonKey1 text, nonKey2 text, nonKey3 text, nonKey4 text, primary key(key0,key1,key2,key3,key4,key5) ); Query Used.. SELECT * FROM TABLE136 WHERE key0='kk kk490' AND key1='kk kk491' AND key2='kk kk492' AND key3='kk kk493' AND key4='kk kk494' API used sqlite_get_table... regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] In Mem Query Performance
Hi, We are using Sqlite in "in Memory Mode" and we have around 200 tables. Each table has 10 columns of type text. Each table has around 1 records each column has around 128bytes data. Select performance is around 2000records/sec. Pls suggest if there is a way to improve further. Table structure,Query style is as below, create table test1 ...200 ( key0 text, key1 text, key2 text, key3 text, key4 text, nonKey0 text, nonKey1 text, nonKey2 text, nonKey3 text, nonKey4 text, primary key(key0,key1,key2,key3,key4,key5) ); Query Used.. SELECT * FROM TABLE136 WHERE key0='490' AND key1='491' AND key2='492' AND key3='493' AND key4='494' API used sqlite_get_table... regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] In Mem Query Performance
RaghavendraK 70574 <[EMAIL PROTECTED]> wrote: > Hi, > > When the DB is opened in "in Memory mode",performance of query does not > improve. For table which has 10 columns of type Text and each column having > 128bytes data and having a total of 1 records. Performance is around 2400 > records/sec. Any ways to improve it. > You should be getting something well in excess of 1 million records per second. Probably you are structuring your query in a way that is very inefficient. Please provide your schema and the exact text of your query and perhaps someone can help you. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] In Mem Query Performance
Hi, When the DB is opened in "in Memory mode",performance of query does not improve. For table which has 10 columns of type Text and each column having 128bytes data and having a total of 1 records. Performance is around 2400 records/sec. Any ways to improve it. Thanks in advance. regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - To unsubscribe, send email to [EMAIL PROTECTED] -
