[android-developers] sqlite performance
i have android application that it save transaction history in database file (sqlite) on the sdcard but there is a lot of records and after number of records done or size application create another similar database file to save in. so can anyone tell me after how many record numbers or what size the database lose their performance -- You received this message because you are subscribed to the Google Groups Android Developers group. To post to this group, send email to android-developers@googlegroups.com To unsubscribe from this group, send email to android-developers+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/android-developers?hl=en
[android-developers] SQLite Performance
My app works by using a SQLite database that is generated on the user's PC and transferred to the device. It all works, but I had not anticipated the number of users who would have really huge amounts of data. In these cases, the UI is very sluggish as it waits for the data to be fetched. I've tried a number of tricks that I was sure would speed things up, but nothing seems to have any noticeable effect. My queries are almost all very simple, being usually a single col=val for the WHERE clause, and INTEGER data in the column. So I can't do much with the queries. The latest, and I am not an SQL expert by any means, was to use CREATE INDEX commands on the PC, believing that these indeces are used to speed up database searches. The indeces increased the size of the database file significantly, so I was then surprised that it seemed to have no effect whatsoever on the speed of my app! A screen that was taking 8 seconds to fill without indeces still takes about 8 seconds even with them. I was hoping to get things down to at least half that. What I am wondering at this point is if the SQLite implementation on Android uses database indeces at all, or if I'm just wasting space by generating them. Can anyone answer this? Also, any other things to try to speed up access? (For what it's worth, on an absolute basis the users have nothing to complain about. My worst-case user so far has data that generates 630,000 records (15 tables), so there's only so much that's possible!) Doug Gordon GHCS Systems -- You received this message because you are subscribed to the Google Groups Android Developers group. To post to this group, send email to android-developers@googlegroups.com To unsubscribe from this group, send email to android-developers+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/android-developers?hl=en
Re: [android-developers] SQLite Performance
I assume you appreciate the fact that just knowing SQL is not knowing how to optimize queries. I'm also going to assume that the read queries are the issue. Insert and delete queries are a whole different issue with respect to indexing. Indexes are indeed the primary means of optimizing SQL queries. I'm wondering if the create index command you ran on the PC is actually in effect on the Android device. If I'm not mistaken, you should be able to verify that the DB on the device actually has indexing enabled. Have you verified the speedup that indexing provided on the PC? If adding column indexing by itself is not effective, you'll need to look into the queries and the actual data patterns. Some queries are structured so that the DB cannot use indexes. See if sqlite has an analyze command for debugging queries. It will show the steps it would use to execute the query. If there's no step that uses the index (even if there is a column index) or if the index lookup is at the end of the steps, then the query is not making maximal use of the index. It's a bit of an art to coerce the query compiler to do it right and sometimes there are pitfalls in SQL that need to be understood. How many queries does you app use? Which one is the bottleneck? Can you post the query? -- You received this message because you are subscribed to the Google Groups Android Developers group. To post to this group, send email to android-developers@googlegroups.com To unsubscribe from this group, send email to android-developers+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/android-developers?hl=en
