TechDays and RAPI speed

TechDays in Lisbon finished today and it was a blast! I had the opportunity to deliver two presentations (SQL Mobile and .NET CF Performance) and the place was always crowded, but not my presentations, alas. There were several parallel tracks and it was not uncommon to have participants standing in aisles or sitting on the floor... Congratulations Microsoft Portugal!

In one of the recesses, I was approached by one of my customers complaining about DesktopSqlCe performance when inserting data in bulk. After comparing the insertion performance of Data Port Wizard, DekstopSqlCe was an order of magnitude slower...

My customer's code also needed to be optimized, but that was not the root of the problem. It was in my code. How could that be if both products share most of the base code? On one hand DPW was blasting 11,000 rows in less than 30 seconds over RAPI, while DesktopSqlCe was performing in the tens of minutes range. But wait, I thought, I've seen this before.

When DPW was being developed, I soon realized that synchronous messaging was very slow over RAPI. If after inserting a row in a table (even if the cursor remains open) and you wait for the device to send an acknowledgement message, then you are going to be very slow. So slow as to justify the tens of minutes scenario.

To solve this, DPW uses an asynchronous method of communicating and that is why it does not stop when it finds an error - all errors are reported at the end and are collected asynchronously. This is fast, very fast indeed.

When I wrote DesktopSqlCe I wrongly assumed that my customers would make occasional and rare direct table inserts and updates, so inserts and updates were being delivered in a synchronous fashion, i.e. waiting for a confirmation after insertion or update. This situation is now solved by adding a new attribute to the SqlCeRowset class (similar to the CF 2.0 new SqlCeResultSet class): ReportError. This attribute is of type SqlCeReportError and has two values: Sync (default) and Async. Now you know why.