Ускорение выполнения запроса Oracle + MS Axapta

[Горбунов Дмитрий]

Всем привет!

Проблема: как ускорить быстродействие выполнения запроса цивилизованным методом???

Наличие индекса DATAAREAID снижает скорость выполнения запроса в 50 раз (24 ч. и 30 мин.). Однако, без этого поля я не могу дублировать данные компании в системе Аксапта (одна таблица на все компании).

Варианты решения проблемы:

1. Отключаю на всех критичных таблицах параметр SaveDatePerCompany. И работаю только в одной компании.

Минусы:
1. В качестве дублирования данных я могу использовать только механизм DUMP системы Oracle
2. Т.к. компания одна, а транзакций целая туча RECID быстро переполнится (1 год)

Плюсы:
1. Скорость выполнения запросов приемлимая

2. Очень дикий способ: пусть на всех таблицах стоит параметр SaveDatePerCompany.
2.1. Я средствами Oracle очищаю индексы от DATAAREAID на критичных таблицах
2.2. Запускаю необходимый запрос
2.3. Восстанавливаю индексы DATAAREAID средствами Oracle (синхронизация базы данных в MS Axapta приводит к аналогичному результату)

Минусы:
1. Реакция MS Axapta непредсказуемая (хотя у меня всё проходило успешно)

Плюсы:
1. Скорость выполнения запросов приемлимая
2. RECID нас не ограничивает, т.к. кол-во компаний не ограничено


Подскажите как можно заменить 2 способ на более цивилизованный???

Всем спасибо

[Андре]

Цитата:

Наличие индекса DATAAREAID снижает скорость выполнения запроса в 50 раз (24 ч. и 30 мин.).

А запросом и планом его выполнения с общественностью не поделишься ?

[Recoilme]

Если речь идет о глобальном ускорении всех запросов - то лучше способ 1. Мы не храним данные по компаниям вообще.
Если конкретный запрос - то в редких случаях я делаю полный инсертрекордсет нужных данных в таблицу своей структуры, правда не для быстродействия больше, а чтоб от блокировок не зависеть (ms sql). Но эти случаи настолько редки... может проще запрос оптимизировать?

[Горбунов Дмитрий]

Для пример был взят следующий маленький подзапрос, задействованный в "большом" запросе:

select level,A_TYPE_COD, A_TYPE_NAME from bmssa.A_TYPE A
where DataAreaId ='tst' --внешний параметр
start with A_TYPE_MR_COD =0 --внешний параметр
connect by prior A_TYPE_COD=A_TYPE_MR_COD


Данный запрос будет выполняться только средствами Oracle.
MS SQL Server никогда не слышал о connect by prior.


Запрос запускался через класс Connection в MS Axapta.

На таблице стояли 2(3) индекса:
0. (DataAreaId)
1.A_TYPE_MR_COD (+DataAreaId)
2.A_TYPE_COD и A_TYPE_MR_COD (+DataAreaId)


Время выполнения запроса:
1. Без индекса DataAreaId - 0.016 сек (1 и 2 индекс без DataAreaID)
2. С индексом DataAreaId - 1.000 сек (0,1 и 2 индекс с DataAreaID)

Боюсь что оптимизировать в данном случае просто нечего.

[Recoilme]

а что это за таблица???
ну так грохните на ней ненужные индексы..

[Roman. ~RVS]

Цитата:

Сообщение от Горбунов Дмитрий

Для пример был взят следующий маленький подзапрос, задействованный в "большом" запросе:

select level,A_TYPE_COD, A_TYPE_NAME from bmssa.A_TYPE A
where DataAreaId ='tst' --внешний параметр
start with A_TYPE_MR_COD =0 --внешний параметр
connect by prior A_TYPE_COD=A_TYPE_MR_COD

привет! помню-помню такое. 8-)
я так понимаю, индексы просто не используются. попробуй включить в запрос дополнительные условия по коду компании:

select level,A_TYPE_COD, A_TYPE_NAME from bmssa.A_TYPE A
where DataAreaId ='tst'
start with A_TYPE_MR_COD =0 and DataAreaId ='tst'
connect by prior A_TYPE_COD=A_TYPE_MR_COD and and DataAreaId ='tst'

единственно, что не помню, можно ли так писать в запросе... оракла под рукой нет проверить.

[AndyD]

А что это у вас за такая великая иерархия, что на ней убъется RecID в одной компании?

[Горбунов Дмитрий]

Таблица - самописная для работы с графом.
По поводу ненужные индексы - см.верхний текст п.1.

Если я убиваю индекс DataAreaId средствами MS Axapta (параметр SaveDatePerCompany), т.о. я ограничен RecID (вычисления при проходе по графу приводят к формированию от 1000 записей для самой простой схемы для 1 итерации, а таких графов у меня около 1000, после нескольких итераций RecId занчительно увеличивается, а предыдущие итерации удаляются).

Если я убиваю индекс DataAreaId средствами Oracle, синхронизация в MS Axapta на одном из рабочем мест приведет к восстановлению индекса (постоянное отслеживание индекса - DataAreaID - дело не благодарное).
Имея индекс DataAreaId я могу сделать "псевдодамп" средствами MS Axapta (функция Дублировать компанию). Например, раз в год перекидывать компнию 2004 -> 2005, 2005 -> 2006 и т.о. RecId в новой компании будет запускаться заново

[Андре]

Цитата:

Наличие индекса DATAAREAID снижает скорость выполнения запроса в 50 раз (24 ч. и 30 мин.).

Цитата:

select level,A_TYPE_COD, A_TYPE_NAME
from bmssa.A_TYPE A .....

