Numerous massive SELECTs across multiple functions; eventually db.transaction dies #288
Comments
Please explain what you mean by "multiple functions".
If you are having trouble with this plugin I recommend you try using the HTML5/Web SQL API first, i.e. just open your database using Also, please make sure you are using the latest version of the Cordova framework. You may want to take a quick look at the discussion in #190. If you continue to have trouble with this plugin, try to isolate the smallest test program you can to reproduce the issue and either post it or send it by e-mail to [email protected].
This is the first time I have heard of a "memory leak on the INSERTs". If you see such a memory leak please report it in a separate issue, along with a test program that can help us reproduce the problem. |
|
On iOS, I'm running code on a large dataset (in one file). Thousands of rows of data; millions of data to search through. Many small queries; a few large ones. These traverse > 20 functions. All callbacks are nested. Please explain what you mean by "multiple functions". We’re uploading records of data fields. The largest project I have has records of ~6300 fields. I download a copy of the record, compare, and perhaps upload changed or new fields in a JSON. What complicates thing more is that new data is handled first; modified data is handled second. I use JavaScript functions to pass control around, but each function contains its own set of database calls. At some point (the same point each time), I open a new DB session, then I open a new db.transacation, then the app stops. 2015-06-17 09:33:43.706 REDCap[7115:1817843] database already open: redcap15 2015-06-17 09:33:43.707 REDCap[7115:1817843] DB opened: redcap15 At some point, the db.transaction command just dies. I have tried to trace, but I cannot figure out how. If I remove one db.transaction, the very next one in the very next function dies. Any ideas on how I can proceed with debugging? If you are having trouble with this plugin I recommend you try using the HTML5/Web SQL API first, i.e. just open your database using window.openDatabase(). Also, please make sure you are using the latest version of the Cordova framework. You may want to take a quick look at the discussion in #190#190. If you continue to have trouble with this plugin, try to isolate the smallest test program you can to reproduce the issue and either post it or send it by e-mail to [email protected]:[email protected]. I’ll try to see if I can create a scenario with recursive function calls that open the database and do something. I cannot yet test this on Android due the memory leak on the INSERTs. This is the first time I have heard of a "memory leak on the INSERTs". If you see such a memory leak please report it in a separate issue, along with a test program that can help us reproduce the problem. — |
|
I think it’s getting locked out. Is there any way to check this? Also, is there any way to force a connection? |
|
I am encountering this in two situations. One is with SQLitePlugin calls only. The other is after a call to another plugin that I wrote to circumvent the Android mass insert issue. It seems to work, but the database is locked afterwards. (I am calling dispose.) Not sure if there’s a good way to see if there’s a locking condition present – in either SQLiteConnection or in SQLitePlugin. Also, I could not reproduce the scenario by multiple recursive calls on a test project. It went something like this, if you’ll excuse the auto-caps. Function fxn() { Fxn(); |
|
@scottjpearson did you find a solution or at least a workaround for this? |
|
Yes. It turns out that the database was just executing an INNER JOIN very slowly on a huge dataset. Transposing into two queries made things much quicker. The breakthrough was discovered by putting in a comment every time the database stepped through execution. Thanks! From: Chris Brody <[email protected]mailto:[email protected]> @scottjpearsonhttps://github.com/scottjpearson did you find a solution or at least a workaround for this? — |
Thanks, this will be documented when I get a chance. Did you find a solution or workaround for #18 as well? |
|
No. Honestly, that is crippling our Android app for big data sets. That’s not a huge deal for our medical research teams in the US, but it’s a much bigger deal for global health teams like Guatemala, Kenya, Cameroon, etc. Many of these countries are hoping to move their countries’ medical research data onto our tablet software exclusively. Because iOS is not present in their countries and because of this memory leak, that’s not really possible. Not to be too dramatic, but if you’re able to make some progress, you’ll be able to help a lot of people who could use a hand up. From: Chris Brody <[email protected]mailto:[email protected]> It turns out that the database was just executing an INNER JOIN very slowly on a huge dataset. Transposing into two queries made things much quicker. Thanks, this will be documented when I get a chance. Did you find a solution or workaround for #18#18 as well? |
|
Stupid question: did you guys try just using the Web SQL API in the webview? Newer versions of Android have raised the limits, especially in the WebView that is displayed by Cordova: http://stackoverflow.com/questions/17266596/no-prompt-to-increase-web-sql-limit-in-android-native-browser |
|
No, we haven’t looked into this. Would it help? And what are the limits? We have 2-million data points for a data set as our example big project. The data values are encrypted as well. Is there an MB limit? I chose SQLite because I liked how storage was only limited by hard disk space. From: Chris Brody <[email protected]mailto:[email protected]> Stupid question: did you guys try just using the Web SQL API in the webview? Newer versions of Android have raised the limits, especially in the WebView that is displayed by Cordova: http://stackoverflow.com/questions/17266596/no-prompt-to-increase-web-sql-limit-in-android-native-browser — |
|
It looks like on the newer Android WebView the database can go up to 50 MB. If you can try it with the Web SQL API (on Android only), as a temporary workaround, and see if it can help you until we find a better solution? Also, if you need encryption did you see the sqlcipher version at https://github.com/litehelpers/Cordova-sqlcipher-adapter? |
|
SQLCipher kept causing the filesystem to fail upon initial load, so I wrote my own encryption scheme. The code is complicated enough and the differences, though slight, are great enough that a complete rewrite should only be done as a last resort. Since both backends use SQLite, could we just use Web SQL for our massive inserts on Android? From: Chris Brody <[email protected]mailto:[email protected]> It looks like on the newer Android WebView the database can go up to 50 MB. If you can try it with the Web SQL API (on Android only), as a temporary workaround, and see if it can help you until we find a better solution? Also, if you need encryption did you see the sqlcipher version at https://github.com/litehelpers/Cordova-sqlcipher-adapter? — |
Yes, but make sure you test it well. I suggest you test it with a multiple of the size you actually need. |
On iOS, I'm running code on a large dataset (in one file). Thousands of rows of data; millions of data to search through. Many small queries; a few large ones. These traverse > 20 functions. All callbacks are nested.
On smaller datasets, everything works.
At some point, the db.transaction command just dies. I have tried to trace, but I cannot figure out how. If I remove one db.transaction, the very next one in the very next function dies.
Any ideas on how I can proceed with debugging?
I cannot yet test this on Android due the memory leak on the INSERTs.
The text was updated successfully, but these errors were encountered: