Running slow on metro

[Ari H-C]

Hello, I'm looking to use this project in my windows 8 metro app, due to it being compatible with all processors, but it seems to be running incredibly slow (as much as 10x by comparison to sqlite-net (https://github.com/praeclarum/sqlite-net) in conjunction with the sqlite x64/x86 dlls. From what I can tell, this may due to a large number of "FileExists" checks, which require forcing synchronicity on to an async task (which is good because I want the synchronous behavior, but presumably might take a bit of extra time) and catching an error. Most of these calls seem to be related to the journaling feature. As I am only ever reading from my databases, I don't need this, and could even work with a readonly connection if that would indeed be faster. In general there seem to be a couple other async tasks being waited upon, including some pure time delays (though I have no idea if any of these are contributing to the slowness). Another cause may be that I am making several single row lookups instead of getting several rows with one call, due to the nature of my data handling. I have tried keeping the connection open and using the same one for all the calls, but it seems to have no noticeable improvement.

Has any one else had such issues? Are there any thoughts on how I can improve the performance? As I posted in a thread yesterday, I think for a start readonly/no journaling might be helpful, but I can't figure out how to make the database behave as such.

[Ari H-C]

I found my problem; the ORDER BY operation is apparently extremely expensive. As soon as I took it out things went more smoothly. It would still be nice to somehow reduce the file checking though, as it probably could still improve performance some.

[Michael Schoneman]

I just sent Noah a patch for WinRT/Metro.  It uses P/Invoke to call the same functions in Kernel32.dll that the base C SQLite uses.  In my admittedly limited testing it seems faster.

-Michael O. Schoneman

[tempy]

Have you tried running it in release mode? I've also found that cs-sqlite is significantly faster in release than in debug for some operations.

[Noah Hart]

Can you send the list of compiler options you are using?  Some of them have significant overhead and are intended only for use in DEBUG mode. 

Two other items:
1) Also, opening the database in readonly mode will help eliminate the locking/sync
2) Michael Schoneman has produced a number of patches as well as a RT specific file,  os_winrt.cs, that will help

Thanks,

Noah