1) Попробуй собрать статистику. Как я понимаю, в твоем случае полное сканирование таблицы более выгодно, чем доступ по индексу. Хм... У тебя большая часть записей принадлежит этой компании (tst) ? В любом случае, я начал бы со сбора статистики. Кстати план запроса ты так и не привел - поэтому все что я пишу - это гадание на кофейной гуще.

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

3) Кроме того, можно использовать Oracle Outline (Стабилизация плана запроса), когда ты для любого запроса можешь указать БД способ его выполнения.

[Recoilme]

сложно всё как то
наверно план запроса - "натурал" или как там в оракле называется

[Recoilme]

Цитата:

Сообщение от Горбунов Дмитрий

Время выполнения запроса:
1. Без индекса DataAreaId - 0.016 сек (1 и 2 индекс без DataAreaID)
2. С индексом DataAreaId - 1.000 сек (0,1 и 2 индекс с DataAreaID)

Боюсь что оптимизировать в данном случае просто нечего.

гы. попробовать избавиться от подзапросов в принципе
или указать план запроса от варианта N 1 для варианта N 2

[Андре]

Цитата:

сложно всё как то
наверно план запроса - "натурал" или как там в оракле называется

Не знаю. Мне даже сложно предположить, что вы имеете в виду, говоря " план запроса - "натурал"".

[Recoilme]

в интербэйсе - фуллскан

я не специалист по бд честно говоря , но решал бы так, в порядке получилось/не получилось:
1. попробовал бы избавиться от подзапросов в принципе через джоин
2. попробовал бы решить задачу не на скл а алгоритмически, скажем на аксапта
3. грохнул бы индексы по полю 1 и 2 и замерял бы
4. попробовал бы подсунуть план от 1 запросу 2
5. спросил совета у спецов

[Андре]

Цитата:

в интербэйсе - фуллскан

В Oracle тоже.

Цитата:

1. попробовал бы избавиться от подзапросов в принципе через джоин

А где там подзапросы то?

Цитата:

я не специалист по бд честно говоря , но решал бы так, в порядке получилось/не получилось:

А я бы сначала посмотрел план выполнения обеих запросов.

Кстати, как такая штука

Цитата:

попробовал бы подсунуть план от 1 запросу 2

в interbase делается ?

[Vadik]

Цитата:

Сообщение от Горбунов Дмитрий

Имея индекс DataAreaId я могу сделать "псевдодамп" средствами MS Axapta (функция Дублировать компанию). Например, раз в год перекидывать компнию 2004 -> 2005, 2005 -> 2006 и т.о. RecId в новой компании будет запускаться заново

Чтобы не постигло жестокое разочарование в 2006 году (а он уже не за горами), проверьте свою гипотезу о генерации RecId в новой компании с нуля сейчас

И перечитайте то, что Вам писал написал Roman. ~RVS. Хинт: поле DataAreaId по умолчанию всегда является первым в индексе, так что не использовать его для связи в запросе как минимум странно.

И, пожалуйста, постарайтесь использовать общепринятую терминологию. Не так легко понять, не читая ветку с самого начала, что Вы имеете в виду под "индексом DataAreaId"

И, наконец, согласитесь - не имея на руках DDL для таблиц, индексов, запроса и плана исполнения, ответить на вопрос "почему все плохо" тяжеловато

[AndyD]

Боюсь что индексы, которые создает Axapta не помогут (если разделять по компаниям). Необходимо создать индекс по полям A_TYPE_MR_COD, A_TYPE_COD и DATAAREAID в Oracle.

Кроме того, если не воспользоваться советом Roman. ~RVS, то у вас данные вернутся неверно, т.к. Oracle вернет вам дополнительные ветви для A_TYPE_MR_COD=0 в других компаниях

[Recoilme]

Цитата:

Сообщение от Андре

В Oracle тоже.
А где там подзапросы то?

Кстати, как такая штука
в interbase делается ?

1. Горбунов ДмитрийДля пример был взят следующий маленький подзапрос, задействованный в "большом" запросе:


2. Ручками. Думаю так же как в оракле, т.к. они очень похожи, есть даже клон оракл реализованный на firebird.
вобщем вот пример синтаксиса:
// Задаем план ручками, иначе Firebird как-то это криво планирует на больших базах
// PLAN ("SORT (JOIN (J1 INDEX (ACC_PROVOD_DEBIT_IDX,ACC_PROVOD_CREDIT_IDX), J2 INDEX (PRIMARY_ACC_HOZOP)))")
// отключил - не работает для счетов, имеющих субсчета

[Горбунов Дмитрий]

Я был не прав, не хватало DataAreaId ='tst' (мой косяк ):


select level,A_TYPE_COD, A_TYPE_NAME from bmssa.A_TYPE A
where DataAreaId ='tst'
start with A_TYPE_MR_COD =0 and DataAreaId ='tst'
connect by prior A_TYPE_COD=A_TYPE_MR_COD and DataAreaId ='tst'


Скорость без индекса DataAteaID - 0.003 сек (было 0.016)


По Explain Plan (план запроса) БД пробегает 2 раза TABLE ACCESS FULL


Оптимизатор запросов (SQL Expert Pro) предложил хинт (время выполнения тоже):

select /*+ INDEX_COMBINE(A) */level,A_TYPE_COD, A_TYPE_NAME from bmssa.A_TYPE A
where DataAreaId ='tst'
start with A_TYPE_MR_COD =0and DataAreaId ='tst'
connect by prior A_TYPE_COD=A_TYPE_MR_COD and DataAreaId ='tst'

По Explain Plan БД пробегает 1 раз TABLE ACCESS FULL - при увеличении данных в таблице применение хинта будет очивидным (т.к. проход всего лишь один)

Спасибо Андре за хинт.