Форум: "Базы";
Текущий архив: 2002.10.28;
Скачать: [xml.tar.bz2];
ВнизКак лучше сделать Найти похожие ветки
← →
Осирис (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
И т.д.
Страницы: 1 вся ветка
Форум: "Базы";
Текущий архив: 2002.10.28;
Скачать: [xml.tar.bz2];
Память: 0.51 MB
Время: 0.009 c