--- 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 10000 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='kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk
> kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk490' AND
> key1='kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk
> kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk491' AND
> key2='kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk
> kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk492' AND
> key3='kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk
> kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk493' AND
> key4='kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk
> kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk494'
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&sid=396545469
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
