使用 Castle ActiveRecord 插入多行并忽略任何重复的推荐方法

发布于 2024-12-17 11:42:26 字数 1920 浏览 0 评论 0原文

我有一个 webmethod,可以将一堆菜谱插入到数据库的队列中(用于存储用户有兴趣烹饪的菜谱,类似于 NetFlix 的电影队列)。用户可以一次检查一堆食谱并将其排队。我有与此类似的代码:

[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();
       }
    }
}

我对 UserId/RecipeId 有一个唯一的约束,因此用户只能将菜谱排队一次。但是,如果他们碰巧选择了队列中已经存在的菜谱,我真的不想用错误消息打扰用户,我只想忽略该菜谱。

如果违反唯一约束,上面的代码将抛出 SQL 异常。解决这个问题的最佳方法是什么,只需忽略重复的行。我目前的想法是:

  • 1)首先从数据库加载用户的整个队列并检查 首先列出该列表。如果配方已经存在,只需继续 for 循环。优点:不会将不必要的 SQL 插入发送到 数据库。缺点:速度较慢,特别是当用户队列很大时。
  • 2)不要使用ActiveRecord,而是传递整个recipeIds数组 转换为 SQL 函数。该函数将检查每一行是否存在 第一的。优点:可能很快,让 SQL 处理所有脏活。 缺点:破坏了 ActiveRecord 模式并需要新的数据库代码,即 通常更难维护且实施成本更高。
  • 3)每次循环后CreateAndFlush。基本上,不要运行整个 在单个事务中循环。添加添加的每一行并提交 捕获 SQL 错误并忽略。优点:启动成本低,而且不 需要新的 SQL 后端代码。缺点:插入速度可能较慢 一次将大量行存入数据库,尽管用户对此表示怀疑 会一次提交十几个左右的新食谱。

Castle 或 NHibernate 框架还有其他小技巧吗?另外,我的 SQL 后端是 PostgreSQL 9.0。谢谢!

更新:

我尝试了第一种方法,看起来效果很好。我突然想到我不必加载整个队列,只需加载recipeIds 中出现的队列即可。我相信我的 foreach() 循环现在是 O(n^2),具体取决于 List::Contains() 的效率,但我认为这可能是不错的对于我将要使用的尺寸。

//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();
   }
}

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();
   }
}

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

芸娘子的小脾气 2024-12-24 11:42:26

您可以使用单个 SQL 语句来完成此操作:

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 仅在该行尚不存在时返回该行,因此仅在这种情况下才会插入该行。


批量插入的解决方案

如果您要一次插入一长串菜谱,您可以:

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;

一次插入少量的解决方案

正如 Mike 所评论的,临时表对于只有几条记录来说是一种矫枉过正。

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;

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;
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文