Welcome!

Welcome to the official BlackBerry Support Community Forums.

This is your resource to discuss support topics with your peers, and learn from each other.

inside custom component

Java Development

Reply
New Contributor
Posts: 7
Registered: ‎11-16-2011
My Device: 9800
My Carrier: airtel

Sqlite performance

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.

Developer
Posts: 729
Registered: ‎05-04-2011
My Device: 9700

Re: Sqlite performance

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.