Java Development

[chaitu2408]

I have 168mb sqlite database in sdcard containing nearly 30 tables each with more than 1000 records. i am using this query to fetch results from database.

select code mfr_code, name mfr_name
from manufacturers
where mfgr_type_code <5and code in(
        select mfgr_code
        from equipment_groups
        )

This query is taking more than 30 seconds in curve 5.0 and more than 1minute in torch 6.0 devices.

The subquery "SELECT mfgr_code FROM equipment_groups" is fetching more than 50k records.

same query when test in android is taking lessthan 5 seconds. What is the problem with blackberry.

Is there any way other way to reduce this processing time.

[maadani]

Yes, sqlite in blackberry is slow.

However, you can try and improve your performance by reducing the number of records you are dealing with:

1. Try and improve your queries. For example, I think that you can re-write your query to something like this:

select code mfr_code, name mfr_name
from manufacturers
where mfgr_type_code <5and exists(
        select mfgr_code
        from equipment_groups

        where code = mfgr_code
        )

You will have to check that the queries produce the same results and which one works better...

2. Alter your DB scheme. Try avoiding joins since a join query creates a temp table with N*M records (N & M are the numbers of records of the involved tables).

3. Try spliting big tables into several tables. For eaxmple, if you have a table with 50k records, try spliting it to 5 tables with 10K records each, usually, according to the ID column. Again, you will have to check for the optimal number of records. 

Hope that helps,

E.