Bug 58342 - Massive drop in performance for sqlite-net somewhere on the way from 10.8 to 10.10
Summary: Massive drop in performance for sqlite-net somewhere on the way from 10.8 to ...
Alias: None
Product: iOS
Classification: Xamarin
Component: Xamarin.iOS.dll ()
Version: XI 10.10 (d15-2)
Hardware: Macintosh Mac OS
: --- normal
Target Milestone: Untriaged
Assignee: Bugzilla
Depends on:
Reported: 2017-07-24 19:11 UTC by asp_net
Modified: 2017-09-12 23:28 UTC (History)
3 users (show)

Is this bug a regression?: ---
Last known good build:

Performance comparison (97.56 KB, image/png)
2017-07-24 19:11 UTC, asp_net
Updated performance comparison with on a real device (116.21 KB, image/png)
2017-07-25 10:45 UTC, asp_net

Notice (2018-05-24): bugzilla.xamarin.com is now in read-only mode.

Please join us on Visual Studio Developer Community and in the Xamarin and Mono organizations on GitHub to continue tracking issues. Bugzilla will remain available for reference in read-only mode. We will continue to work on open Bugzilla bugs, copy them to the new locations as needed for follow-up, and add the new items under Related Links.

Our sincere thanks to everyone who has contributed on this bug tracker over the years. Thanks also for your understanding as we make these adjustments and improvements for the future.

Please create a new report on Developer Community or GitHub with your current version information, steps to reproduce, and relevant error messages or log files if you are hitting an issue that looks similar to this resolved bug and you do not yet see a matching new report.

Related Links:

Description asp_net 2017-07-24 19:11:46 UTC
Created attachment 23760 [details]
Performance comparison

My team and I are working on a Xamarin.iOS app which uses a custom fork of sqlite-net for 2,5 years now. While performance always has been okay (not good), we saw a massive drop of speed for any kind of SQL queries, from simple to complex, some weeks ago.

We didn't know what happened, but we hand two versions of the app at hand: a fast one, and a slow one. We first looked for some changes on our side, but there was nothing. The slow one was just a small bug fix release with no substantial changes. Eventually I could track it down: The fast one was built with Xamarin.iOS, the slow one with the latest (stable) bits, Xamarin.iOS

Downgrading to 10.8 is fixing the issue for now, but of course it's not a long-term solution (especially as we also had roll-back C# 7 code).

While looking into sqlite-net, I learned not only that we should replace our own version with the latest bits, but also that even those latest bits show some significant decrease between 10.10 and 10.8: sqlite-net became about (!) 40% (!) slower with the latest (stable) Xamarin.iOS version.

See a matrix of my results attached.

Q: Is this a known issue?

(Unfortunately it would cost me some time to build an isolated failing test solution, time I am short of at the moment. But if it helps and you're unable to reproduce the issue, I can see what I can do.)

PS: Frank A. Krueger, the author of sqlite-net, points in the direction of a reflection regression: https://twitter.com/praeclarum/status/889504759245266944
Comment 1 Sebastien Pouliot 2017-07-24 20:00:02 UTC
I'm not aware of any large performance regression. However the change from Mono's `mcs` to Roslyn `csc` has turned quite a few _interesting_ corner cases behaviours.

Performance of the simulator builds (JIT) is often not representative of the device builds (AOT + LLVM). Do you have numbers for device builds ? (your image only shows numbers for 10.10)

Also if easier (than a test case) can you try to use XI 10.10 with an older Mono (which uses `mcs`) ? That will help us see who should be looking into the regression. Thanks!
Comment 2 asp_net 2017-07-24 20:07:44 UTC
Yep, we also ran into `partial async` with csc ;-). 

That's why I first tried to downgrade Mono (from 5.0 to 4.8) - that didn't change anything. It's very likely (somehow) related to Xamarin.iOS and not Mono, from my point of view.

I will send you some numbers from the same device and 10.8 tomorrow.
Comment 3 asp_net 2017-07-25 10:45:13 UTC
Created attachment 23775 [details]
Updated performance comparison with on a real device
Comment 4 Sebastien Pouliot 2017-07-26 19:34:25 UTC
Interesting. It seems the performance gap is a lot worse on your fork. Can you share a diff (or a description) of the changes ? that would help pinpoint where the issue is located (a reproducible test case is still needed). Thanks!
Comment 5 asp_net 2017-07-31 09:34:12 UTC
I don't think it's about the changes of our fork, those are minor improvements and hacks for some edge-cases we were facing. But, what's really different, is the age.

The new, fast one, is https://github.com/praeclarum/sqlite-net. Our fork is based on https://github.com/oysteinkrog/SQLite.Net-PCL, which hasn't seen any updates recently. To be more specific, I think we're running on https://www.nuget.org/packages/SQLite.Net-PCL/3.0.5 from April 2015.

Unfortunately I don't think it's easy or even possible to compare those two regarding this matter. We also won't investigate here further, we will just migrate to https://github.com/praeclarum/sqlite-net.
Comment 6 Manuel de la Peña [MSFT] 2017-09-11 11:10:10 UTC
As you are not investigating further, should we close the bug and let it be reopened if needed? 

@Sebastien, any input?
Comment 7 asp_net 2017-09-11 11:37:15 UTC
That's on hold at the moment on our side. We're updating sqlite-net in the next weeks and will then try to update again. But until then you can close it, if you're not going after the (smaller) performance gap.
Comment 8 Manuel de la Peña [MSFT] 2017-09-12 23:28:14 UTC
Closing then and feel free to reopen when needed.