Главная страница
    Top.Mail.Ru    Яндекс.Метрика
Форум: "Базы";
Текущий архив: 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.63 MB
Время: 0.043 c
3-92353
KidMan
2002-09-30 22:16
2002.10.28
Соеденение с MSSQL


3-92330
Hirara
2002-10-04 22:29
2002.10.28
Поле типа Time


14-92667
Jumbot
2002-10-05 20:08
2002.10.28
СРОЧНО!!! HELP ME!!! SOS!!!


1-92430
Stainer
2002-10-17 16:22
2002.10.28
вопрос по RXLib


3-92408
Sergey V. Shadrin
2002-09-27 09:47
2002.10.28
проблема с BDE или еще чем-то...





Afrikaans Albanian Arabic Armenian Azerbaijani Basque Belarusian Bulgarian Catalan Chinese (Simplified) Chinese (Traditional) Croatian Czech Danish Dutch English Estonian Filipino Finnish French
Galician Georgian German Greek Haitian Creole Hebrew Hindi Hungarian Icelandic Indonesian Irish Italian Japanese Korean Latvian Lithuanian Macedonian Malay Maltese Norwegian
Persian Polish Portuguese Romanian Russian Serbian Slovak Slovenian Spanish Swahili Swedish Thai Turkish Ukrainian Urdu Vietnamese Welsh Yiddish Bengali Bosnian
Cebuano Esperanto Gujarati Hausa Hmong Igbo Javanese Kannada Khmer Lao Latin Maori Marathi Mongolian Nepali Punjabi Somali Tamil Telugu Yoruba
Zulu
Английский Французский Немецкий Итальянский Португальский Русский Испанский