Performing an insert OR upgrade (upsert) on a sql server compact server

Question

I have a C # project that uses sqlserver compact edition and an entity structure to access data. I need to insert or update a large number of lines, more than 5000 or more, in db, so if the key exists, update the record if you do not insert it. I cannot find a way to do this with the compact version and EF with terrible performance, i.e. Take 2 minutes plus on the i7 core. I tried to find the record to see if it exists, and then insert, if not, or update if it is, the search is the killer. I tried to compile the search query and it only improved a bit. Another thing I tried is to insert the record into a try catch, and if it is not updated, but it forces me to save the changes in each record to get an exception, and not at the end, which is a performance killer. Obviously, I cannot use stored procedures, since this is a compact version. In addition, I looked at just executing t-sql directly anyway on db, but the lack of process instructions in a compact seems to not allow this. I searched for the world from ideas. I really wanted to use compact, if I could, to express the benefits of deployment and the ability to prevent the user from digging around db. Any suggestions would be appreciated.

thanks

Answer 1

Perhaps you can get the result you are looking for using simple queries. Let's say the table you want to insert or update looks like this:

TABLE original id integer, value char(100) 

first you can create a temporary table with new values ​​(you can use SELECT INTO or other ways to create it)

 TABLE temp id integer, value char(100) 

now you need to do two things, update the lines in the original, and then insert 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) 

Answer 2

When we use SQL CE (and SQL Express Express), we always call the update first and then call the insert if udate gives the number of rows at 0. It is very simple to implement and does not require expensice try..catch blocks for the control flow.

Answer 3

Given your expression about the problem, I'm going to suggest that this software assumes a relatively muddy environment. Do you think you need to complete the sqlce definition task and do it yourself? Essentially, take a sorted list of all identifiers (keys?) From the corresponding table and check each object key against this list before its turn for insertion?

This leads to some assumptions, which would be bad news with a typical DB, but that you can probably go into sqlce. For example, it assumes that the rows will not be inserted or significantly changed by another user during this insertion.

If the list of keys is too long for reasonable storage in memory for such a check, I am afraid that I will say that sqlce simply cannot be the right tool to work. :(

Answer 4

I'm not sure if this is possible or not, since I did not use the Entity Framework, but you tried to start the update first and checked rowcount - insert if rows were not updated? This can be faster than eliminating exceptions. Generally, it is bad practice to use exceptions for the control flow and often slows things down significantly.

If you can write SQL directly, the fastest way to do this is to have all the data in a temporary table, then update what exists and insert the residuals (as in the example of Andrea Bertani above). You should get slightly better results using the left join in the source table in the selection in your insert and excluding any rows with values ​​from the source table that are not null:

 INSERT INTO original SELECT * FROM temp LEFT JOIN original ON original.id = temp.id WHERE original.id IS NULL 

Answer 5

I would recommend using SqlCeResultSet directly. You lose a pleasant EF type of security, but performance is incredibly fast. We switched from ADO.NET 2.0 of type TypeDataSets to SqlCeResultSet and SqlCeDataReader and saw 20-50 times more speed.

Answer 6

See SqlCeResultSet. For the .NETCF project, I removed almost all of the sql code in favor of this class. Just find "SqlCeResultSet" here and msdn.

Short review:

• Open the result file. If you need a search (to check for existence), you will need to provide an index for the result set.

• Search in the result set and read to check if the string is found. This is very fast even on tables with tens of thousands of rows (because the search uses an index).

• Insert or update a record (see SqlCeResultSet.NewRecord).

---

We have successfully developed a project with a sqlce database with a main product table with more than 65,000 rows (read / write with 4 indexes).

Answer 7

The compact version of SQL Server develops quite early in this phase. In addition, depending on your device, accessing a memory disk may be quite slow, and the overhead of security like SQLCE plus.NET is quite intense. It works best with a fairly static data warehouse.

I suggest you use the lighter weight API or consider SQLite.