Windows Phone Local Database tip: Exploring INSERT performance–5 power tweaks

Josué Yeray Julián, (blog in Spanish here), has done some interesting tests on INSERT performance, both on the emulator (like I started doing in my previous blog post) and on two real devices, an HTC Mazaa and Nokia Lumia 800. (Sadly, I do not own a real device – yet!)

In summary,  the following parameters can improve performance for doing INSERTs of 500.000 rows:

1: Set “Max Database Size” on your connection string  to an expected size in advance (default is 32 MB, max is 512 MB on Windows Phone)

2: Increase “Max Buffer Size” to a value higher than the default of 384 KB. On the tested real devices the max of 4096 worked best.

3: Run the INSERT process in a background thread.

4: Remove any Version columns (if only INSERTSs matter, not UPDATEs)

5: Use InsertAllOnSubmit instead of InsertOnSubmit

These tweaks reduced the total processing time on the Nokia Lumia 800 from 29:20 to 16:04!

But - keep in mind that you can use Database first development with the SQL Server Compact Toolbox add-in and then include the database with your application package. You can also use the SqlCeBlukCopy API to insert 1.000.000 rows (twice as many as tested) in 6 seconds on the desktop!

You can read the Microsoft Translator versions of his 2 blog posts here and here.