Re: [sqlite] SQLite performance for 10 Million Records

[Manzoor Ilahi Tamimy]

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?.


Regards

TAMIMY


On Mon, 19 Jun 2006 12:38:32 -0400, Derrell.Lipman wrote
> "Manzoor Ilahi Tamimy" <[EMAIL PROTECTED]> writes:
> 
> > //--------------------------------------------------------------------
> > 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)
> 
> The count() function is always slow, I think, because no index can 
> be used; it must actually count the number of records.  I suspect, 
> though, that your problem is missing indexes.  Is there an implicit 
> or explicit index on itm.IDC?  Is there an implicit or explicit 
> index on HVH.IDC?  With query times like you're seeing, I'd assume not.
> 
> If you post your schema along with the queries you want to issue,
>  many people here will likely provide suggestions for improvement.
> 
> Derrell