Dear ALL, I am really thankful to Bill King, Micha Bieber , Derrell for your valuable suggestions.
I was really confused that which way should I follow now, because I was sure that SQLite will work much better. when I got the suggestion about Firebird then again I went to the comparison page of SQLite and found that Firebird was also slow in many cases compared to SQLite. >From the DerrellÂ’s reply again I got hope that yes I can achieve that using SQLite. Now I want to add some more information about my work may be you will add some suggestions. //-------------------------------------------------------------------- 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) //-------------------------------------------------------------------- Now Please have look at these results. What should I do now to improve the performance. Thanks and regards Manzoor Ilahi Tamimy On Mon, 19 Jun 2006 09:47:03 -0400, Derrell.Lipman wrote > Bill King <[EMAIL PROTECTED]> writes: > > > Manzoor Ilahi Tamimy wrote: > > > >> We are Using SQLite for one of our project. > >> > >>The Database Size is more than 500 MB. > >>It contain one table and about 10 million Records. > > > Err, for that size, I'd recommend going something heavier, like > > firebird. This is not sqlite's solution domain in the slightest. > > I'd have to differ on opinion here. I have an sqlite database > that's at 6.9GB with about 40 million records, and it's working just > fine. My query speed has not changed substantially as the database > has grown. > > With sqlite, you *do* need to hand-optimize your queries. Determine > which queries are slow, and consider splitting them into multiple > queries which generate temporary tables. By doing this, you can > create indexes, as appropriate, on the temporary tables that make > the overall time to accomplish your goal much less than cramming it > all into a single query that is not so highly optimized. > > Oh, and I'm using the old sqlite 2.8 series. I expect I'd be > getting even better speed if I used the newer 3 series. > > Cheers, > > Derrell
