At 03:30 20/06/2006, you wrote:
Here Is The Schema For these Tables.
CREATE TABLE HVH (
Field1 VARCHAR(8), IDC VARCHAR(4),
Field3 VARCHAR(2), Field4 VARCHAR(4),
Field5 VARCHAR(7), Field6 VARCHAR(8),
Field7 VARCHAR(1), Field8 FLOAT);
CREATE TABLE ITM(
IDC VARCHAR(4), ITEMNAME VARCHAR(20),
COLUMN3 VARCHAR(1), COLUMN4 VARCHAR(1),
COLUMN5 VARCHAR(1), COLUMN6 VARCHAR(1),
COLUMN7 VARCHAR(1), COLUMN8 VARCHAR(1),
COLUMN9 VARCHAR(1), COLUMN10 VARCHAR(1),
COLUMN11 VARCHAR(1), COLUMN12 VARCHAR(1),
COLUMN13 VARCHAR(1), COLUMN14 VARCHAR(1),
COLUMN15 VARCHAR(1), COLUMN16 VARCHAR(1));
CREATE INDEX index1 ON ITM (IDC);
//--------------------------------------------------------------------
TEST 1 ( Disk DB )
Table Names itm , HVH
Number of Records : itm  100,000 and HVH  less than 10,000
QUERY:
Select count(*) from itm, HVH where itm .IDC = HVH.IDC
Elapse Time : 54.359 (Sec)
//--------------------------------------------------------------------
TEST 2 ( Disk DB )
Table Names itm , HVH
Number of Records : itm  5 Million and HVH  less than
10,000
QUERY:
create index index1 on itm(IDC)
Elapse Time : 0.356 (Sec)
Select count(*) from itm,HVH where itm.IDC=HVH.IDC
Elapse Time : 64 (Sec)
//--------------------------------------------------------------------
TEST 3 ( Disk DB )
Table Names itm
Number of Records : itm  20 Million
QUERY:
Select count(*) from itm
Elapse Time : 133 (Sec)
//--------------------------------------------------------------------
Where need chnge?.
add before create:
pragma default_cache_size =65536 (64M pages in cache memory, each
page 8.5 KBytes in memory -> 00MB of memory, adjust as you need)
pragma cache_size = 8192 (8KBytes per page in disk)
pragma temp_store = 2 ( Use memory for all indices, temp tables, etc...)
You can try before query
pragma synchronous = 0 (Or OFF, but as it's a read, i dude it can help)
Also you forget to add an index to HVH table.
Also you can check the www.sqlite.org main page, there is a direct
link to documentation.
Remember: Software is not a ring that only can be pushed, it has
instructions and technical documentation, and in this case, clear,
concise and brief, and very good.
