Re: [android-developers] android SQLite
usually the syncing with a database pattern happens via service in an alarmmanager then updating a content provider or something of that nature.. kris On Fri, Oct 26, 2012 at 1:26 AM, nageswara rao Rajana wrote: > Hi, > > In my app i should maintain a local database which contain 20 tables with > primary and foreign keys later it should sync with remote server database. > I have some queries following > > I came across that foreign key constraint is not supported. > Can we implement Joins. > What is maximum size of database file. > Do we have any api's to sync with remote database. > > Please help me > > Thanking you, > nageswararao. > > > -- > 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 -- 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] android SQLite
Hi, In my app i should maintain a local database which contain 20 tables with primary and foreign keys later it should sync with remote server database. I have some queries following - I came across that foreign key constraint is not supported. - Can we implement Joins. - What is maximum size of database file. - Do we have any api's to sync with remote database. Please help me Thanking you, nageswararao. -- 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] Android, SQLite, massive (5000%+) loss of performance on a certain query, on a certain device
Can you please elaborate? Basically, what I need to do is find the row number of a particular row where _id = X.Basically, I am trying to position my ListView so that this row is showing out of potentially thousands of rows. I strongly suspect there is a better way than to loop through the Cursor and check the_id to get its position, but I don't know what it is. The order, in which, the rows are returned are user selectable -- I have indecies on all the sort columns. Any ideas would be welcomed. I, by no means, consider myself a SQL guru. On Thursday, March 22, 2012 7:54:36 AM UTC+8, lbendlin wrote: > > Of course there is a faster way. Run a second query that identifies the > required row (make sure the condition is indexed) and throw the cursor out > of the window. > > On Wednesday, March 21, 2012 3:22:24 AM UTC-4, Zsolt Vasvari wrote: >> >> I run the exact same query twice in parallel: once in the UI thread (I >> know I shouldn't, but it's fast) and then I run it again in the AsyncTask, >> this time reading through the entire cursor to find a particular row index. >> (Is there a faster way to do that in SQL, btw?). >> >>> -- 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] Android, SQLite, massive (5000%+) loss of performance on a certain query, on a certain device
Hmm, it's hard to debug since I cannot duplicate the issue and it happens for so few users. AFAIK, both of these guys were on Gingerbread. On Wednesday, March 21, 2012 10:22:11 PM UTC+8, Kostya Vasilyev wrote: > > Are you perhaps running multiple instances of this query at the same > time, causing contention inside SQLite? > > From what you've described, it seems possible - since AsyncTask uses a > pool of more than one thread, and you also mentioned running this > query on the UI thread as well. > > I would try to use AsyncQueryHandler, which processes requests one at > a time, or some other mechanism to ensure that these queries are > serialized -- if only for the sake of debugging. > > As far as finding an index, two things come to mind. > > One is a cache of prior query results (perhaps using a SparseArray or > something like it) and then looking up indices in this cache. > > The other is writing a count(*) type query just to get the index. > > Perhaps you've already considered both, given your vast experience, if > so, my apologies for suggesting something so obvious. > > -- K > > 21 марта 2012 г. 16:54 пользователь Zsolt Vasvari > написал: > > But that should effect both the UI thread query and the worker thread > query. > > But only one runs slow. > > -- 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] Android, SQLite, massive (5000%+) loss of performance on a certain query, on a certain device
Of course there is a faster way. Run a second query that identifies the required row (make sure the condition is indexed) and throw the cursor out of the window. On Wednesday, March 21, 2012 3:22:24 AM UTC-4, Zsolt Vasvari wrote: > > I run the exact same query twice in parallel: once in the UI thread (I > know I shouldn't, but it's fast) and then I run it again in the AsyncTask, > this time reading through the entire cursor to find a particular row index. > (Is there a faster way to do that in SQL, btw?). > >> >>> -- 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] Android, SQLite, massive (5000%+) loss of performance on a certain query, on a certain device
Wow. Just checked my 4.0.1 and 4.0.3 sources - yes, it's there: /** * An {@link Executor} that can be used to execute tasks in parallel. */ public static final Executor THREAD_POOL_EXECUTOR = new ThreadPoolExecutor(CORE_POOL_SIZE, MAXIMUM_POOL_SIZE, KEEP_ALIVE, TimeUnit.SECONDS, sPoolWorkQueue, sThreadFactory); /** * An {@link Executor} that executes tasks one at a time in serial * order. This serialization is global to a particular process. */ public static final Executor SERIAL_EXECUTOR = new SerialExecutor(); private static volatile Executor sDefaultExecutor = SERIAL_EXECUTOR; The documentation is still somewhat lacking: "After HONEYCOMB, it is planned to change this back to a single thread to avoid common application errors caused by parallel execution." "Planned to change" is not the same as "Yes, we did it". Anyway, back to the original issue - Zsolt didn't mention which executor his code is using and which Android version his customer is running. Also of interest: AsyncTaskLoader explicitly uses THREAD_POOL_EXECUTOR. -- K 22 марта 2012 г. 1:29 пользователь Mark Murphy написал: > 2012/3/21 Kostya Vasilyev : >> From what you've described, it seems possible - since AsyncTask uses a >> pool of more than one thread, and you also mentioned running this >> query on the UI thread as well. > > Actually... on ICS, that's apparently no longer true. > > Somebody tweeted this year-old commit today: > > https://github.com/android/platform_frameworks_base/commit/81de61bfddceba0eb77b3aacea317594b0f1de49 > > which looks like they're only doing one at a time by default in ICS, > unless you use an Executor with a thread pool. > > I need to run some tests to confirm that this indeed is the ICS > implementation. > > -- > Mark Murphy (a Commons Guy) > http://commonsware.com | http://github.com/commonsguy > http://commonsware.com/blog | http://twitter.com/commonsguy > > Android Training...At Your Office: http://commonsware.com/training > > -- > 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 -- 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] Android, SQLite, massive (5000%+) loss of performance on a certain query, on a certain device
2012/3/21 Kostya Vasilyev : > From what you've described, it seems possible - since AsyncTask uses a > pool of more than one thread, and you also mentioned running this > query on the UI thread as well. Actually... on ICS, that's apparently no longer true. Somebody tweeted this year-old commit today: https://github.com/android/platform_frameworks_base/commit/81de61bfddceba0eb77b3aacea317594b0f1de49 which looks like they're only doing one at a time by default in ICS, unless you use an Executor with a thread pool. I need to run some tests to confirm that this indeed is the ICS implementation. -- Mark Murphy (a Commons Guy) http://commonsware.com | http://github.com/commonsguy http://commonsware.com/blog | http://twitter.com/commonsguy Android Training...At Your Office: http://commonsware.com/training -- 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] Android, SQLite, massive (5000%+) loss of performance on a certain query, on a certain device
Are you perhaps running multiple instances of this query at the same time, causing contention inside SQLite? >From what you've described, it seems possible - since AsyncTask uses a pool of more than one thread, and you also mentioned running this query on the UI thread as well. I would try to use AsyncQueryHandler, which processes requests one at a time, or some other mechanism to ensure that these queries are serialized -- if only for the sake of debugging. As far as finding an index, two things come to mind. One is a cache of prior query results (perhaps using a SparseArray or something like it) and then looking up indices in this cache. The other is writing a count(*) type query just to get the index. Perhaps you've already considered both, given your vast experience, if so, my apologies for suggesting something so obvious. -- K 21 марта 2012 г. 16:54 пользователь Zsolt Vasvari написал: > But that should effect both the UI thread query and the worker thread query. > But only one runs slow. -- 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] Android, SQLite, massive (5000%+) loss of performance on a certain query, on a certain device
But that should effect both the UI thread query and the worker thread query. But only one runs slow. On Wednesday, March 21, 2012 7:11:43 PM UTC+8, Mark Murphy (a Commons Guy) wrote: > > Also, bear in mind that on 2.2 and below (and some 2.3 devices), we > are dealing with the YAFFS2 filesystem, so if some other app is doing > a bunch of disk I/O, you may be blocked by their work. > > On Wed, Mar 21, 2012 at 3:22 AM, Zsolt Vasvari wrote: > > Actually, just to respond to myself -- I don't believe this a SQLite > issue. > > > > For this only happens on the one query that runs in the AsyncTask. > > > > I run the exact same query twice in parallel: once in the UI thread (I > know > > I shouldn't, but it's fast) and then I run it again in the AsyncTask, > this > > time reading through the entire cursor to find a particular row index. > (Is > > there a faster way to do that in SQL, btw?). > > > > Anyway, the first query runs quick, the second one has this problem the > OP > > saw for a couple of users. Since the query is the same in both cases, I > > think this is a thread starvation issue instead of a SQLite query > problem. > > That's just my hunch based on 20 years of development experience. > > > > > > > > > > On Wednesday, March 21, 2012 2:28:46 PM UTC+8, Zsolt Vasvari wrote: > >> > >> I have 2 users (out of thousands) reporting this problem. I am at a > loss > >> as to why this is happening, also. It doesn't have anything to the > data as > >> the user claims they few rows. I even had them recreate their database > (I > >> have a backup facility that that backs up/restores from JSON) and the > >> problem still occurs. > >> > >> One guy took the phone back because he suspected a hardware issue. The > >> other person never followed up, so I don't know if he solved it or what. > >> > >> > >> > >> On Wednesday, March 21, 2012 6:54:30 AM UTC+8, Dianne Hackborn wrote: > >>> > >>> You can get the SQLite version to perform acceptably. You just need to > >>> use it correctly -- set up indices as appropriate as needed for joins > and > >>> such. I am not a SQL (lite or otherwise) expect in any way so I can't > help > >>> you with the particulars, but at the very least make sure you are > actually > >>> setting indices on the columns that are involved in deciding what rows > are > >>> included in the query result. > >>> > >>> Also all you are doing by putting your query in the main thread of your > >>> process is causing your process to ANR when it takes a long time. The > query > >>> all happens in native code down in SQLite, so you won't see anything > in your > >>> java traces (nor typically anything interesting in native traces either > >>> since most likely, yes, you are executing the query in SQLite). > >>> > >>> On Tue, Mar 20, 2012 at 3:22 PM, momo wrote: > > I'm rewriting a simple translation app with a SQLite db. There is an > extreme hit to performance between two queries, but only on certain > devices. > > One query lists the english words in a ListView, the other lists the > secondary language in a list view. The data is structured > differently, and > is from a remote server. > > Both are single SQL statements, run via db.rawQuery. Both use > AsyncTask > to keep heavy lifting in another thread. > > On both devices, the "english" query returns almost instantly (less > than > 1 second, every time): > > return db.rawQuery("select _id as id, english as label from > english_words order by english collate nocase", null); > > On one device, the "secondary_langauge" query returns almost instantly > as well. No problem there, ever. This is a Samsung Galaxy SII. On > another > device (Samsung Nexus S), this query takes around 30 seconds. This > query > has some joins, as follows: > > return db.rawQuery("select definitions._id as id, secondary_language > as > label from english_words join definition_bridge on > english_words._id=definition_bridge.word_id join definitions on > definitions._id=definition_bridge.definition_id order by > secondary_language", null); > > I ran it in the emulator once, and got the same result as the Nexus S > (the 30 second hang). It took a little 1.5 hours to download and > parse the > returns from the server on the emulator (which takes a few seconds on > either > device), so I gave up on further debug with the emulator at that > point. > > This is the only difference between the two operations. The listView > is > the same, the adapter is the same, the AsyncTask is the same. The > number of > rows returned is different - there are about 2000 english words, and a > little over 3000 words in the other language. I don't think this > explains > the vast difference in performance. > > I took the query out of the AsyncTask to see if I cou
Re: [android-developers] Android, SQLite, massive (5000%+) loss of performance on a certain query, on a certain device
Also, bear in mind that on 2.2 and below (and some 2.3 devices), we are dealing with the YAFFS2 filesystem, so if some other app is doing a bunch of disk I/O, you may be blocked by their work. On Wed, Mar 21, 2012 at 3:22 AM, Zsolt Vasvari wrote: > Actually, just to respond to myself -- I don't believe this a SQLite issue. > > For this only happens on the one query that runs in the AsyncTask. > > I run the exact same query twice in parallel: once in the UI thread (I know > I shouldn't, but it's fast) and then I run it again in the AsyncTask, this > time reading through the entire cursor to find a particular row index. (Is > there a faster way to do that in SQL, btw?). > > Anyway, the first query runs quick, the second one has this problem the OP > saw for a couple of users. Since the query is the same in both cases, I > think this is a thread starvation issue instead of a SQLite query problem. > That's just my hunch based on 20 years of development experience. > > > > > On Wednesday, March 21, 2012 2:28:46 PM UTC+8, Zsolt Vasvari wrote: >> >> I have 2 users (out of thousands) reporting this problem. I am at a loss >> as to why this is happening, also. It doesn't have anything to the data as >> the user claims they few rows. I even had them recreate their database (I >> have a backup facility that that backs up/restores from JSON) and the >> problem still occurs. >> >> One guy took the phone back because he suspected a hardware issue. The >> other person never followed up, so I don't know if he solved it or what. >> >> >> >> On Wednesday, March 21, 2012 6:54:30 AM UTC+8, Dianne Hackborn wrote: >>> >>> You can get the SQLite version to perform acceptably. You just need to >>> use it correctly -- set up indices as appropriate as needed for joins and >>> such. I am not a SQL (lite or otherwise) expect in any way so I can't help >>> you with the particulars, but at the very least make sure you are actually >>> setting indices on the columns that are involved in deciding what rows are >>> included in the query result. >>> >>> Also all you are doing by putting your query in the main thread of your >>> process is causing your process to ANR when it takes a long time. The query >>> all happens in native code down in SQLite, so you won't see anything in your >>> java traces (nor typically anything interesting in native traces either >>> since most likely, yes, you are executing the query in SQLite). >>> >>> On Tue, Mar 20, 2012 at 3:22 PM, momo wrote: I'm rewriting a simple translation app with a SQLite db. There is an extreme hit to performance between two queries, but only on certain devices. One query lists the english words in a ListView, the other lists the secondary language in a list view. The data is structured differently, and is from a remote server. Both are single SQL statements, run via db.rawQuery. Both use AsyncTask to keep heavy lifting in another thread. On both devices, the "english" query returns almost instantly (less than 1 second, every time): return db.rawQuery("select _id as id, english as label from english_words order by english collate nocase", null); On one device, the "secondary_langauge" query returns almost instantly as well. No problem there, ever. This is a Samsung Galaxy SII. On another device (Samsung Nexus S), this query takes around 30 seconds. This query has some joins, as follows: return db.rawQuery("select definitions._id as id, secondary_language as label from english_words join definition_bridge on english_words._id=definition_bridge.word_id join definitions on definitions._id=definition_bridge.definition_id order by secondary_language", null); I ran it in the emulator once, and got the same result as the Nexus S (the 30 second hang). It took a little 1.5 hours to download and parse the returns from the server on the emulator (which takes a few seconds on either device), so I gave up on further debug with the emulator at that point. This is the only difference between the two operations. The listView is the same, the adapter is the same, the AsyncTask is the same. The number of rows returned is different - there are about 2000 english words, and a little over 3000 words in the other language. I don't think this explains the vast difference in performance. I took the query out of the AsyncTask to see if I could get some more debug info, and did get an ANR: at android.database.sqlite.SQLiteQuery.native_fill_window(Native Method) at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:73) at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:287) at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:268) at com.whatever.adapters.W
Re: [android-developers] Android, SQLite, massive (5000%+) loss of performance on a certain query, on a certain device
Actually, just to respond to myself -- I don't believe this a SQLite issue. For this only happens on the one query that runs in the AsyncTask. I run the exact same query twice in parallel: once in the UI thread (I know I shouldn't, but it's fast) and then I run it again in the AsyncTask, this time reading through the entire cursor to find a particular row index. (Is there a faster way to do that in SQL, btw?). Anyway, the first query runs quick, the second one has this problem the OP saw for a couple of users. Since the query is the same in both cases, I think this is a thread starvation issue instead of a SQLite query problem. That's just my hunch based on 20 years of development experience. On Wednesday, March 21, 2012 2:28:46 PM UTC+8, Zsolt Vasvari wrote: > > I have 2 users (out of thousands) reporting this problem. I am at a loss > as to why this is happening, also. It doesn't have anything to the data as > the user claims they few rows. I even had them recreate their database (I > have a backup facility that that backs up/restores from JSON) and the > problem still occurs. > > One guy took the phone back because he suspected a hardware issue. The > other person never followed up, so I don't know if he solved it or what. > > > > On Wednesday, March 21, 2012 6:54:30 AM UTC+8, Dianne Hackborn wrote: >> >> You can get the SQLite version to perform acceptably. You just need to >> use it correctly -- set up indices as appropriate as needed for joins and >> such. I am not a SQL (lite or otherwise) expect in any way so I can't help >> you with the particulars, but at the very least make sure you are actually >> setting indices on the columns that are involved in deciding what rows are >> included in the query result. >> >> Also all you are doing by putting your query in the main thread of your >> process is causing your process to ANR when it takes a long time. The >> query all happens in native code down in SQLite, so you won't see anything >> in your java traces (nor typically anything interesting in native traces >> either since most likely, yes, you are executing the query in SQLite). >> >> On Tue, Mar 20, 2012 at 3:22 PM, momo wrote: >> >>> I'm rewriting a simple translation app with a SQLite db. There is an >>> extreme hit to performance between two queries, but only on certain devices. >>> >>> One query lists the english words in a ListView, the other lists the >>> secondary language in a list view. The data is structured differently, and >>> is from a remote server. >>> >>> Both are single SQL statements, run via db.rawQuery. Both use AsyncTask >>> to keep heavy lifting in another thread. >>> >>> On both devices, the "english" query returns almost instantly (less than >>> 1 second, every time): >>> >>> return db.rawQuery("select _id as id, english as label from >>> english_words order by english collate nocase", null); >>> >>> On one device, the "secondary_langauge" query returns almost instantly >>> as well. No problem there, ever. This is a Samsung Galaxy SII. On >>> another device (Samsung Nexus S), this query takes around 30 seconds. This >>> query has some joins, as follows: >>> >>> return db.rawQuery("select definitions._id as id, secondary_language as >>> label from english_words join definition_bridge on >>> english_words._id=definition_bridge.word_id join definitions on >>> definitions._id=definition_bridge.definition_id order by >>> secondary_language", null); >>> >>> I ran it in the emulator once, and got the same result as the Nexus S >>> (the 30 second hang). It took a little 1.5 hours to download and parse the >>> returns from the server on the emulator (which takes a few seconds on >>> either device), so I gave up on further debug with the emulator at that >>> point. >>> >>> This is the only difference between the two operations. The listView is >>> the same, the adapter is the same, the AsyncTask is the same. The number >>> of rows returned is different - there are about 2000 english words, and a >>> little over 3000 words in the other language. I don't think this explains >>> the vast difference in performance. >>> >>> I took the query out of the AsyncTask to see if I could get some more >>> debug info, and did get an ANR: >>> >>> at android.database.sqlite.SQLiteQuery.native_fill_window(Native >>> Method) >>> at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:73) >>> at >>> android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:287) >>> at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:268) >>> at >>> com.whatever.adapters.WordListAdapter.getCount(WordListAdapter.java:39) >>> >>> I rewrote the adapter's getCount method to return a cached count >>> (determined during instantiation). After, I didn't get an ANR again, but >>> otherwise the performance was not improved and the query still took around >>> 30 seconds. >>> >>> I'm totally at a loss. As me
Re: [android-developers] Android, SQLite, massive (5000%+) loss of performance on a certain query, on a certain device
I have 2 users (out of thousands) reporting this problem. I am at a loss as to why this is happening, also. It doesn't have anything to the data as the user claims they few rows. I even had them recreate their database (I have a backup facility that that backs up/restores from JSON) and the problem still occurs. One guy took the phone back because he suspected a hardware issue. The other person never followed up, so I don't know if he solved it or what. On Wednesday, March 21, 2012 6:54:30 AM UTC+8, Dianne Hackborn wrote: > > You can get the SQLite version to perform acceptably. You just need to > use it correctly -- set up indices as appropriate as needed for joins and > such. I am not a SQL (lite or otherwise) expect in any way so I can't help > you with the particulars, but at the very least make sure you are actually > setting indices on the columns that are involved in deciding what rows are > included in the query result. > > Also all you are doing by putting your query in the main thread of your > process is causing your process to ANR when it takes a long time. The > query all happens in native code down in SQLite, so you won't see anything > in your java traces (nor typically anything interesting in native traces > either since most likely, yes, you are executing the query in SQLite). > > On Tue, Mar 20, 2012 at 3:22 PM, momo wrote: > >> I'm rewriting a simple translation app with a SQLite db. There is an >> extreme hit to performance between two queries, but only on certain devices. >> >> One query lists the english words in a ListView, the other lists the >> secondary language in a list view. The data is structured differently, and >> is from a remote server. >> >> Both are single SQL statements, run via db.rawQuery. Both use AsyncTask >> to keep heavy lifting in another thread. >> >> On both devices, the "english" query returns almost instantly (less than >> 1 second, every time): >> >> return db.rawQuery("select _id as id, english as label from english_words >> order by english collate nocase", null); >> >> On one device, the "secondary_langauge" query returns almost instantly as >> well. No problem there, ever. This is a Samsung Galaxy SII. On another >> device (Samsung Nexus S), this query takes around 30 seconds. This query >> has some joins, as follows: >> >> return db.rawQuery("select definitions._id as id, secondary_language as >> label from english_words join definition_bridge on >> english_words._id=definition_bridge.word_id join definitions on >> definitions._id=definition_bridge.definition_id order by >> secondary_language", null); >> >> I ran it in the emulator once, and got the same result as the Nexus S >> (the 30 second hang). It took a little 1.5 hours to download and parse the >> returns from the server on the emulator (which takes a few seconds on >> either device), so I gave up on further debug with the emulator at that >> point. >> >> This is the only difference between the two operations. The listView is >> the same, the adapter is the same, the AsyncTask is the same. The number >> of rows returned is different - there are about 2000 english words, and a >> little over 3000 words in the other language. I don't think this explains >> the vast difference in performance. >> >> I took the query out of the AsyncTask to see if I could get some more >> debug info, and did get an ANR: >> >> at android.database.sqlite.SQLiteQuery.native_fill_window(Native Method) >> at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:73) >> at >> android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:287) >> at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:268) >> at >> com.whatever.adapters.WordListAdapter.getCount(WordListAdapter.java:39) >> >> I rewrote the adapter's getCount method to return a cached count >> (determined during instantiation). After, I didn't get an ANR again, but >> otherwise the performance was not improved and the query still took around >> 30 seconds. >> >> I'm totally at a loss. As mentioned, everything but the queries is >> identical. And on the Galaxy SII, there is no problem at all - less than a >> second to populate the ListView, even under abuse (touching the button that >> launches the request as fast as I could). >> >> At this point, I'm wondering if it'd be better to abandon SQLite >> entirely, and using a Java model to manage data. Assuming I can't get the >> SQLite version to perform reasonably, what could I expect in terms of >> performance using Collections (with the number of entries mentioned above), >> when I need to do a search for example (which I imaging would require >> iterating over the whole thing on each key event). >> >> Any advice? >> >> TYIA. >> >> -- >> 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@go
Re: [android-developers] Android, SQLite, massive (5000%+) loss of performance on a certain query, on a certain device
You can get the SQLite version to perform acceptably. You just need to use it correctly -- set up indices as appropriate as needed for joins and such. I am not a SQL (lite or otherwise) expect in any way so I can't help you with the particulars, but at the very least make sure you are actually setting indices on the columns that are involved in deciding what rows are included in the query result. Also all you are doing by putting your query in the main thread of your process is causing your process to ANR when it takes a long time. The query all happens in native code down in SQLite, so you won't see anything in your java traces (nor typically anything interesting in native traces either since most likely, yes, you are executing the query in SQLite). On Tue, Mar 20, 2012 at 3:22 PM, momo wrote: > I'm rewriting a simple translation app with a SQLite db. There is an > extreme hit to performance between two queries, but only on certain devices. > > One query lists the english words in a ListView, the other lists the > secondary language in a list view. The data is structured differently, and > is from a remote server. > > Both are single SQL statements, run via db.rawQuery. Both use AsyncTask > to keep heavy lifting in another thread. > > On both devices, the "english" query returns almost instantly (less than 1 > second, every time): > > return db.rawQuery("select _id as id, english as label from english_words > order by english collate nocase", null); > > On one device, the "secondary_langauge" query returns almost instantly as > well. No problem there, ever. This is a Samsung Galaxy SII. On another > device (Samsung Nexus S), this query takes around 30 seconds. This query > has some joins, as follows: > > return db.rawQuery("select definitions._id as id, secondary_language as > label from english_words join definition_bridge on > english_words._id=definition_bridge.word_id join definitions on > definitions._id=definition_bridge.definition_id order by > secondary_language", null); > > I ran it in the emulator once, and got the same result as the Nexus S (the > 30 second hang). It took a little 1.5 hours to download and parse the > returns from the server on the emulator (which takes a few seconds on > either device), so I gave up on further debug with the emulator at that > point. > > This is the only difference between the two operations. The listView is > the same, the adapter is the same, the AsyncTask is the same. The number > of rows returned is different - there are about 2000 english words, and a > little over 3000 words in the other language. I don't think this explains > the vast difference in performance. > > I took the query out of the AsyncTask to see if I could get some more > debug info, and did get an ANR: > > at android.database.sqlite.SQLiteQuery.native_fill_window(Native Method) > at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:73) > at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:287) > at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:268) > at > com.whatever.adapters.WordListAdapter.getCount(WordListAdapter.java:39) > > I rewrote the adapter's getCount method to return a cached count > (determined during instantiation). After, I didn't get an ANR again, but > otherwise the performance was not improved and the query still took around > 30 seconds. > > I'm totally at a loss. As mentioned, everything but the queries is > identical. And on the Galaxy SII, there is no problem at all - less than a > second to populate the ListView, even under abuse (touching the button that > launches the request as fast as I could). > > At this point, I'm wondering if it'd be better to abandon SQLite entirely, > and using a Java model to manage data. Assuming I can't get the SQLite > version to perform reasonably, what could I expect in terms of performance > using Collections (with the number of entries mentioned above), when I need > to do a search for example (which I imaging would require iterating over > the whole thing on each key event). > > Any advice? > > TYIA. > > -- > 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 -- Dianne Hackborn Android framework engineer hack...@android.com Note: please don't send private questions to me, as I don't have time to provide private support, and so won't reply to such e-mails. All such questions should be posted on public forums, where I and others can see and answer them. -- 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.c
[android-developers] Android, SQLite, massive (5000%+) loss of performance on a certain query, on a certain device
I'm rewriting a simple translation app with a SQLite db. There is an extreme hit to performance between two queries, but only on certain devices. One query lists the english words in a ListView, the other lists the secondary language in a list view. The data is structured differently, and is from a remote server. Both are single SQL statements, run via db.rawQuery. Both use AsyncTask to keep heavy lifting in another thread. On both devices, the "english" query returns almost instantly (less than 1 second, every time): return db.rawQuery("select _id as id, english as label from english_words order by english collate nocase", null); On one device, the "secondary_langauge" query returns almost instantly as well. No problem there, ever. This is a Samsung Galaxy SII. On another device (Samsung Nexus S), this query takes around 30 seconds. This query has some joins, as follows: return db.rawQuery("select definitions._id as id, secondary_language as label from english_words join definition_bridge on english_words._id=definition_bridge.word_id join definitions on definitions._id=definition_bridge.definition_id order by secondary_language", null); I ran it in the emulator once, and got the same result as the Nexus S (the 30 second hang). It took a little 1.5 hours to download and parse the returns from the server on the emulator (which takes a few seconds on either device), so I gave up on further debug with the emulator at that point. This is the only difference between the two operations. The listView is the same, the adapter is the same, the AsyncTask is the same. The number of rows returned is different - there are about 2000 english words, and a little over 3000 words in the other language. I don't think this explains the vast difference in performance. I took the query out of the AsyncTask to see if I could get some more debug info, and did get an ANR: at android.database.sqlite.SQLiteQuery.native_fill_window(Native Method) at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:73) at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:287) at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:268) at com.whatever.adapters.WordListAdapter.getCount(WordListAdapter.java:39) I rewrote the adapter's getCount method to return a cached count (determined during instantiation). After, I didn't get an ANR again, but otherwise the performance was not improved and the query still took around 30 seconds. I'm totally at a loss. As mentioned, everything but the queries is identical. And on the Galaxy SII, there is no problem at all - less than a second to populate the ListView, even under abuse (touching the button that launches the request as fast as I could). At this point, I'm wondering if it'd be better to abandon SQLite entirely, and using a Java model to manage data. Assuming I can't get the SQLite version to perform reasonably, what could I expect in terms of performance using Collections (with the number of entries mentioned above), when I need to do a search for example (which I imaging would require iterating over the whole thing on each key event). Any advice? TYIA. -- 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] Android SQLite Example Project
I have created an open source Google Android App, hosted on Google Projects to help new Android Developers get up to speed with how to do some various development tasks, and provide some reusable code that all developers may use. The Google Project URL is code.google.com/p/my-android-sqlite-example- project/. I am looking for some constructive feedback, suggestions on how improve the code, to help ensure good solutions are being provided. The audience for this project is intended to be software developers who are new to developing Google Android Applications, and also for those developers that are searching for a clean and simple SQLiteOpenHelper Database Adapter implementation. I have provided some documentation in the downloads section of this project, to help you get started, including the full downloadable Eclipse Android Project source file in a .ZIP file format, and an executable Android .APK file, built from this project (refer to the description for installation instructions). The project consists of a simple list activity for demonstrating how to do different things in Android, including: - A SQLiteOpenHelper database adapter, how to properly open and close it, etc. - A custom AlertDialog?.Builder message box - Logging messages, warnings, errors, etc. - How to export SQLite database data - How to sort data through a custom Preference Activity - How to clean up objects in onDestroy - How to make menu icons - How to add animation to buttons - How to create User Interfaces - How to use the string.xml file to populate labels, text, etc. - How to create a date-select button - And more. Have fun, this is meant to be educational, and constructive feedback is appreciated. Thank you. -- 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] android SQLite query
> > I am facing a problem to access IP camera directly in android.I got byte > streams of video file by using http request I am facing a problem How to > convert live stream to video file . Anybody knows it please replay me.. > Thanks -- 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] android SQLite query
You will need to learn SQL. Getting all data from a table goes like SELECT * FROM tablename That's what needs to go into the SQL string field. -- 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] android SQLite query
hello! i don't know how to view my DB file as list. there's an SQLite data base file prepared and already passed to my comp from droid device. and has been opened in sqlite browser. the browser has a window where :'SQL string' textbox and 'SQL quiary' button. there are three column in my DB: _Id,time_float, acceleration_float . so i m gessing i must put some SQL instruction in mentioned text field and tap SQL quiary button to get purpose list of fields to visualize . but i don't know SQL well. so that the question is clear: what a string? -- 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] Android + SQLite - Brazilian student
hi i am a student from Brazil and I am studying android. I would ask if you could share a material on Android and sqlite (CRUD). Thank you all -- 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] Android sqlite database file
u easily get exact db file . use these steps On Fri, Jun 17, 2011 at 3:04 PM, Ankit Kasliwal wrote: > Hi, >Ok. > > > On Fri, Jun 17, 2011 at 3:00 PM, nageswara rao rajana < > nagu.raj...@gmail.com> wrote: > >> Hi Ankit, >> >> Thanks for the links. I will work on it and i will get back >> to you, if i get any issues. >> >> Thanking you, >> Nagu. >> >> -- >> 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 >> > > > > -- > > Thanks and Regards, > > Ankit Kasliwal > kasliwalankit2...@gmail.com > +91-9300-940-136 > > -- > 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 > -- 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] Android sqlite database file
Hi, Ok. On Fri, Jun 17, 2011 at 3:00 PM, nageswara rao rajana wrote: > Hi Ankit, > > Thanks for the links. I will work on it and i will get back > to you, if i get any issues. > > Thanking you, > Nagu. > > -- > 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 > -- Thanks and Regards, Ankit Kasliwal kasliwalankit2...@gmail.com +91-9300-940-136 -- 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] Android sqlite database file
Hi Ankit, Thanks for the links. I will work on it and i will get back to you, if i get any issues. Thanking you, Nagu. -- 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] Android sqlite database file
Hi, i think you find you ans on this site http://pa.rezendi.com/2010/04/sqlite-on-android.html http://stackoverflow.com/questions/1209469/storing-android-application-data-on-sd-card On Fri, Jun 17, 2011 at 2:50 PM, nageswara rao rajana wrote: > Hi, >>> >> > I want to extract db file from device. > > Thanking you, > Nagu. > > -- > 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 > -- Thanks and Regards, Ankit Kasliwal kasliwalankit2...@gmail.com +91-9300-940-136 -- 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] Android sqlite database file
> > Hi, >> > I want to extract db file from device. Thanking you, Nagu. -- 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] Android sqlite database file
HI, its simple to check ur all data. use sqlite database browser. download it from its website. Steps to find the sqlite data from sdcard. - Go to DDMS-file explorer - click on mnt- Sdcard - choose ur desired project's data - and pull from sdcard(this option is in the corner of file explorer) - store ur database file in hard drive - Browse it using sqlite database browser. - :) - On Fri, Jun 17, 2011 at 2:29 PM, nageswara rao rajana wrote: > Hi, > > > I implemented an database application on device. But i am unable to > find where the database file is stored on sdcard. > Please any one help me. > > Thanking you, > Nagu. > > -- > 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 -- 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] Android sqlite database file
Hi, I implemented an database application on device. But i am unable to find where the database file is stored on sdcard. Please any one help me. Thanking you, Nagu. -- 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] Android sqlite and multithreading
Hi everybody! I would like to know better ways to use SQLite in Android. I've got some UI Activities and background service (Android service which runs some AsyncTasks to change the data, they also can use database concurrently ) First I tried to use all this stuff as is, just open cursor in ListActivity, show data. My AsyncTasks were doing some changes to my local sqlite db. So, sometimes I was getting: DATABASE IS LOCKED exception. Then I tried to synchronize all my update/insert/delete parts of my AsyncTasks. So my app got better, almost everything was working wonderfull. But, some users were sending error messages with all the same exception. So, I finished with such solution for now: I do not hold any Cursors open. I open it, read to my arraylists, then close. So I also tried to synchronize read methods. Now everything is working fine, no "DATABASE IS LOCKED" exception. But, I don't like such solution, first of all because of performance issues. Opening cursor and getting all data from it to arraylist is not very cool and fast. May be I missed something?! How do you use sqlite in Android? -- 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] Android sqlite unable open database problem
Hi, im doing debug with my mobile. My code works in the past, but now is not working and i dont know why i cant create or open an existing database. Here is my code: http://stackoverflow.com/questions/2163100/creating-database-in-android Could you help me about how to solve this problem? I read that there is an option from the command line: emulator -wipe-data but since im not using an emulator to debug the application (im using my own android mobile), im lost about how to solve the problem. Thanks in advance. Best Regards. Josema. -- 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] Android - SQLite Database help
I am new to android and I am getting follwing errors in my compilation.. //Cannot make static reference to a non-static method //Cannot make static reference to non-static fields please help! here's the code.. package database.create; import android.app.Activity; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.SQLException; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.util.Log; public class DBAdapter extends Activity { public static final String KEY_STDID = "_ID"; public static final String KEY_FNAMEID = "FNAME"; public static final String KEY_LNAMEID = "LNAME"; public static final String KEY_DOBID = "DOB"; public static final String KEY_HEIGHTID = "HEIGHT"; public static final String KEY_WEIGHTID = "WEIGHT"; private static final String TAG = "DBAdapter"; private static final String DATABASE_NAME = "HEALTHCARE"; private static final String DATABASE_TABLE = "PHYSICAL_FITNESS"; private static final int DATABASE_VERSION = 1; public static final String DATABASE_CREATE = "create table PHYSICAL_FITNESS (_id integer primary key," + "fname text not null, lname text not null," + " d.o.b integer not null, height integer not null," + "weight integer not null); "; private final Context context; private DatabaseHelper DBHelper; private SQLiteDatabase db; public DBAdapter(Context ctx) { this.context = ctx; DBHelper = new DatabaseHelper(context); } private static class DatabaseHelper extends SQLiteOpenHelper { DatabaseHelper (Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } public void onCreate(SQLiteDatabase db) { db.execSQL(DATABASE_CREATE); } public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data"); db.execSQL("DROP TABLE IF EXISTS physical_fitness"); onCreate(db); } public DatabaseHelper open() throws SQLException { db = DBHelper.getWritableDatabase(); return this; } public void close() { DBHelper.close(); } public long insertphysical_fitness (String _id, String FNAME, String LNAME, String DOB, String HEIGHT, String WEIGHT) { ContentValues initialvalues = new ContentValues(); initialvalues.put(KEY_STDID,_id); initialvalues.put(KEY_FNAMEID, FNAME); initialvalues.put(KEY_LNAMEID, LNAME); initialvalues.put(KEY_DOBID, DOB); initialvalues.put(KEY_HEIGHTID, HEIGHT); initialvalues.put(KEY_WEIGHTID, WEIGHT); return db.insert(DATABASE_TABLE, null, initialvalues); } public boolean deleteTitle(long rowID){ return db.delete(DATABASE_TABLE, KEY_STDID + "=" + rowID, null) > 0; } public Cursor getAllTitles() { return db.query(DATABASE_TABLE, new String[] { KEY_STDID, KEY_FNAMEID, KEY_LNAMEID, KEY_DOBID, KEY_HEIGHTID, KEY_WEIGHTID}, null, null, null, null, null); } //---retrieves a particular titl