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
Developer
trivedirujuta
Posts: 528
Registered: ‎10-06-2009
My Device: 9800,BB Dev Alpha,z10 limited edition
My Carrier: Vodafone

Need urgent help to improve db performance

Hi,

 

I have around 465 records in table named productcategory and need to iterate thourgh parentid field to display data in treefield. its like if parentid=null then it should be shown as parentnode in tree otherwise childnode.I have also set index on parentid but still its taking too long to display.Here is the code.

 

try {

URI myURI = URI.create(ConstantCodes.DBPATH);
d = DatabaseFactory.open(myURI);
Statement stn1 = d.createStatement("SELECT ExternalProductCategoryID,ParentId,Name " +
"FROM ProductCategory "+
"WHERE ParentId ='null'");

stn1.prepare();
Cursor cursorcat1 = stn1.getCursor();

Row r ;
while (cursorcat1.next()) {

r = cursorcat1.getRow();
System.out.println("in loop????????????");
Statement stn2 = d.createStatement("SELECT * " +
"FROM ProductCategory "+
"WHERE ParentId = ?");
stn2.prepare();
stn2.bind(1, r.getString(0));
Cursor cursorcat2 = stn2.getCursor();
System.out.println("222222222222222222"+r.getString(0));

Row row;
int parentnode=0;
boolean isAdded = false;
boolean isEqual=false;

while (cursorcat2.next()) {
boolean isPurchased=false;
row = cursorcat2.getRow();
System.out.println("in nexxxxxxxxxxxt"+row.getString(1));

Statement stn3 = d.createStatement("SELECT * " +
"FROM ProductCategory "+
"WHERE ParentId = ?");
stn3.prepare();
stn3.bind(1, row.getString(0));
Cursor cursorcat3 = stn3.getCursor();

Row r3;
while(cursorcat3.next()){
r3=cursorcat3.getRow();
Statement stprodcomp = d.createStatement("SELECT * " +
"FROM ItemList " +
"WHERE List_Id = ? AND ProductCategory_ExternalProductCategoryID=? AND Purchased=0");
stprodcomp.prepare();
System.out.println(listids[objMyList.getSelectedIndex()]+"iddddddd");
stprodcomp.bind(1, listids[objMyList.getSelectedIndex()]);
stprodcomp.bind(2, r3.getString(0));
Cursor cursorprodcomp = stprodcomp.getCursor();
Row rowcomp;
while (cursorprodcomp.next()) {
rowcomp = cursorprodcomp.getRow();
if(!isAdded)
{parentnode=tree.addChildNode(0, r.getString(2),false,r.getString(2),r.getString(0),0,"","");
System.out.println("parent added");
isAdded=true;
isThereData=true;}
if(rowcomp.getString(3).equalsIgnoreCase(""))
tree.addChildNode(parentnode, r3.getString(2),false,r3.getString(2),r3.getString(0),0,"","");
else{
Statement stpro = d.createStatement("SELECT * " +
"FROM Product " +
"WHERE ProductCategory_ExternalProductCategoryID=? AND Barcode=?");
stpro.prepare();
stpro.bind(1, rowcomp.getString(2));
stpro.bind(2,rowcomp.getString(3));
Cursor cr = stpro.getCursor();
Row rprod;
while (cr.next()) {
rprod = cr.getRow();
System.out.println("in child added");
tree.addChildNode(parentnode, rprod.getString(2),false,rprod.getString(2),rprod.getString(1),0,rprod.getString(0),"");
}
stpro.close();
}

}
stprodcomp.close();


}
stn3.close();

Statement stprodcomp = d.createStatement("SELECT * " +
"FROM ItemList " +
"WHERE List_Id = ? AND ProductCategory_ExternalProductCategoryID=? AND Purchased=0");
stprodcomp.prepare();
System.out.println(listids[objMyList.getSelectedIndex()]+"iddddddd");
stprodcomp.bind(1, listids[objMyList.getSelectedIndex()]);
stprodcomp.bind(2, row.getString(0));
Cursor cursorprodcomp = stprodcomp.getCursor();
Row rowcomp;
while (cursorprodcomp.next()) {
rowcomp = cursorprodcomp.getRow();
if(!isAdded)
{parentnode=tree.addChildNode(0, r.getString(2),false,r.getString(2),r.getString(0),0,"","");
System.out.println("parent added");
isThereData=true;
isAdded=true;}
if(rowcomp.getString(3).equalsIgnoreCase(""))
tree.addChildNode(parentnode, row.getString(2),false,row.getString(2),row.getString(0),0,"","");
else{
Statement stpro = d.createStatement("SELECT * " +
"FROM Product " +
"WHERE ProductCategory_ExternalProductCategoryID=? AND Barcode=?");
stpro.prepare();
stpro.bind(1, rowcomp.getString(2));
stpro.bind(2,rowcomp.getString(3));
Cursor cr = stpro.getCursor();
Row rprod;
while (cr.next()) {
rprod = cr.getRow();
System.out.println("in child added");
tree.addChildNode(parentnode, rprod.getString(2),false,rprod.getString(2),rprod.getString(1),0,rprod.getString(0),"");
}
stpro.close();
}

}
stprodcomp.close();

}
//}

//}
stn2.close();

}
stn1.close();
d.close();
if(isThereData)
vfm.add(tree);
System.out.println("tree added??????????");


}
catch ( Exception e ) {
System.out.println(e+"Exception in treee");

} }

 

Please please help me.

Rujuta Trivedi
Please use plain text.
Regular Contributor
lunaplena
Posts: 205
Registered: ‎06-11-2013
My Device: Blackbery z10
My Carrier: RIM

Re: Need urgent help to improve db performance

I seriouly suggest you to use Native SDK to develope this app, you will get better performence naturally.

Please use plain text.
Developer
kanaksony
Posts: 571
Registered: ‎04-11-2009
My Device: BB 10 Dev Alpha

Re: Need urgent help to improve db performance

Hi Rujuta,

 

I have also worked upon a BB Project using SQLite as DB, I also faced serious performance issues regarding time to fetch/insert results from DB with large data. 

 

After using Database.beginTransaction() and Database.commitTransaction() methods, the performance was really better.

 

The time to insert records in DB earlier, which was around 2 minutes reduced upto 40 seconds afterwards.

 

May be this can help.

 

 

Regards,
Kanak Sony
------------------------------------------------------------------------------------------
http://dodevelopnshare.wordpress.com/ | http://www.linkedin.com/profile/view?id=188131481&trk=tab_pro
Please use plain text.
Regular Contributor
lunaplena
Posts: 205
Registered: ‎06-11-2013
My Device: Blackbery z10
My Carrier: RIM

Re: Need urgent help to improve db performance

If your database is so large. I suggest you to take your database to a powerful service instead of deploying it to a mobile device. Then you can send web request to get your data from the server. And I am sure it will be lot more faster than 40sec. 

Please use plain text.