Recommended approach to insert many rows in Castle ActiveRecord and ignore any cheats

Question

I have a web method that inserts a bunch of recipes into a queue in a database (to store recipes that a user is interested in cooking, like a NetFlix movie queue). The user can immediately check a bunch of recipes and put them in the queue. I have code similar to this:

[WebMethod] public void EnqueueRecipes(SecurityCredentials credentials, Guid[] recipeIds) { DB.User user = new DB.User(credentials); using (new TransactionScope(OnDispose.Commit)) { foreach (Guid rid in recipeIds) { DB.QueuedRecipe qr = new DB.QueuedRecipe(Guid.NewGuid(), user, new DB.Recipe(rid)); qr.Create(); } } } 

I have a unique restriction on UserId / RecipeId, so the user can only insert the recipe once. However, if they want to select a recipe that is already in the queue, I really do not want to disturb the user with an error message, I just want to ignore this recipe.

The above code throws an SQL exception if a unique constraint is violated. What's the best approach to get around this and just ignore duplicate lines. My current ideas:

• 1) First, load the entire user queue from the database and check this list first. If the recipe already exists, simply continue to the for loop. Pros: No unnecessary SQL inserts are sent to the database. Cons: slower, especially if the user has a large queue.
• 2) Do not use ActiveRecord and instead pass the entire array of recipeIds to the SQL function. This function checks if each row exists first. Pros: Potentially fast, allows SQL to handle all the dirty work. Cons: Breaks the ActiveRecord pattern and requires new DB code, which is often harder to maintain and more expensive to execute.
• 3) CreateAndFlush after each cycle. Basically, do not run this entire cycle in one transaction. Commit each row as you add it and catch SQL errors and ignore it. Pros: Low startup cost and no new SQL code required. Cons: Potentially slower to insert multiple rows into the database at once, although it is doubtful that the user will ever submit more than a dozen new recipes at once.

Are there any other tricks with the Castle or NHibernate framework? In addition, my SQL server is PostgreSQL 9.0. Thanks!

Update:

I fired the first shot, and it seems to work very well. This happened to me, I do not need to download the entire queue, only those that appear in recipeIds. I believe that my foreach() is now O (n ^ 2) depending on the efficiency of List<Guid>::Contains() , but I think this is probably suitable for the sizes I will work with.

 //Check for dupes DB.QueuedRecipe[] dbRecipes = DB.QueuedRecipe.FindAll(Expression.In("Recipe", (from r in recipeIds select new DB.Recipe(r)).ToArray() )); List<Guid> existing = (from r in dbRecipes select r.Recipe.RecipeId).ToList(); using (new TransactionScope(OnDispose.Commit)) { foreach (Guid rid in recipeIds) { if (existing.Contains(rid)) continue; DB.QueuedRecipe qr = new DB.QueuedRecipe(Guid.NewGuid(), user, new DB.Recipe(rid)); qr.Create(); } } 

Answer 1

You can do this with a single SQL statement:

 INSERT INTO user_recipe SELECT new_UserId, new_RecipeId FROM user_recipe WHERE NOT EXISTS ( SELECT * FROM user_recipe WHERE (UserId, RecipeId) = (new_UserId, new_RecipeId) ); 

SELECT only returns a row if it does not already exist, so it will only be inserted in this case.

---

Volume Insert Solution

If you have a long list of recipes to paste immediately, you can:

 CREATE TEMP TABLE i(userId int, recipeid int) ON COMMIT DROP; INSERT INTO i VALUES (1,2), (2,4), (2,4), (2,7), (2,43), (23,113), (223,133); INSERT INTO user_recipe SELECT DISTINCT i.* -- remove dupes from the insert candidates themselves FROM i LEFT JOIN user_recipe u USING (userid, recipeid) WHERE u.userid IS NULL; 

---

Solution for inserting a handful at a time

A temporary table would be redundant for just a few entries, as Mike commented.

 INSERT INTO user_recipe SELECT i.* FROM ( SELECT DISTINCT * -- only if you need to remove possible dupes FROM ( VALUES (1::int, 2::int) ,(2, 3) ,(2, 4) ,(2, 4) -- dupe will be removed ,(2, 43) ,(23, 113) ,(223, 133) ) i(userid, recipeid) ) i LEFT JOIN user_recipe u USING (userid, recipeid) WHERE u.userid IS NULL;