SAP или 1С?

[Recoilme]

PHP код:

while select  ItemId, Sum(Qty) from It
    group by ItemId
    Where (It.DatePhysical < startDate) && (It.StatusReceipt==StatusReceipt::Purchased || It.StatusReceipt==StatusReceipt::Received || It.StatusReceipt==StatusReceipt::Registered) ||
        (It.StatusIssue==StatusIssue::Deducted || It.StatusIssue==StatusIssue::Picked || It.StatusIssue==StatusIssue::Sold) 


хинт заставляет мс скуэль юзать составной индекс итемид/датефизикал на условии датефизикал. В результате разница в быстродействии с Хинтом / без хинта составляет >1,5 часа(не дождался, снял задачу)/ 1,5 минуты соответственно.

ЗЫ: без хинта - план по инвенттрансу - натурал

[Ronin]

2 Recoilme:
Ваш пример заинтриговал меня :-)
Чтобы проверить влияние данной опции на производительность я запустил подобный запрос в Query Analyzer'е.

select ItemId, Sum(Qty)
from InventTrans
Where DatePhysical < '20060301'
and ( StatusReceipt = 1 or
StatusReceipt = 2 or
StatusReceipt = 3 or
StatusIssue = 1 or
StatusIssue = 2 or
StatusIssue = 3)
group by ItemId
--option (fast 1)


Вот какая картинка получилась (картинка во вложении). Как видно из нее - индекс используется в обоих случаях один и тот же. Время тоже сопоставимо. Более того, запрос с опцией оказался даже чуть более эффективным. Видимо дело тут не все же не в опциях...

[Recoilme]

Вы слишком хорошо думаете об аксапте. Надо выполнить запрос из аксапты и извлечь его из профайлера, а потом убрать хинт оптион фаст и удивиться. Запрос из аксапты будет отличен от того что Вы думаете, что аксапта напишет

[Ronin]

В случае селекта из одной таблицы он будет отличаться только большим количеством скобочек. Кстати, индекс, который используется при выполнении вышеприведенных запросов - сложный, который строится по 7ми полям, первые 2 как раз itemID и datePhysical. Возможно именно это объясняет высокую эффективность данного индекса в этом запросе. Впрочем, если Вы получаете иные результаты, интересно было бы посмотреть на запрос, который запускает аксапта с планом и на структуру используемого индекса.

Плохо о ней я начинаю думать при составлении сложных запросов. Там да, она может запрос с outer join представить двумя вложеными запросами типа
while select
{
while select
{
}
}
В свое время меня это очень неприятно удивило.

[Recoilme]

Цитата:

Сообщение от Ronin

В случае селекта из одной таблицы он будет отличаться только большим количеством скобочек.

Не только. Ещё ордер баем будет отличаться. И соответственно - планом.

Мне некогда гонять этот запрос опять, сорри. В свое время я на эту проблему целый день убил пока разобрался. Если интересно - проверьте сами.

Вообще если эту тему разроете чуть глубже - и получите отличные от моих результаты на реальном тесте, а не на абстракном запросе из QA - обещаю подключиться ибо хотелось бы подетальней разобраться в причинах

[Ronin]

Добрый день, Recoilme!

Не хочу показаться занудным, но отдаю себе отчет в том что данное письмо будет засчитано именно как махровое занудство :-)

Вы писали "хинт заставляет мс скуэль юзать составной индекс итемид/датефизикал на условии датефизикал. В результате разница в быстродействии с Хинтом / без хинта составляет >1,5 часа".

Я же хотел сказать, что само по себе выбор составного индекса не может практически на 2 порядка "тормознуть" выполнение данного запроса. Проиллюстрировал это на примере.
Order by по тому же полю, что и group by в данном случае на выбор оптимизатором индекса не повлияет. Иначе это уже будет огромный камень в огород MS SQL и поводом к выпуску хотфикса, а не генератора запросов Аксапты. На мой взгляд причина описаной вами проблемы лежит в плоскости оптимизации используемой базы данных.

Чтобы не осталось белых пятен, могу сказать, что ваш запрос, запущенный в Job'е из Аксапты отработал не медленнее, чем его аналог в Query Analyzer'e. При этом тестировались 2 разновидности запроса с разными условиями во WHERE:
(Проверка даты) && (Статусы прихода || Статусы расхода)
и ваш запрос
(Проверка даты) && (Статусы прихода) || (Статусы расхода)


