复杂查询 - 联接会比子查询更快吗?

Question

我目前正在Delphi中开发一个应用程序，该应用程序使用SQL来利用第三方发票系统的后端，以便我们可以扩展其报告功能。我认为自己相当精通编程的delphi方面，但是SQL对我来说是新的，所以在这个论坛和其他资源的巨大帮助下，我设法自学了比我想象的更多。

大多数数据是从几个表格中提取的（我对那边没有问题，所以我不会用这些细节堵塞帖子），但是我在获取成本价格时遇到了问题。它存储在跟踪历史成本价的表中，因此对于每个产品 （16'000+），可能有数百条记录，但我只需要最接近 （<=） 发票日期的每个产品的成本。这是函数：

CREATE FUNCTION dbo.CostAtDate ( @costdate AS datetime , @product AS int )
RETURNS decimal(18,2)
AS
BEGIN
DECLARE @result decimal(18,2)
SET @result = (
  Select Top 1
    BASE_InventoryCostLogDetail.AverageCostAfter
  From
    BASE_InventoryCostLogDetail
  Where
    CreatedDttm < @costdate And CreatedDttm > DATEADD(month,-1,@costDate) And
    ProdId = @product
  Order By
    CreatedDttm Desc)
RETURN @result
END 

这是其中一个查询（有几个不同的查询，但都基于相同的结构）：

Select
  BASE_Customer.Name,
  SO_SalesOrder.OrderNumber,
  SO_SalesOrderInvoice_Line.Description,
  SO_SalesOrderInvoice_Line.UnitPrice,
  Case SO_SalesOrderInvoice_Line.ItemTaxCodeId
    When '100' Then (SO_SalesOrderInvoice_Line.UnitPrice / 11) * 10
    Else SO_SalesOrderInvoice_Line.UnitPrice End As exgst,
  SO_SalesOrderInvoice_Line.QuantityUom,
  SO_SalesOrderInvoice_Line.QuantityDisplay,
  Case SO_SalesOrderInvoice_Line.QuantityUom
    When 'cases.' Then dbo.CostAtDate(SO_SalesOrder.OrderDate,
    SO_SalesOrderInvoice_Line.ProdId)  * BASE_Product.SoUomRatioStd
    Else dbo.CostAtDate(SO_SalesOrder.OrderDate,
    SO_SalesOrderInvoice_Line.ProdId) End As cost,
  Case SO_SalesOrderInvoice_Line.QuantityUom
    When 'cases.' Then ((dbo.CostAtDate(SO_SalesOrder.OrderDate,
    SO_SalesOrderInvoice_Line.ProdId) * BASE_Product.SoUomRatioStd) / 11) * 10
    Else (dbo.CostAtDate(SO_SalesOrder.OrderDate,
    SO_SalesOrderInvoice_Line.ProdId) / 11) * 10 End As exgstcost,
  BASE_Product.SoUomRatioStd,
  BASE_Product.Name As Name1,
  SO_SalesOrder.OrderDate
From
  BASE_Customer Inner Join
  SO_SalesOrder On SO_SalesOrder.CustomerId = BASE_Customer.CustomerId
  Inner Join
  SO_SalesOrderInvoice_Line On SO_SalesOrderInvoice_Line.SalesOrderId =
    SO_SalesOrder.SalesOrderId Inner Join
  BASE_Product On SO_SalesOrderInvoice_Line.ProdId = BASE_Product.ProdId
Where
  SO_SalesOrder.OrderDate Between '20131028' And '20131029'

现在，当我在

所选范围内只有几张发票时，这工作正常，但鉴于它每条记录至少调用该函数三次，当我在一天以上的时间段内生成报告时，性能确实会下降（我们通常需要涵盖两周的报告）。

不幸的是，鉴于它是第三方产品（任何好奇的人都可以使用 inFlow 库存），我无法更改表结构。

有什么方法可以使用更高效的连接、派生表（我理解这个概念，但从未做过）甚至重写整个查询以大大提高性能？

Answer 1

看来我已经设法解决了自己的问题，只是需要更多的研究，横向思维，很多失败的尝试和诅咒词（哦，这么多诅咒词！

我考虑在这个程序的 delphi 方面添加额外的步骤，这些步骤将根据我需要的日期范围从成本价格表中进行选择，然后重写我的原始查询以合并加入的新表。但是，如果您在此过程中没有学习任何新技能，那么解决问题就没有那么有趣了;-）。

答案是：TVF-表值函数。经过对替代方法的大量研究，我偶然发现了这些TVF。进一步的调查似乎表明，由于优化器处理标量函数的方式与 TVF 的相反，它们在某些应用程序中非常快，所以我决定重写我的函数：

CREATE FUNCTION dbo.CostAtDate ( @costdate AS datetime , @product AS int )
RETURNS table
AS
Return (
  Select Top 1
    BASE_InventoryCostLogDetail.AverageCostAfter
  From
    BASE_InventoryCostLogDetail
  Where
    CreatedDttm < @costdate And CreatedDttm > DATEADD(month,-1,@costDate) And
    ProdId = @product
  Order By
    CreatedDttm Desc)

而不是称其为传统方式

dbo.CostAtDate(SO_SalesOrder.OrderDate, SO_SalesOrderInvoice_Line.ProdId)

我将查询中它的所有引用重新调整为：

(Select * from dbo.CostAtDate(SO_SalesOrder.OrderDate, SO_SalesOrderInvoice_Line.ProdId))

测试它，我发现性能显着提高（4k+ 记录为 65 秒，与以前的函数相反，即使预期的结果集为 ~10k 条记录，通常也会在几分钟后超时。

我相信你们中的很多人都知道一个更好的方法，但目前这很有效......而这一切都是我自己发现的：向我致敬！