linq 速度慢

Question

源程序
                    sumMoney = products.Sum(p => p.iDistributeWeight * p.iUnitPrice/1000);
对应的sql代码
exec sp_executesql N'SELECT SUM(([t0].[iDistributeWeight] * [t0].[iUnitPrice]) / @p7) AS [value]
FROM [prod_Product] AS [t0]
LEFT OUTER JOIN [prod_Order] AS [t1] ON [t1].[cOrderCode] = [t0].[cOrderCode]
LEFT OUTER JOIN [dbo].[sbase_customer] AS [t2] ON [t2].[custCode] = [t1].[cCustCode]
WHERE ([t1].[DeliveryDate] < @p0) AND ([t1].[DeliveryDate] >= @p1) AND ([t2].[custName] LIKE @p2) AND (([t1].[cOrderTypeCode] = @p3) OR ([t1].[cOrderTypeCode] = @p4)) AND (([t1].[cStatusCode] = @p5) OR ([t1].[cStatusCode] = @p6))',N'@p0 datetime,@p1 datetime,@p2 nvarchar(4000),@p3 nvarchar(4000),@p4 nvarchar(4000),@p5 nvarchar(4000),@p6 nvarchar(4000),@p7 decimal(33,4)',@p0='2011-08-26 00:00:00:000',@p1='2011-01-04 00:00:00:000',@p2=N'%双日プラネッ卜株式会社%',@p3=N'1',@p4=N'2',@p5=N'6',@p6=N'7',@p7=1000.0000在sql server中执行很快，1秒中
在源程序中报超时

Answer 1

是不是连接有问题，用sql profile看看是不是执行了。

Answer 2

绝对不可能，这都能超时那LINQ还能存活下来么？找找你别的地方的原因。

Answer 3

麻烦你下一次说  LINQ TO SQL 很慢

Answer 4

貌似你的实际sql里还有很多where条件呀。你这段linq也没有where呀。

Answer 5

好像建立model的时候可以选择每个field的currency mode.
如果都设置成true的话，update记录是会将所有的field都放在where中去标识一条记录，如果被人已经更新了记录的话，就找不到产生错误。

Answer 6

完整代码是这样的，
我定义了一个函数，返回结果集，同时参数中有一个out参数，返回汇总金额，
时间段是1年，当运行到计算汇总金额的时候，超时，（这个是在.net中执行超时）
在sql profile跟踪，找到执行的sql代码，在查询分析器中执行，1秒钟就执行成功了数据库连接不存在问题          var orders = db.Orders.Where(o => 1 == 1); //已发货、已到货                    var status = db.NormalTypes.Where(n => n.cType == "OrderStatus");
                    var type = db.NormalTypes.Where(n => n.cType == "OrderType");                    orders = orders.Where(o => o.cStatusCode == "6" || o.cStatusCode == "7"); //已发货、已到货                    //订单区域：厂内、大区
                    orders = orders.Where(o => o.cOrderTypeCode == "1" || o.cOrderTypeCode == "2");                    if (strStartDate != null)
                    {
                        DateTime startDate = Convert.ToDateTime(strStartDate);
                        orders = orders.Where(o => o.DeliveryDate >= startDate);
                    }
                    if (strEndDate != null)
                    {
                        DateTime endDate = Convert.ToDateTime(strEndDate);
                        orders = orders.Where(o => o.DeliveryDate < endDate);
                    }                    if (deptCode != null)
                    {
                        orders = orders.Where(o => o.cDeptCode == deptCode);
                    }                    if (salemanCode != null)
                    {
                        orders = orders.Where(o => o.cSalemanCode == salemanCode);
                    }                    if (custName != null)
                    {
                        orders = orders.Where(o => SqlMethods.Like(o.Customer.cust_name, "%" + custName + "%"));
                    }                    if (hasPart != null)
                    {
                        if ((bool)hasPart)
                        {
                            orders = orders.Where(o => o.InvoiceOrders.Count() != 0);
                        }
                        else
                        {
                            orders = orders.Where(o => o.InvoiceOrders.Count() == 0);
                        }
                    }                    if (isWaitInvoice != null)
                    {
                        if ((bool)isWaitInvoice)
                        {
                            orders = orders.Where(o => (o.Products.Sum(p => p.iDistributeWeight * p.iUnitPrice / 1000) - (o.Discounts.Sum(d => d.iMoney) ?? 0) - (o.InvoiceOrders.Sum(io => io.iMoney) ?? 0)) > 0);
                        }
                    }                    if (orderCode != null)
                    {
                        orders = orders.Where(o => SqlMethods.Like(o.cOrderCode, "%" + orderCode + "%"));
                    }                    if (cStoreHouse != null)
                    {
                        if (cStoreHouse == "厂内")
                        {
                            orders = orders.Where(o => (o.cStoreHouse == "" || o.cStoreHouse==null ));
                        }
                        else
                        {
                            orders = orders.Where(o => (o.cStoreHouse == cStoreHouse));
                        }
                    }                    sumMoney = orders.Sum(o => o.Products.Sum(p=>p.iDistributeWeight * p.iUnitPrice/1000));
                    if (sumMoney != null)
                    {
                        sumMoney = Math.Round((decimal)sumMoney, 2);
                    }                    if (isSum != null)
                    {
                        if (isSum == 1)
                        {
                            return orders.GroupBy(o => new { o.cDeptCode, o.cDeptName, o.cSalemanCode, o.cSalemanName }).Select(g => new { 大区 = g.Key.cDeptName,大区编号 = g.Key.cDeptCode, 业务员 = g.Key.cSalemanName, 总价 = g.Sum(o => o.Products.Sum(p => p.iDistributeWeight * p.iUnitPrice / 1000)), 优惠 = g.Sum(o => o.Discounts.Sum(d => d.iMoney) ?? 0), 应收货款 = g.Sum(o => o.Products.Sum(p => p.iDistributeWeight * p.iUnitPrice / 1000) - (o.Discounts.Sum(d => d.iMoney) ?? 0)) }).OrderBy(o => o.大区);
                        }
                    }                    return orders.Join(status, o => o.cStatusCode, s => s.cCode, (o, s) => new { o, s }).Join(type, o => o.o.cOrderTypeCode, t => t.cCode, (o, t) => new { o, t }).Select(o => new { 订单编号 = o.o.o.cOrderCode, 业务员 = o.o.o.Person.cPersName, 客户名称 = o.o.o.Customer.cust_name, 总价 = o.o.o.Products.Sum(p => p.iDistributeWeight * p.iUnitPrice / 1000), 应收货款 = o.o.o.Products.Sum(p => p.iDistributeWeight * p.iUnitPrice / 1000) - (o.o.o.Discounts.Sum(d => d.iMoney) ?? 0), 优惠 = o.o.o.Discounts.Sum(d => d.iMoney) ?? 0, 剩余未开票金额 = o.o.o.Products.Sum(p => p.iDistributeWeight * p.iUnitPrice / 1000) - (o.o.o.Discounts.Sum(d => d.iMoney) ?? 0) - (o.o.o.InvoiceOrders.Sum(io => io.iMoney) ?? 0), 发货日期 = o.o.o.DeliveryDate, 订单区域 = o.o.o.cOrderTypeName, 订单日期 = o.o.o.OrderDate, 是否开发票 = o.o.o.iWithTax, 配货重量 = o.o.o.Products.Sum(p => p.iDistributeWeight)  }).OrderByDescending(o => o.发货日期);