Есть не мало примеров тому, когда у оптимизатора "сносило крышу". Сносить ее может начать например из за большого количества неэффективных индексов по таблице. Однако на практике я наблюдал подобные эффекты на более сложных запросах, чем селект с группировкой из одной таблицы.

[Recoilme]

Маленькое замечание: "тестиррование проводилось на реальных объемах, индексы штатные (ItemIdx)"

[Recoilme]

что то видно плоховато, дубль 2

[Ronin]

Здравствуйте, Recoilme!

С нетерпением ждал Вашего ответа. Мое подозрение на счет природы вашей проблемы окрепло. :-)

Насколько я понимаю, индех ItemIdx не изменялся в процессе внедрения? То есть он остался в том виде, в котором создался при инсталляции системы. Если так, то его структура должна быть следующей:
DataAreaID
ItemID
datePhysical.

Теперь, основываясь на имеющихся знаниях, проанализируем ваши запросы и их планы.
select sum(qty), itemID
from inventTrans
where datePhysical<'20041001'
group by itemID
order by itemID

оптимизатор не может найти эффективный индекс для выполнения данного запроса и решает, что раз надо получить все записи, то full scan будет достаточно оптимальным. Использовать индекс ItemIdx он не может по той простой причине, что в запросе отсутствует условие/сортировка/группировка по DataAreaID.

Если мы добавляем в конце опцию FAST 20, оптимизатор решает, что использование хоть какого-то индекса позволит быстрее получить первую партию записей. Напрасно считает :-) Но не его это вина и уж тем более не вина Аксапты. Просто для данного запроса ОТСУТСТВУЕТ эффективный индекс. По факту получается, что самым оптимальным планом является использование Full scan'а.

Что можно сделать.
1. Добавить в инструкцию WHERE фильтр по компании. Или добавить группировку по компании. Плохо, что этот вариант не позволит повысить селективность индекса, но может помочь.
2. Если компания используется одна, то есть смысл вообще убрать из индекса поле DataAreaID.
3. Создать еще один индекс со следующими полями: itemID, datePhysical, и коль мы часто используем в запросе наложения условий по статусам прихода и расхода, то включить в него StatusIssue, StatusReceipt.

При использовании индекса подобного тому, что описан в пп.3, ваш запрос обрабатывался на реальных объемах (15 000 000 записей) 1 минуту и 4 секунды.

Про индексы очень хорошо описано в статье http://www.sql.ru/articles/mssql/03013101Indexes.shtml

Надеюсь мне удалось немного поднять репутацию Аксапты ;-)

[mazzy]

Ronin, спасибо.

Только, пожалуйста, будьте осторожны вот с такими советами...

Цитата:

Сообщение от Ronin

2. Если компания используется одна, то есть смысл вообще убрать из индекса поле DataAreaID.

Штатными средствами этого сделать не получится. А использование нештатных средств может сильно навредить новичкам.

Делать таблицу номенклатуры общей только для повышения производительности... все равно что лечить головную боль гильотиной.

А с остальным сов.согласен.

[mifi]

Цитата:

Сообщение от mazzy

Ronin, спасибо.

Только, пожалуйста, будьте осторожны вот с такими советами...

Штатными средствами этого сделать не получится. А использование нештатных средств может сильно навредить новичкам.

Делать таблицу номенклатуры общей только для повышения производительности... все равно что лечить головную боль гильотиной.

А с остальным сов.согласен.

Я тоже не стал бы советовать убирать DataAreaId, но почему это нельзя сделать штатными средствами, непонятно - свойство SaveDataPerCompany чем не штатное средство?

[mazzy]

Цитата:

Сообщение от mifi

свойство SaveDataPerCompany чем не штатное средство?

Этого я и боялся...
Не спорю, штатное. Но я же говорил - это все равно, что лечить мигрень гильотиной... Да, совершенно верно, голова больше болеть не будет...

[AndyD]

2 Recoilme

А в вашем запросе все правильно?
Вы выбираете проводки (до даты и со статусом прихода закуплено, получено или зарегистрировано) или (со статусом расхода отпущено, скомплектовано или продано).
Т.е. у вас выбираются все расходные проводки, а приходные проводки только на дату.

[mifi]

Цитата:

Сообщение от mazzy

Этого я и боялся...
Не спорю, штатное. Но я же говорил - это все равно, что лечить мигрень гильотиной... Да, совершенно верно, голова больше болеть не будет...

