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

Native Development

Reply
Developer
Posts: 16,992
Registered: ‎07-29-2008
My Device: Z10 LE, Z30, Passport
My Carrier: O2 Germany
Accepted Solution

SQLite performance optimization

We have a performance issue with our sqlite database.

There are about 10k entries in the table. We select 20 of them with a trivial query (select * by id1 and id2 order by date desc limit 20 offset 0) within a transaction.

The device needs 865ms for this, when we copy the database to the PC and use SQLiteManager the call is executed in 76ms.

 

As we can get tables with about 100k entries in some cases we would really like to get these queries faster.

Any ideas?

----------------------------------------------------------
feel free to press the like button on the right side to thank the user that helped you.
please mark posts as solved if you found a solution.
@SimonHain on twitter
Developer
Posts: 1,524
Registered: ‎12-18-2012
My Device: Z30, Z10 LE, DevAlpha C, PlayBook

Re: SQLite performance optimization

Hi!
Were indexes built on these fields? What does
"EXPLAIN QUERY PLAN SELECT ……" print?

Andrey Fidrya, @zmeyc on twitter
Developer
Posts: 16,992
Registered: ‎07-29-2008
My Device: Z10 LE, Z30, Passport
My Carrier: O2 Germany

Re: SQLite performance optimization

it seems we have indeed a missing index on one of the used fields. i feel stupid now Smiley Happy
----------------------------------------------------------
feel free to press the like button on the right side to thank the user that helped you.
please mark posts as solved if you found a solution.
@SimonHain on twitter
Developer
Posts: 16,992
Registered: ‎07-29-2008
My Device: Z10 LE, Z30, Passport
My Carrier: O2 Germany

Re: SQLite performance optimization

[ Edited ]

edit: solved it Smiley Happy

 

it is quite bothersome to optimize the indices as the bb10 sqlite is prone to using a different approach than the SQLiteManager.

Still, we have several working indices now, an "ANALYZE" at the end of the initialization also improved the performance, and on top we have added some limitations to the queried range.

we also loaded a list of often-used things into memory at app launch.

 

before: 2928ms

now: 184ms

----------------------------------------------------------
feel free to press the like button on the right side to thank the user that helped you.
please mark posts as solved if you found a solution.
@SimonHain on twitter