The database is strictly read-only on the Android side. When created on the PC, I issue, for example, the command "CREATE INDEX EvtIdx ON tblEvtRef (idind);". On the Android side, I use the SQLiteDatabase.query method, in which one specifies the SQL command in fragments as opposed to having it parse out an entire query string, but what I'm doing is equivalent to:
"SELECT col1,col2,... FROM tblEvtRef WHERE idind = <val>;" Where "val" is an integer e.g, "idind = 221" (this is not the primary key, and multiple matches are expected). All of my queries are basically at this level of simplicity. I'll take a look at the database on the Android using the sqlite3 utility and see what it tells me, but overall SQLite is a somewhat restricted subset of what a typical database server would provide. Still, I'd like to exhaust any possible avenues for improving efficiency. Doug Gordon On Nov 28, 3:14 pm, Frank Weiss <fewe...@gmail.com> wrote: > 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
