Оптимизация запроса

[Alexius]

Вторая серия, для начала:

1. Индекс на таблицу inventTrans с 3-мя полями DateFinancial, DateExpected, InventDimId

2. Слегка модифицированный запрос

while select inventTrans
where ((inventTrans.TransType == InventTransType::Purch) || (inventTrans.TransType == InventTransType::Sales))
&& (includeEstimated || ( ! includeEstimated &&
((inventTrans.StatusReceipt == StatusReceipt::None && inventTrans.StatusIssue == StatusIssue::Sold) ||
(inventTrans.StatusReceipt == StatusReceipt::Purchased && inventTrans.StatusIssue == StatusIssue::None))))
&& (transByPeriod
&& ((inventTrans.DateFinancial
&& inventTrans.DateFinancial >= dateFrom
&& inventTrans.DateFinancial <= dateTo) ||
( ! inventTrans.DateFinancial
&& inventTrans.DateExpected >= dateFrom
&& inventTrans.DateExpected <= dateTo)) || (!transByPeriod))
join ItemCategoryId from inventTable
where inventTable.ItemId == inventTrans.ItemId &&
inventTable.ItemType != ItemType::.....
join DlvInventTripId from inventDim
where inventDim.InventDimId == inventTrans.InventDimId
&& (( ! tripId && inventDim.DlvInventTripId != "") || (tripId && inventDim.DlvInventTripId == tripId))
exists join DlvInventTripId from inventDimTripByPeriod
where inventDimTripByPeriod.[field] == inventDim.[field]
join inventTransByPeriod
where inventTransByPeriod.InventDimId == inventDimTripByPeriod.InventDimId
&& inventTransByPeriod.DateFinancial <= dateTo
&& ((inventTransByPeriod.DateFinancial
&& inventTransByPeriod.DateFinancial >= dateFrom) ||
( ! inventTransByPeriod.DateFinancial
&& inventTransByPeriod.DateExpected >= dateFrom
&& inventTransByPeriod.DateExpected <= dateTo))