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!