Innerjoin vs包含,它更快[英] InnerJoin vs Contains, which is faster

本文是小编为大家收集整理的关于Innerjoin vs包含,它更快的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我正在编写查询,以使用LINQ从数据库中找到整数值的总和.在发展时,我想到了两个想法.我可以通过contains或内部连接来做到这一点.以下是查询

var Customer = db.tbl_User_to_CustomerMast.Where(i => i.fk_Store_ID == s.ShopID).ToList();

查询1:

var RewardCount = db.tbl_RewardAwardMast.Where(i => Customer.Select(j => j.User_Customer_ID).ToList().Contains(i.fk_Customer_UserID.Value)).Sum(i => i.RewardPoints).GetValueOrDefault(0);

查询2:

var RewardCount = Customer.Join(db.tbl_RewardAwardMast, i => i.User_Customer_ID, j => j.fk_Customer_UserID, (i, j) => new { Customer = i, Reward = j }).Sum(i=>i.Reward.RewardPoints).GetValueOrDefault(0);

我知道,在两个查询中,将在服务器端完成计算以匹配记录.

那么哪个查询适合更快执行?

推荐答案

我会说查询1会更快,因为它将被翻译成简单的SQL与此类似:

SELECT SUM(RewardPoints)
FROM User_to_CustomerMast
WHERE fk_Customer_UserID IN (c1,c2,c3,c4,c5,...,cx)

查询2可能会较慢,因为您本质上试图将本地列表变量与DB的数据一起加入本地列表变量.这个列表最初来自数据库并不重要,在执行ToList()时,这种与数据库的关联丢失了.要执行此类查询,将将必要的信息传递给数据库以生成第二个表,以便使用JOIN现有表.这将使用Union完成.

SELECT SUM(RewardPoints)
FROM 
    User_to_CustomerMast INNER JOIN
    (
         SELECT c1
         UNION 
         SELECT c2
         UNION 
         SELECT c3
         UNION 
         SELECT c4
         UNION 
         SELECT c5
         UNION 
         ...
         ...
         SELECT cx
     ) AS T ON T.Id = fk_Customer_UserID 

如您所见,当地列表增长时,查询会快速扩展.如果您的列表太长了,那么您甚至可能会得到SQL太复杂的例外.

您可以在此处查看有关此主题的详细分析: http://blog.hompus.nl/2010/08/26/加入 - Queryable-an-an-nionumerable/

本文地址:https://www.itbaoku.cn/post/1557005.html

问题描述

I am writing query to find sum of the integer value from database using linq. while developing I got two thoughts in my mind. I can either do it by contains or inner join. Following are the queries

var Customer = db.tbl_User_to_CustomerMast.Where(i => i.fk_Store_ID == s.ShopID).ToList();

Query 1:

var RewardCount = db.tbl_RewardAwardMast.Where(i => Customer.Select(j => j.User_Customer_ID).ToList().Contains(i.fk_Customer_UserID.Value)).Sum(i => i.RewardPoints).GetValueOrDefault(0);

Query 2:

var RewardCount = Customer.Join(db.tbl_RewardAwardMast, i => i.User_Customer_ID, j => j.fk_Customer_UserID, (i, j) => new { Customer = i, Reward = j }).Sum(i=>i.Reward.RewardPoints).GetValueOrDefault(0);

I know that in both queries calculation will be done on the server side for matching the records.

So which query is suitable for faster execution?

推荐答案

I would say that Query 1 will be faster as it will be translated simple SQL similar to this:

SELECT SUM(RewardPoints)
FROM User_to_CustomerMast
WHERE fk_Customer_UserID IN (c1,c2,c3,c4,c5,...,cx)

Query 2 might be slower because you are essentially trying to join local list variable with data from DB. It does not matter that this list originally came from DB, this association with DB is lost when doing ToList(). To execute such query, necessary information will be passed to DB to generate second table so there is something to JOIN existing table with. This will be done using UNION.

SELECT SUM(RewardPoints)
FROM 
    User_to_CustomerMast INNER JOIN
    (
         SELECT c1
         UNION 
         SELECT c2
         UNION 
         SELECT c3
         UNION 
         SELECT c4
         UNION 
         SELECT c5
         UNION 
         ...
         ...
         SELECT cx
     ) AS T ON T.Id = fk_Customer_UserID 

As you can see, query expands fast when local list grows. If your list is too long, at one point you might even get exception that SQL is too complex.

You can see detailed analysis on this topic here: http://blog.hompus.nl/2010/08/26/joining-an-iqueryable-with-an-ienumerable/