Как лучше сделать

← →
Осирис (2002-10-04 12:07) [0]

Есть большой запрос вида:

Select ........
from T1
left outer join T2
left outer join T3
left outer join T4
left outer join T5
where ................

Запрос выполняется очень долго. Если преобразовать запрос к такому виду:

Select ........
from T1
left outer join T2
left outer join T3
left outer join T4
where ................ (*)

запрос работает раз в 5 быстрее.

Для решения этой проблемы я вижу 2 решения:

1. Использовать второй запрос, а для получения недостающих полей из T5 использовать вычислимые поля, в которых будут посылаться запросы на сервер. То есть нагрузка ложится на сервер в виде большого количества небольших запросов.

2. Использовать второй запрос (назовем его *). Кроме него на сервер посылать сразу еще один запрос

select id, text from T5 where ..... (**)

В Query1 (тот который *) делаем вычисляемое поле (все как и в первом варианте), но в onCalc... уже обращаемся не на сервер, а фильтруем Query2 (**). Соответственно нагрузка ложится на клиента.

Какой из способов лучше ? Или кто-то посоветует что-то свое ?

← →
ЮЮ (2002-10-04 12:25) [1]

Cервер-то какой?

← →
Осирис (2002-10-04 12:26) [2]

Сорри. MS SQL 7.0

← →
Johnmen (2002-10-04 12:27) [3]

1. По возможности не использовать внешних соединений
2. Попытаться разбить один большой запрос на неск.небольших
3. Что быстрее работает, то и лучше !

← →
ЮЮ (2002-10-04 12:47) [4]

Вместо
Select ........
from T1
left outer join T2
left outer join T3
left outer join T4
left outer join T5
where ................
использовать
Select ........
from (select * from T1 where ................) T1
left outer join T2
left outer join T3
left outer join T4
left outer join T5
т.е. сначала отобрать необходимые записи, а затем уже подцеплять справочники и всё остальное

← →
Осирис (2002-10-04 12:54) [5]

>> ЮЮ

Знаешь, я тоже сначала так подумал. Но время выполнения запроса от этого не уменьшилось. Я так понимаю оптимизатор запросов сам выполняет подобную операцию.

← →
ЮЮ (2002-10-04 13:12) [6]

left outer join T5 on t5.keyField = ...
надеюсб keyField - поле, по которому постоен первичный ключ?

да и на реальный запрос посмотреть не мешало бы :-)

← →
Осирис (2002-10-04 13:52) [7]

>> надеюсь keyField - поле, по которому постоен первичный ключ?

К счастью ключ есть. Понимаешь, сложность в том, что база данных уже спроектированна и не нами, и менять ее мы не можем. Я делаю отчет по этой базе.

>> да и на реальный запрос посмотреть не мешало бы

-----------------------------------------------------

declare @beginDate datetime
declare @endDate datetime
declare @itemId varchar(30)
declare @itemName varchar(50)
declare @custVendAC varchar(3)
declare @Name varchar(50)
declare @TransRef varchar(20)
set @beginDate = "2002-7-1"
set @endDate = "2002-7-30"
set @itemId = "%"
set @itemName = "%"
set @custVendAC = "%"
set @name = "%"
set @TransRef = "%"

select DatePhysical, InventTrans.ItemId, ItemName, CustVendAC, Name, TransRefId, TransType,
NumDoc =
case InventTrans.TransType
when 3 then InventTrans.PackingSlipId
else InventJournalTrans.dem_NumDoc
end,
Ceh =
case InventTrans.TransType
when 3 then " "
when 5 then InventJournalTrans.Dimension
when 6 then InventJournalTrans.ToInventDimId
end,
sum(InventTrans.Qty) Qty, InventTableModule.UnitId,
CostByOne =
case sum(InventTrans.Qty)
when 0 then 0
else
sum(case when costAmountPosted=0 then costAmountPhysical else costAmountPosted end)/sum(InventTrans.Qty)
end,
sum(case when costAmountPosted=0 then costAmountPhysical else costAmountPosted end)
from InventTrans
left outer join VendTable on (InventTrans.CustVendAC=VendTable.AccountNum)
left outer join InventJournalTrans on (InventJournalTrans.InventTransId = InventTrans.InventTransId)
left outer join InventTable on (InventTrans.ItemId=InventTable.ItemId)
left outer join InventTableModule on (InventTableModule.ItemId = InventTrans.ItemId)
where InventTableModule.ModuleType = 0 and
(
TransType = 5 or
TransType = 6 or
(TransType = 3 and ( (StatusReceipt = 1) or (StatusReceipt = 2) ) )
)
and InventTrans.InventTransIdReturn = ""
and DatePhysical>=@beginDate and DatePhysical<@endDate
and InventTrans.ItemId like @itemId

and ItemName like @itemName
and ltrim(CustVendAC) like @custVendAC
and ((Name like @Name) or Name is Null)
and ltrim(TransRefId) like @TransRef
and InventTrans.Qty<>0

group by DatePhysical, InventTrans.ItemId, ItemName, CustVendAC, Name, TransRefId, UnitId, TransType, PackingSlipId,
dem_NumDoc, InventJournalTrans.Dimension, ToInventDimId
order by DatePhysical

---------------------------------------------------------------

А что, мои два способа совсем не катят ? :)

← →
ЮЮ (2002-10-04 13:56) [8]

Сорри, рабочее (и нерабочее) время уже вышло. Контора закрывается.

← →
ЮЮ (2002-10-05 08:49) [9]

К сожалению, у полей не указаны таблицы, что не позволяет реально оценить связь приведённых пяти таблиц.
Но исходя из того, что четыре последних таблицы связаны только с первой, можно полагать, что все они являются "справочниками" для полей в 1 таблице.
Если же они являются Master или Detail-таблицами для InventTrans ,
то в этом случае будет неправильно вычисляться Sum(InventTrans.Qty).

Итак, первый, и возможно, главный тормоз - это group by по 12 полям.
Хотя реальных полей группировки, наверное всё же меньше.
часть из этих полей - подстановка из справочников,которые надо подключать после выполнения группировки,
а часть включена для того, чтобы можно было использовать в выражениях типа:
Ceh =
case InventTrans.TransType
when 3 then " "
when 5 then InventJournalTrans.Dimension
when 6 then InventJournalTrans.ToInventDimId
end
т.е. к самодостаточным InventTrans.TransType и InventTrans.InventTransId в группировку искуственно "притянуты" ещё и InventJournalTrans.Dimension и InventJournalTrans.ToInventDimId,
которые можно было бо добавить в запрос после группировки.

Поэтому, прежде, чем продолжать, хотелось бы прояснить вышеизложенные сомнения и предположения.

>А что, мои два способа совсем не катят ? :)
Это зависит от того, что в T2-T5.

← →
Осирис (2002-10-07 10:13) [10]

>> Но исходя из того, что четыре последних таблицы связаны только с первой, можно полагать, что все они являются "справочниками" для полей в 1 таблице.

Да действительно - это справочники.

>> К сожалению, у полей не указаны таблицы, что не позволяет реально оценить связь приведённых пяти таблиц.

В select все проля из InventTrans кроме:

InventName из InventTable
Name из VendTable

>> Итак, первый, и возможно, главный тормоз - это group by по 12 полям. Хотя реальных полей группировки, наверное всё же меньше.

Да действительно. Группировка нужно только по DatePhysical, InventTrans.ItemId. Но чтобы включить остальные поля в select пришлось их включить и в group by.

Не подскажите, где можно почитать про то, как составлять ОПТИМАЛЬНЫЕ запросы. Интересуют именно примеры, "что так делать конечно можно, но вот так будет быстрее и лучше."

Спасибо за помощь.

← →
ЮЮ (2002-10-07 12:06) [11]

>Группировка нужно только по DatePhysical, InventTrans.ItemId. Но чтобы включить остальные поля в select пришлось их включить и в group by

Да, но остальные поля, будучи включенными в группировку, мельчат эти самы группы. Или у всех записей, с одним и тем же DatePhysical, InventTrans.ItemId все остальные поля совпадают? Так это или нет?

← →
3JIA9I CyKA (2002-10-07 12:23) [12]

2Осирис
Запрос написан с перепугу, что ли? Тебе его нужно переписывать нАфИнГ весь. А мне лениво. И зря ты не привёл струкатуру таблиц. Надо было.

← →
ЮЮ (2002-10-07 12:28) [13]

А если они всё-таки разные, а группировки нужны только по DatePhysical, InventTrans.ItemId, то запрос в принципе не верен. Что бы помочь составить правильный запрос, всё-таки надо знать связи твоих таблиц, иначе это гадание на кофейной гуще.

>Не подскажите, где можно почитать про то, как составлять ОПТИМАЛЬНЫЕ запросы.
Я это уяснял методом проб и ошибок :-)

← →
3JIA9I CyKA (2002-10-07 12:34) [14]

2ЮЮ
>>Я это уяснял методом проб и ошибок :-)
Это единственный (известный мне) "работающий" способ.

← →
Осирис (2002-10-07 14:21) [15]

>> Или у всех записей, с одним и тем же DatePhysical, InventTrans.ItemId все остальные поля совпадают? Так это или нет?

Да это так. Совпадают.

Насчет структуры таблиц:

Есть таблица складских проводок:

DatePhysical - дата проводки
ItemId - идентификатор номенклатуры
CustVendAC - идентификатор поставщика
TransRefId - служебная информация (для запроса это не важно)
TransType - тип проводки
Qty - количество
costAmountPhysical - сумма по накладной
costAmountPosted - сумма по счету

К этой таблице подсоединяются справочники номенклатур InventTable:

ItemId - идентификатор номенклатуры
ItemName - наименование номенклатуры

Справочник поставщиков:

CustVendAC - идентификатор поставщика
Name - наименование поставщика

Кроме того в результатирующем наборе должны отображаться:

Номер документа

NumDoc =
case InventTrans.TransType
when 3 then InventTrans.PackingSlipId
else InventJournalTrans.dem_NumDoc
end,

И цех

Ceh =
case InventTrans.TransType
when 3 then " "
when 5 then InventJournalTrans.Dimension
when 6 then InventJournalTrans.ToInventDimId
end,

которые зависят от типа проводки TransType

Есть таблица InventTableModule:

ItemId - идентификатор номенлатуры
ModuleType - состояние номенклатуры
UnitId - единица измерения

sum(case when costAmountPosted=0 then costAmountPhysical else costAmountPosted end) -

это мы берем стоимость.

Это мы вычисляем цену:

CostByOne =
case sum(InventTrans.Qty)
when 0 then 0
else
sum(case when costAmountPosted=0 then costAmountPhysical else costAmountPosted end)/sum(InventTrans.Qty)
end,

where InventTableModule.ModuleType = 0 - говорю, что нас интересует ном-ра только в состоянии 0.

← →
ЮЮ (2002-10-08 04:59) [16]

>>>> Или у всех записей, с одним и тем же DatePhysical, InventTrans.ItemId все остальные поля совпадают? Так это или нет?
> Да это так. Совпадают.

Ты уверен? Разве не может быть так:
Есть одна проводка, в которой есть некий ItemId1, у которой поставшик CustVendAC1. Есть другая проводка, в которой тот же ItemId1, но поставщик другой ItemId2.
Какую стоимость ты хочешь получить в данном случае, среднюю по всеи поставщикам? Если да, то Name из VendTable получить уже невозможно, т.к. CustVendAC1 должно быть исключено из группировки.
> Кроме того в результатирующем наборе должны отображаться Номер документа и Цех
Это опять камнь в вышеиздоженный огород.

Итак,начнём с более сложного запроса, когда нужны поставщик,документ,цех и т.д., но стоимость идентификатора номенклатуры должна быть средняя за день , мы должны:
1) Получить среднюю стоимость идентификатора номенклатуры за период:

SELECT DatePhysical,ItemId,SUM(Qty) Qty,(SUM(case when costAmountPosted=0 then costAmountPhysical else costAmountPosted end) Cost
FROM InventTrans
WHERE DatePhysical>=:DateFrom AND DatePhysical<=:DateTO
AND ItemId IN
(SELECT ItemId FROM InventTableModule WHERE InventTableModule.ModuleType=0)
GROUP BY DatePhysical,ItemId

Можно попробовать без IN (SELECT и сравнить какой запрос работает быстрее, т.е.

SELECT DatePhysical,InventTrans.ItemId,SUM(Qty) Qty,(SUM(case when costAmountPosted=0 then costAmountPhysical else costAmountPosted end) Cost
FROM InventTrans,InventTableModule
WHERE DatePhysical>=:DateFrom AND DatePhysical<=:DateTO
AND InventTableModule.ItemId=InventTrans.ItemId
AND InventTableModule.ModuleType=0
GROUP BY DatePhysical,ItemId

Теперь опять выбираем нужные строки из InventTrans, прицепляем справочники и полученные средние цены:

SELECT <нужные поля> from
InventTrans,InventTable,....,(наш запрос) MIDDLEPRICE
Where InventTrans.ItemId=InventTable.ItemId AND ... MIDDLEPRICE.ItemId=InventTable.ItemId AND MIDDLEPRICE.DatePhysical=InventTable.DatePhysical

Если нужна средняя цена не за день, а за весь месяц, то убираем из запроса группировку по DatePhysical и убираем из связки AND MIDDLEPRICE.DatePhysical=InventTable.DatePhysical

Если нужна средняя цена по каждому поставщику, до в группировку добавляем CustVendAC
и добавляем его в связку and MIDDLEPRICE.CustVendAC=InventTable.CustVendAC

И т.д.

Как лучше сделать Найти похожие ветки