I have c# project that is using sqlserver compact edition and entity framework for data access. I need to add or update at least 5000 rows to the database, thus if the key is present, update the record; else, add it. With the small edition and EF, I am unable to do this task without it performing poorly, taking more over two minutes on a core i7 pc. I've tried looking for the record to check if it already exists, adding it if it does not, or updating it if it does, but the search is the problem. I've tried putting the search query together, but the improvement was little. Another thing ive tried is inserting the record in a try catch and if it fails update, but that forces me to savechanges on every record to get the exception as opposed to at the end which is a performance killer. Naturally, because it is the small edition, I cannot utilize saved procedures. Additionally, I considered simply running T-SQL directly on the database somehow, but the absence of process statements in compact appears to rule that out. I've looked all around the globe but have run out of ideas. If I may say so, the deployment advantages and the ability to stop users from poking about in the database were the main reasons I really wanted to adopt compact. Any ideas would be much appreciated.
Using straightforward inquiries could help you get the answer you're looking for. Consider the following if you wish to change or add data to this table:
TABLE original id integer, value char(100)
Initially, a temporary table with the updated values may be made (you can use a SELECT INTO or other ways to create it)
TABLE temp id integer, value char(100)
Currently, you must change the original rows and then enter the new values.
UPDATE original SET original.value = temp.value FROM original, temp WHERE original.id = temp.id INSERT INTO original SELECT * from temp WHERE temp.id not IN (select o.id from original o)
When using SQL CE (and SQL 2005 Express, for that matter), we always perform an update first, and if the update returns a row count of 0, we then call an insert. This is incredibly inexpensive to perform and very easy to apply. To regulate flow, use try..catch blocks.