的响应速度慢查询视图时 - 使用LINQ to SQL

Question

我有以下看法：

SELECT  
poHeader.No_ AS PONumber, 
poHeader.[Buy-from Vendor No_] AS VendorNumber, 
poHeader.[Document Date] AS DocumentDate, 
vendor.Name AS VendorName, 
vendor.Contact AS VendorContact, 
vendor.[E-Mail] AS VendorEmail, 
vendor.Address AS VendorAddress, 
vendor.[Address 2] AS VendorAddress2, 
vendor.City AS VendorCity, 
vendor.County AS VendorCounty, 
vendor.[Post Code] AS VendorPostCode, 
vendor.[Phone No_] AS VendorPhone, 
vendor.[Fax No_] AS VendorFax, 
salesHeader.No_ AS SONumber, 
poHeader.[Order Date] AS OrderDate, 
salesHeader.[Crocus Comment] AS CrocusComment, 
salesHeader.GiftMessage, 
salesHeader.[Delivery Comment] AS DeliveryComment, 
salesHeader.[Shipment Date] AS DeliveryDate, 
COALESCE (salesHeader.[Ship-to Name], 
poHeader.[Ship-to Name]) AS DeliveryName, 
COALESCE (salesHeader.[Ship-to Address], 
poHeader.[Ship-to Address]) AS DeliveryAddress, 
COALESCE (salesHeader.[Ship-to Address 2], 
poHeader.[Ship-to Address 2]) AS DeliveryAddress2, 
COALESCE (salesHeader.[Ship-to City], 
poHeader.[Ship-to City]) AS DeliveryCity, COALESCE (salesHeader.[Ship-to County], 
poHeader.[Ship-to County]) AS DeliveryCounty, 
COALESCE (salesHeader.[Ship-to Post Code], 
poHeader.[Ship-to Post Code]) AS DeliveryPostcode, 
salesHeader.DeliveryPhoneNo, poForEmailing.Processed, 
poForEmailing.Copied 

FROM   
Navision4.dbo.[Crocus Live$Purch_ orders for e-mailing] AS poForEmailing 
LEFT OUTER JOIN 
Navision4.dbo.[Crocus Live$Purchase Header] AS poHeader ON poForEmailing.No_ = poHeader.No_ 
INNER JOIN 
Navision4.dbo.[Crocus Live$Vendor] AS vendor ON poHeader.[Buy-from Vendor No_] = vendor.No_ 
LEFT OUTER JOIN 
Navision4.dbo.[Crocus Live$Sales Header] AS salesHeader ON salesHeader.No_ = dbo.fnGetSalesOrderNumber(poHeader.No_) 

这种观点是在一个名为NavisionMeta 数据库中创建它查询数据库（在同一台服务器上）claled Navision4

我最近将两个数据库都移到了新的（更好的）硬件上。 不知道这是相关的，但新的硬件具有SQL 2008，和旧的硬件中运行SQL 2000

如果我使用SQL Management Studio中此查询需要2分钟以上进行查询：

SELECT * 
FROM [NavisionMeta].[dbo].[PurchaseOrders] 
WHERE Processed=0 AND Copied=0 

这太久呀！

即使我将超时时间调整为5分钟，LINQ中的以下查询将全部放在一起超时！

  var purchaseOrdersNotProcessed = (from p in db.PurchaseOrders 
               where p.Copied.Equals(0) 
               && p.Processed.Equals(0) 
               select p).ToList(); 

什么令我困惑的是，在以前的硬件上，它工作的很好！

以防万一它是相关的，上面所用的UDF是：

CREATE FUNCTION [dbo].[fnGetSalesOrderNumber](@PONumber varchar(20)) 
RETURNS varchar(20) 
AS 
BEGIN 

RETURN (
SELECT 
    TOP 1 [Sales Order No_] 
FROM 
    Navision4.dbo.[Crocus Live$Purchase Line] 
WHERE 
    [Document No_] = @PONumber 
) 

Answer 1

一个起点，可能是看旧机和新机无论在查询的执行计划。新版本的SQL Server肯定会存在优化差异。执行计划可能会告诉你，索引是必要的，由于某种原因，在以前的版本中并不重要。

Answer 2

您也可以考虑更新统计信息。

Answer 3

编辑

