If you are using Internet Explorer, please remove blackberry.com from your compatibility view settings.

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
simon_hain
Posts: 15,892
Registered: ‎07-29-2008
My Device: Z10 LE
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
Please use plain text.
Developer
Zmey
Posts: 1,510
Registered: ‎12-18-2012
My Device: PlayBook, Z10, DAC

Re: SQLite performance optimization

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

Andrey Fidrya, @zmeyc on twitter
Please use plain text.
Developer
simon_hain
Posts: 15,892
Registered: ‎07-29-2008
My Device: Z10 LE
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 :smileyhappy:
----------------------------------------------------------
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
Please use plain text.
Developer
simon_hain
Posts: 15,892
Registered: ‎07-29-2008
My Device: Z10 LE
My Carrier: O2 Germany

Re: SQLite performance optimization

[ Edited ]

edit: solved it :smileyhappy:

 

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

:Chef:

----------------------------------------------------------
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
Please use plain text.