[Mono-dev] Mono.Data.Sqlite performance

[David A Knight]

On 27 Jan 2012, at 17:27, Robert Jordan wrote:

> On 27.01.2012 15:54, David A Knight wrote:
>> I have been looking at changing an iOS app over to using Monotouch
>> and run into an issue with Sqlite performance.  Writing some test
>> code (not running on a device) that just opens a database and does a
>> number of inserts (in a transaction) the performance difference
>> between C (using Core Foundation for unicode strings) and C# is
>> massive.  ~37k compared to ~14k inserts per second under MacOSX
>> (including the iOS simulator) / Linux.
>> 
>> Looking at Mono.Data.Sqlite I can't see any code that would cause
>> this and so have come to the conclusion (wrongly or rightly) that the
>> difference in performance is down to Sqlite functions being called
>> via P/Invoke and the overhead it causes.  Is there anything that can
>> be done to reduce this overhead that isn't already in place?  Am I
>> wrong that the difference is caused by the P/Invoke?
> 
> It's hard to say w/out a test case.
> 
> If you did not specify SqliteConnection's "UseUTF16Encoding=true"
> option, the p/invoke layer would have to marshal strings  back and
> forth between UTF-8 and UTF-16. This might add substantial overhead
> to apps that need 37k transactions per second.


UTF-16, hence the use of Core Foundation for the C version.  Test code attached.  Timing is only of the inserts, not including the commit.  exception handling deliberately left out.  Loop times without inserts occurring, but still building the parameter string values are as good as identical between C and C#

-------------- next part --------------
A non-text attachment was scrubbed...
Name: speedtest.cs
Type: application/octet-stream
Size: 4293 bytes
Desc: not available
URL: <http://lists.ximian.com/pipermail/mono-devel-list/attachments/20120127/b39ccbea/attachment.obj>
-------------- next part --------------


It isn't so much needs 37k inserts per second, just that the drop in performance would look very bad.  On a device the figure (for C) is obviously lower, but given a similar drop in speed it isn't an acceptable user experience for me unfortunately.



David