我不知道你正在尝试做的，但如果你试图让PO的信息，我认为下面的变化会有所帮助，你有

FROM Navision4.dbo.[Crocus Live$Purch_ orders for e-mailing] AS poForEmailing 
    LEFT OUTER JOIN Navision4.dbo.[Crocus Live$Purchase Header] AS poHeader ON poForEmailing.No_ = poHeader.No_ 
    INNER JOIN Navision4.dbo.[Crocus Live$Vendor] AS vendor ON poHeader.[Buy-from Vendor No_] = vendor.No_ 
    LEFT OUTER JOIN Navision4.dbo.[Crocus Live$Sales Header] AS salesHeader ON salesHeader.No_ = dbo.fnGetSalesOrderNumber(poHeader.No_) 

试试这个：

FROM Navision4.dbo.[Crocus Live$Purch_ orders for e-mailing] AS poForEmailing 
    LEFT JOIN Navision4.dbo.[Crocus Live$Purchase Header] AS poHeader ON poForEmailing.No_ = poHeader.No_ 
    LEFT JOIN Navision4.dbo.[Crocus Live$Vendor] AS vendor ON poHeader.[Buy-from Vendor No_] = vendor.No_ 
    LEFT JOIN Navision4.dbo.[Crocus Live$Sales Header] AS salesHeader ON salesHeader.No_ = dbo.fnGetSalesOrderNumber(poHeader.No_) 

就速度而言，您可能需要更新一些索引...尤其是No_字段。 除了做如下变更摆脱fuGetSalesOrderNumber（）呼吁各行：

;WITH PurchaseLineByPO AS 
(
    SELECT MAX([Sales Order No_]) as SO, [Document No_] as DNum 
    FROM Navision4.dbo.[Crocus Live$Purchase Line] 
    Group By [Document No_] 
) 
--blah blah whole select goes here.. with 
JOIN PurchaseLineByPO ON DNum = poHeader.No_ 
--in the join and 
LEFT OUTER JOIN 
Navision4.dbo.[Crocus Live$Sales Header] AS salesHeader ON salesHeader.No_ = PurchaseLineByPO.SO 
--replaces what you had 

看看是否能为你工作。

忽略低于旧的东西...

这是我很难清楚地看到你在做什么在这里，但它是罕见的外部连接的需要 - 这是真正你想要做什么？如果是这样，您可以反转订单并进行内部连接。例如，你说：

FROM   
Navision4.dbo.[Crocus Live$Purch_ orders for e-mailing] AS poForEmailing 
LEFT OUTER JOIN 
Navision4.dbo.[Crocus Live$Purchase Header] AS poHeader ON poForEmailing.No_ = poHeader.No_ 

，你可以说

FROM   
Navision4.dbo.[Crocus Live$Purchase Header] AS poHeader 
LEFT INNER JOIN 
Navision4.dbo.[Crocus Live$Purch_ orders for e-mailing] AS poForEmailing ON poForEmailing.No_ = poHeader.No_ 

根据您的数据，这可能会对你的运行时间显著影响。

Answer 4

Left Inner Join？嗯，不知道这是否会有所帮助...

如果此查询以前工作（数据不是性能），那么上述查询将受益于将所有联接转换为INNER JOINs。因为你是INNER JOINing一个来自poHeader（外连接）的值的供应商，所以你基本上在poHeader上做了一个内连接要求（除了外连接可能遇到性能问题外）。供应商无法返回值，除非poHeader具有值，并且由于供应商是内连接的，如果poHeader中没有值，整行将被忽略。与salesHeader一样。在连接中使用的函数需要poHeader中的一个值（必须根据上述逻辑具有一个值），所以这个连接也可以从显式的INNER JOIN中获益，而不仅仅是一个隐含的连接。

除此之外，我的确同意关于索引的说法（除了关于很少需要的外部连接的说法外，就像说如果你有一把锤子，你不需要一把螺丝刀）。索引听起来是对性能较弱的最合理的解释。具体来说，你应该检查你的poForEmailing别名表是否有索引（[processed]，[copied]）。如果没有该索引，则可以预期查询时间至少翻倍，因为数据大小加倍，因为该表中的每条记录都需要针对这些谓词进行测试。关于你原来的问题，我没有注意到SQL Server 2008中的任何东西都会提示性能发生这样的变化，所有其他事情都是平等的。