使用 Castle ActiveRecord 插入多行并忽略任何重复的推荐方法
我有一个 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
的效率,但我认为这可能是不错的对于我将要使用的尺寸。
//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, justcontinue
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 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
您可以使用单个 SQL 语句来完成此操作:
SELECT
仅在该行尚不存在时返回该行,因此仅在这种情况下才会插入该行。批量插入的解决方案
如果您要一次插入一长串菜谱,您可以:
一次插入少量的解决方案
正如 Mike 所评论的,临时表对于只有几条记录来说是一种矫枉过正。
You can do that with a single SQL statement:
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:
Solution for inserting a handful at a time
Temporary table would be an overkill for just a few records, as Mike commented.