Сложные запросы - будет ли объединение быстрее, чем подзапрос?

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 Inventory для всех, кому интересно), я не могу изменить структуру таблиц.

Есть ли способ использовать более эффективные объединения, производную таблицу (я понимаю концепцию, но никогда не делал этого) или даже переписать весь запрос, что значительно повысит производительность?

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))

Протестировав его, я обнаружил значительное увеличение производительности (4 секунды для записей более 65 тыс., В отличие от предыдущей функции, которая обычно отключалась через несколько минут, даже если ожидаемый набор результатов составлял ~10 тыс. Записей).

Я уверен, что многие из вас знают еще лучший способ, но на данный момент это работает хорошо.... и я нашел все это сам: спасибо мне!