Poor insert performance

[remk]

We use this plugin on windows phone 8.1 (js project, non silverlight). The insert performance are poor (more than 10 times slower than on android (using native websql ) and ios ( using brodysoft plugin )).
Our database consists of a key/value store where the value is a Json blob.
It takes 40 second to save 700 record on a nokia 925. Grouping them by transaction of 100 record.
By transaction of 10 elements it take 65seconds. And by transaction of 400 it take less than 20 second but the db keep using all the cpu ressource after the success callback of the last transaction is called. Making other io failed (ajax queries).

I've tried grouping the insert using INSERT ... SELECT ... UNION ALL SELECT ...
without much improvement.

During this whole time the ui is frozen.

Is there some way to improve insert performance ?
by configuration maybe ? can we tune the sqlitedb with this plugin ?

Is this plugin is useable into webworkers to a least keep the ui responsive (i tried without success) ?

Thanks

[guillaumebadin]

👍 I have the same problem performance are very poor !!!

[sgrebnov]

Thank you for reporting this, we are instigating the problem...

[lpessoa]

Any news on this one?

[guillaumebadin]

Effectively, we have always the same problem.

[EionRobb]

We improve performance in our version of the plugin by specifying a few SQLite pragma options:

PRAGMA temp_store=MEMORY;
PRAGMA synchronous=OFF;
PRAGMA page_size=4096;

[guillaumebadin]

Thank you very much we will test this and we will get back to you.

[lpessoa]

Used EionRobb's approach. Worked like a charm...

[remk]

How do you execute the pragma statements ? I execute them from my js file just after opening the db.
The commands are executed without problems.
Only for "The PRAGMA synchronous=OFF;" which freeze the application.

Do you have any insight ?

[lpessoa]

If you use the EionRobb's version of the plugin it is already setup by default for you.

[remk]

ok, i modified the ConnectToDb method in SQLiteProxy.cs like this : https://gist.github.com/remk/0e4a6f20cf8aa2f3febe

The perfomances are definitely better but not stellar.

[remk]

I tried EionRobb's plugin without success. I think it's because our project target the cordova 'windows' platform and not the 'wp8' one.
I really would like to make it work, the original sqlite seems to have much better insert performance than the c#sqlite used in the msopentech plugin.
Do you have any idea ?

For the msopentech plugin maintainers: would it be possible to replace the c#sqlite database with the original one ?

[guillaumebadin]

+1 @sgrebnov What is the strategy from microsoft ? If we want have good crosspatform application on windows we must have a good SQLite plugin no ?

[lpessoa]

@remk i am using it on 'windows' platform using cordova and not 'wp8'. Would be glad to help you on setting it up.

[remk]

@lpessoa Thanks that's nice of you.
My last try was :

• add existing project SQLite3.vcxproj to my solution
• convert it to 8.1 project
• move the destructors to private in the *.h files, otherwise the project do no build.
• reference the SQLite project. i have a warning that the project doesn't correspond.
• load the websql.js file in my index.html

What have i done wrong ?

[lpessoa]

@remk that is basically it.. the only difference is that i've changed the destructors in the header files to virtual and not private.

What kind of errors are u getting?

[remk]

@lpessoa in the websql.js l have this error:
SCRIPT126: Unhandled exception at line 99, column 5 in ms-appx://ubilab/www/websql.js
0x8007007e - JavaScript runtime error: The specified module could not be found.
File: websql.js, Line: 99, Column: 5

at this line :
storageParam.db = new SQLite3.Database(storageParam.dbName);

it seems like the js code couldn't interact with the cpp interface. Did i miss something ?

additionally i can build sqlite in debug mode but in release mode , i have this error :
2>c:\users\remi\documents\projects\sql\sqlite\sqlite3\sqlite3.c(40640): error C4703: potentially uninitialized local pointer variable 'p' used
2>c:\users\remi\documents\projects\sql\sqlite\sqlite3\sqlite3.c(44496): error C4703: potentially uninitialized local pointer variable 'p' used
2>c:\users\remi\documents\projects\sql\sqlite\sqlite3\sqlite3.c(44513): error C4703: potentially uninitialized local pointer variable 'p' used
2>c:\users\remi\documents\projects\sql\sqlite\sqlite3\sqlite3.c(45416): error C4703: potentially uninitialized local pointer variable 'p' used
2>LINK : fatal error LNK1257: code generation failed

[sgrebnov]

Hi guys, thank you a lot for investigating this issue - I've just pushed a fix based on additional PRAGMA options.
c0b74d1

[EionRobb]

@sgrebnov
PRAGMA ignore_check_constraints=on; disables uniqueness and primary key checks on insert and will cause issues in a database

PRAGMA journal_mode=OFF; disables the transaction journal which can a) cause db corruption if a write is half way through when the app is closed and b) prevents the rollback of a transaction if it were to fail (which is what the whole point of the db.transaction() call is for)

I suggest you remove both of those dangerous pragma options

[sgrebnov]

updated, thank you @EionRobb

[DibranMulder]

Hi guys we created an async version of this plugin, which addresses a lot of performance issues. Please take a look at the repo

[ISYSMichael]

I still have this issue. It's so bad that a process that takes 10 seconds in Android and iOS takes several minutes on WP8.

[DibranMulder]

@ISYSMichael Try this plugin, its async and performs better.

[ISYSMichael]

That only seems to be for Windows, not WP8. Going by the plugin.xml.

[DibranMulder]

@ISYSMichael Windows is the "new" universal app project. This includes both Windows and Windows Phone.