Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

A webservice that I call returns a list of data. The data from the webservice is updated periodically, so a call to the webservice done in one hour could return the same data as a call done in an hour. Also, the data is returned based on a start and end date.

We have multiple users that can run the webservice search, and duplicate data is most likely to be returned (especially for historical data). However I don't want to insert this duplicate data in the database.

I've created a db table in which the data is stored (most important columns are)

Id int autoincrement PK  
Date date not null        --The date to which the data set belongs.  
LastUpdate date not null  --The date the data set was last updated.  
UserName varchar(50)      --The name of the user doing the search.

I use sql server 2008 express with c# 4.0 and visual studio 2010. Entity Framework is used as the ORM. If stored procedures could be avoided in the proposed solution, then that will be a plus.

Another way of looking interpreting what I'm asking a solution for is as follows: I have a million unique records in my table. A user does a new search. The search results from the user contains around 300k records of the data that is already in the db. An efficient solution to finding and inserting only the unique records is needed.

A combination of the Date, LastUpdate and UserName makes a record unique.

share|improve this question
1  
You forgot the most important information: what makes a record unique? –  Pieter B Jul 4 '12 at 8:04
    
A combination of the Date, LastUpdate and UserName. –  Eminem Jul 4 '12 at 8:06
    
Downvote with no comment? –  Eminem Jul 5 '12 at 5:03
add comment

9 Answers

Another approach: Add a column to your database, "Hash". It's a SHA-256 or the like--something big enough to be unique. Require that the column be unique.

share|improve this answer
add comment

Easy,

  1. Create a unique name or number for every client that access the db ws.
  2. Create a Table named "Downloaded" and insert a row every time a client access the ws and retrieve rows from the database. This row will contain the name of the client and the main table's row unique id.
  3. The next time you access the ws for data, alter the select to include as o join, only the rows that were not "downloaded" by this user.

Got it?

share|improve this answer
add comment

Well, the obvious solution is to have a unique key on the columns that make the row unique.

A combination of the Date, LastUpdate and UserName makes a record unique.

Alternatively, you might just get rid of the surrogate key and use the above as the primary key (depends upon where else you are using it).

Inserts should be done using Merge, which will allow you to insert the record only when it doesn't already exist.

share|improve this answer
add comment
up vote 0 down vote accepted

I've settled on the following solution.
On my sql server table, I added an index. Check the image for the settings

enter image description here

In visual studio, I updated my eddmx (Entity framework file), but when I tried to insert duplicate records, all I got was another error :

System.Data.Entity.Infrastructure.DbUpdateConcurrencyException: Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.

So I created a simple stored procedure to update the entity. Added the stored procedure to my edmx file and called that from my code and things are working now and duplicates are ignored.

heres a link that helped me find a solution
http://sqlkit.com/2009/06/17/
http://msdn.microsoft.com/en-us/library/x480ck3b.aspx

share|improve this answer
add comment

I love this link. It is so obnoxiously verbose on this topic.

http://asp.net.bigresource.com/in-MVC-and-Entity-Framework-How-to-avoid-duplicate-rows-FDZBD1gIq.html

A unique key would be a simple solution, if you could clean up the duplicates.

share|improve this answer
add comment

Try this:

IF (SELECT TOP(1) text FROM the_table WHERE text='yourtext') <> 'yourtext'
begin
 INSERT INTO the_table (text) VALUES ('yourtext')
end

it queries the DB for yourtext and selects it, if it finds a row, it won't fire, if it doesn't find a row, it will insert.

Not reall well tested though.

share|improve this answer
add comment

Your client could track which rows have changed (created/updated/deleted) and only send those changed rows to the server. With a unique key defined, you can perform inserts for the rows identified by your client as new. This way, you only have to send the changed (created/updated/deleted) rows to the database. No need to send the entire data set retrieved if it has not changed. This type of processing is available in EF via the ObjectContext, ObjectStateManager classes.

share|improve this answer
add comment

You have to find out what columns records make unique. Then you usually set a unique constraints on one/multiple columns in the database. This will (depending on db-system) throw some error if you want to insert another record with the same unique columns.

See http://en.wikipedia.org/wiki/Unique_key

share|improve this answer
    
I have thought about this, however, doing the try..catch method of checking for duplicates is not a method I intend on employing –  Eminem Jul 4 '12 at 6:35
    
Ok. Just out of curiosity. Why not? Does it take so long? What does happen if you batch insert a whole bunch of records. Are all items rejected if one is duplicated, or just the one? –  matcauthon Jul 4 '12 at 6:44
1  
All items would be rejected if you set up your commit to be after all the inserts are run. E.g. Begin transaction insert statement 1 is run insert statement 2 is run etc.. Commit transaction Also, allowing exceptions to control program flow is bad design. Have a read: yoda.arachsys.com/csharp/exceptions.html –  Eminem Jul 4 '12 at 6:54
add comment

A simple solution would be to create an in-memory cache supported by a set, which should write the information in the database, only when it's full, and say only 10000 entries leaving room for other 10000 (see it as a queue). Or store it to the DB when the WS call returns different data (provided that different data in calls means that there are no duplicates in it). By using a set, you are assured that no duplicates are present in it.

share|improve this answer
    
I like the idea of the set. But that would mean I would have to fetch the records from the webservice for that specific user/date combo, insert it into a set, then fetch the records from the db for that user/date combo and find the difference between the two sets and save that to the db. –  Eminem Jul 4 '12 at 6:27
    
I have understood (and thus made the assumption) that duplicates can only arise because the call to WS returns the same data; it has not been updated. In the case that the WS call from today and the WS call from a month from now may contain duplicates this solution is not good. –  m3th0dman Jul 4 '12 at 6:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.