推荐的方法来插入一个城堡ActiveRecord的许多行和忽略任何受骗者 [英] Recommended approach to insert many rows with Castle ActiveRecord and ignore any dupes

问题描述

我有插入一堆菜谱成队列在数据库中（存储食谱用户的爱好有烹饪，类似于Netflix的电影队列）一个WebMethod。用户能够在一次检查一串食谱和队列他们。我有类似下面的代码：

  [的WebMethod] 
公共无效EnqueueRecipes（SecurityCredentials凭据，的Guid [] recipeIds） 
 {
 DB.User用户=新DB.User（凭证）;使用
 
（新的TransactionScope（OnDispose.Commit））
 {
的foreach（GUID摆脱在recipeIds）
 {
 DB.QueuedRecipe QR =新的数据库.QueuedRecipe（Guid.NewGuid（），用户，新DB.Recipe（RID））; 
 qr.Create（）; 
} 
} 
} 
  

我的唯一性约束用户名/ RecipeId因此用户只能排队配方一次。但是，如果他们碰巧选择配方这是已经在他们的队列我真的不希望打扰一个错误消息的用户，我只是想忽略配方。

如果唯一约束违反上面的代码将抛出一个SQL例外。什么是解决这个问题的最好办法，而完全忽略重复行。我现在的想法是：

• 1）首先从数据库中读取用户的整个队列，并检查
  ，列出第一。如果配方已经存在，只是继续在
  for循环。优点：没有不必要的SQL插入被发送到
  数据库。缺点：慢，特别是如果用户有一个大辫子


• 2）不要使用ActiveRecord，而是传递整个recipeIds数组
  到SQL函数。此功能将检查每行第一个存在
  。优点：快速潜在的，让SQL处理所有的肮脏的工作。
  缺点：每次循环后坏ActiveRecord的模式，需要新的数据库代码，这是
  往往难以维持和昂贵的实施


• 3）CreateAndFlush。基本上，不要运行在一个事务中这整个
  循环。提交每一行，因为它的加入和
  捕获SQL错误和忽视。优点：低启动成本，并没有
  需要新的SQL后端代码。缺点：插入
  大量的行到数据库一次，虽然这是值得怀疑的用户
  将永远提交了十几个新的食谱一次潜在的慢

是否有与城堡或NHibernate的框架，任何其他小动作？另外，我的SQL后端是PostgreSQL的9.0。 ！谢谢

更新：

我参加了一个射击在第一种方法和它似乎工作得很好。它发生在我，我没有加载整个队列，只是出现在recipeIds的人。我相信现在我的的foreach（）循环是O（n ^ 2）根据名单，LT的效率的Guid> ::载有（），但我认为这可能是因为我将要使用的大小得体。

  //检查愚弄
 DB.QueuedRecipe [] = dbRecipes DB.QueuedRecipe.FindAll（Expression.In（配方，
（从R IN recipeIds选择新DB.Recipe（R））。ToArray的（）
））; 
 
名单，LT;&的Guid GT;现有=（在dbRecipes从R选择r.Recipe.RecipeId）.ToList（）;使用
 
（新的TransactionScope（OnDispose.Commit））
 {
的foreach（GUID摆脱在recipeIds）
 {
如果（existing.Contains（RID ））
继续; 
 
 DB.QueuedRecipe QR =新DB.QueuedRecipe（Guid.NewGuid（），用户，新DB.Recipe（RID））; 
 qr.Create（）; 
} 
} 
  

解决方案

您能做到这一点与一个SQL语句：

<预类=郎-SQL prettyprint-覆盖> INSERT INTO user_recipe
选择new_UserId，new_RecipeId
FROM user_recipe
WHERE NOT EXISTS（
选择*
FROM user_recipe
WHERE（用户ID，RecipeId）=（new_UserId，new_RecipeId ）
）;


的

SELECT 只返回该行，如果它不存在，所以它只会在这种情况下插入。

---

解决方案对于批量插入

如果你有食谱一长串一次插入，您可以：

<预类=郎-SQL prettyprint，覆盖> CREATE TABLE TEMP我（用户ID INT，recipeid INT）ON COMMIT DROP;

INSERT INTO I值
（1,2），（2,4），（2,4），（2,7），（2,43），（23,113）， （223133）;

INSERT INTO user_recipe
SELECT DISTINCT我* - 从插入考生自理
从我删除愚弄
LEFT JOIN user_recipeù使用（用户ID，recipeid）
，其中u.userid IS NULL;


---

解决方案同时插入了一把

临时表将是短短记录矫枉过正，因为迈克评论道。

<预类=郎-SQL prettyprint -override> INSERT INTO user_recipe
选择我*
FROM（
SELECT DISTINCT * - 只有当你需要删除可能愚弄
FROM（
值（1 ::整型，2 :: INT）
，（2，3）
，（2，4）
，（2，4） - 欺骗将除去
，（2，43）
，（23，113）
，（223，133）
）本人（用户ID，recipeid）
）本人
LEFT JOIN user_recipeù使用（用户ID，recipeid）
，其中u.userid IS NULL;


I have a webmethod that inserts a bunch of recipes into a queue in the database (to store recipes the user is interested in cooking, similar to NetFlix's movie queue). The user is able to check off a bunch of recipes at once and queue them. 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 constraint on UserId/RecipeId so a user can only enqueue a recipe once. However, if they happen to select a recipe that's already in their queue I don't really want to bother the user with an error message, I just want to ignore that recipe.

The above code will throw a SQL exception if the unique constraint is violated. What's the best approach to get around this, and simply ignore duplicate rows. My current ideas are:

• 1) First load the user's entire queue from the database and check that list first. If the recipe already exists, just continue in the for loop. Pros: No unnecessary SQL inserts get sent to the database. Cons: Slower, especially if the user has a big queue.
• 2) Don't use ActiveRecord and instead pass the entire recipeIds array into a SQL function. This function will check if each row exists first. Pros: Potentially fast, lets SQL handle all the dirty work. Cons: Breaks ActiveRecord pattern and requires new DB code, which is often harder to maintain and costlier to implement.
• 3) CreateAndFlush after each loop. Basically, don't run this entire loop in a single transaction. Commit each row as it's added and catch SQL errors and ignore. Pros: Low startup cost, and doesn't require new SQL backend code. Cons: Potentially slower for inserting lots of rows into the database at once, though it's doubtful a user would ever submit over a dozen or so new recipes at once.

Are there any other little tricks with Castle or the NHibernate framework? Also, my SQL backend is PostgreSQL 9.0. Thanks!

Update:

I took a shot at the first approach and it seems to work pretty well. It occured to me I don't have to load the entire queue, just the ones that appear in recipeIds. I believe my foreach() loop is now O(n^2) depending on the efficiency of List<Guid>::Contains() but I think this is probably decent for the sizes I'll be working 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();
   }
}

解决方案

You can do that 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)
   );

The SELECT only returns the row if it doesn't already exist, so it will only be inserted in this case.

---

Solution for bulk inserts

If you have a long list of recipes to insert at once, you could:

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

Temporary table would be an overkill for just a few records, 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;

这篇关于推荐的方法来插入一个城堡ActiveRecord的许多行和忽略任何受骗者的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！