В смысле? При чем тут гильотина? Если у клиента одна компания и так будет очень долго и он принимает обдуманное решение - я не вижу здесь никакого криминала, с учетом того, что как мы уже установили, это можно сделать штатными средствами разработки.

[Recoilme]

Цитата:

Сообщение от AndyD

2 Recoilme

А в вашем запросе все правильно?
Вы выбираете проводки (до даты и со статусом прихода закуплено, получено или зарегистрировано) или (со статусом расхода отпущено, скомплектовано или продано).
Т.е. у вас выбираются все расходные проводки, а приходные проводки только на дату.

Это просто пример несложного запроса и неэффективности повсеместного использования аксой оптионфаста, условия по статусам - вообще убраны. Корректный вариант этого запроса можно найти по ссылке в подписи в уголке проктолога

[Recoilme]

Цитата:

Сообщение от mifi

В смысле? При чем тут гильотина? Если у клиента одна компания и так будет очень долго и он принимает обдуманное решение - я не вижу здесь никакого криминала, с учетом того, что как мы уже установили, это можно сделать штатными средствами разработки.

+1
У нас именно так и сделано. Сэйвдатаперкомпани отключен. В нашем случае гильотиной было бы оставить датаареаид ...

[Recoilme]

Цитата:

Сообщение от Ronin

Здравствуйте, Recoilme!
Надеюсь мне удалось немного поднять репутацию Аксапты ;-)

Соб-но репутация аксапты мне безразлична Мы работаем на ней и я считаю что это достаточно эффективный инструмент, позволяющий решать задачи.
Рано или поздно. Так или иначе.
В отличие от тех систем где задачи либо вообще невозможно решить, либо придется переписывать вообще всё для их решения
Но и особо восторгаться почему то не тянет
Надеюсь что когда-ть появятся настоящие клиент-серверные системы в моем имховском понимании этого словосочетания. На которых можно будет приятно и комфортно решать поставленные задачи не парясь по поводу быстродействия, глючков и багофичей .
Пока я вижу такие только в зародышевом состоянии. Вобщем сильно тянет в оффтопик, завязываю.
Аксапта вобщем рулит, только иногда на поворотах заносит.

[Recoilme]

2 Ronin;

Чего то я с этими индексами совсем запутался.. Может быть подскажете эффективный индекс для данного запроса (с оптионфаст) ???
Потому что вроде у нас как раз такой индекс который Вы описываете (без датаареаид). Индекс по одинокой датефизикал оптимизатор тоже не хочет использовать, наверно из-за инструкции орддербай по итемид.

[Ronin]

Почему бы не подсказать, подскажу.

В дереве AOT это поле не отображается. Но если вы посмотрите на этот индекс из Enterprize Manager'a или Query Analyzer'a, то его увидите. AOT - не лучшее средство для анализа и Perfomance tuning'а. В дереве AOT вы не увидите и некоторых полей в таблицах, которые однако существуют физически и отображаются в обозревателе таблицы или паспорте записи.

Как избавиться от данного поля - есть 2 пути.

1. Как написал mifi, используйте свойство SaveDataPerCompany. Это свойство таблицы. Благодаря данному свойству вы можете избавиться от поля dataareaid и в таблице и в индексе.
2. Создайте свой индекс.

Почему оптимизатор не строит план запроса с использованием данного индекса. Order by тут ни причем. Более того, использование order by наоборот побуждает использовать индекс, в котором имеется данное поле. Вот цитата из той ссылки что я вам давал ( http://www.sql.ru/articles/mssql/03013101Indexes.shtml ):


16.3

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

Например, если создается индекс по полям Field1, Field2, то он может быть применен только в запросе где в критериях используются оба этих поля. Так же этот индекс будет полезен для условий, построенных для одного Field1. Для одного Field2 этот индекс не может быть применен.

Так же обратите внимание на обслуживание статистики. Пункт 14.

[Recoilme]

Цитата:

Сообщение от Ronin

Почему бы не подсказать, подскажу.

В дереве AOT это поле не отображается. Но если вы посмотрите на этот индекс из Enterprize Manager'a или Query Analyzer'a, то его увидите. AOT - не лучшее средство для анализа и Perfomance tuning'а. В дереве AOT вы не увидите и некоторых полей в таблицах, которые однако существуют физически и отображаются в обозревателе таблицы или паспорте записи.

У нас нет этого поля. Выше я ответил Михаилу, что именно так у нас и отключено.

Искусственный интелект оптимизатора подсказывает, что в данном запросе эффективного индекса быть не может. Что ему можете противопоставить Вы?