Windows Phone SQLCE performance is very poor

Question

I am writing this topic since I have been struggling with this problem for three whole days!

Basically, I have a program that collects a large CSV file and uses this as an input to the local SQLCE database. For each line in this CSV file (which represents an object, it can be called a “dog”), I need to know if this dog exists in the database. If it already exists, do not add it to the database. If it does not exist, add a new row to the database.

The problem is that each query takes about 60 milliseconds (at the beginning when the database is empty) and it reaches about 80 ms when the database is about 1000 rows. When I need to go through 1000 lines (which, in my opinion, is a bit), it takes about 70,000 ms = 1 minute and 10 seconds (just to check if the database is updated), too slow! Given that this amount is likely to someday be more than 10,000 rows, I cannot expect my user to wait more than 10 minutes until his database is synchronized. Instead, I tried using a compiled query, but that did not improve performance.

The field that im is looking for is a string (which is the primary key) and is indexed.

If necessary, I can update this thread with code so you can see what I'm doing.

Answer 1

SQL CE on Windows Phone is not the fastest creature, but you can optimize it:

This article describes a number of things you can do: WP7 Local DB Best Practices

They also provide a WP7 project that can be downloaded so you can play with the code.

In addition to this article, I would suggest changing your PK from string to int; rows take up more space than ints, so your index will be larger and take longer to load from isolated storage. Of course, in SQL Server, searching for strings is slower than searching in ints / longs.