Настройка индексов

[vesna dba]

Всем привет!

Мониторю длительные запросы SQL на пользователях.
В планах запросов наблюдаю TABLE SCAN например по таблице CUSTINVOICETRANS.
Смотрю через репозитарий свойства таблицы
[attachment=603:CustInvoiceTrans.JPG]
Наблюдаю отсутсвие PrimaryIndex, ClusterIndex
(и во многоих таблицах, по которым встречаю в планах запросов сканирование таблиц такое положение)

В тестовой базе например делаю имеющийся индекс по полю InvoiceId как кластерный,
повторно запускаю отчет, мониторю, смотрю план запроса - сканирования таблицы уже нет.
Соответственно нет уже огромной очереди на диске, где отдельно выложил данную таблицу (даже отдельно она лежит от своих индексов).
Все это хорошо, НО не хотелось бы огрести проблем в другом месте, так как все сделано чисто интуитивно.

Прошу меня наставить на путь истинный, дать ссылки на доки (теорию по индексам знаю , практики мало) и т.п., какую инфу требовать от программистов.
Наверняка кто то уже занимался этой проблемой.
Потери производительности на этой неоптимальности просто огромные, особенно когда в конце месяца активно работают продажники, логистика, бухгалтерия..

[AlGol]

Если по условию которое установлено в запросе есть индекс, то сканирование таблицы не должно делаться. Максимум сканирование индекса.

Попробуй для начала сделать проверку/синхронизацию по таблицам в которых наблюдаешь сканирование.
Это вызывается из Администрирование/ Периодические операции/ SQL Администрирование.

Если проверка не даст ошибок, то сделай реиндексацию по таблице (делается там же).

Если после этого все равно все плохо - то надо смотреть что за индексы на таблице, что за запросы долго работают и тд.
методики есть и у mazzy - http://axapta.mazzy.ru/lib/querytuning/

[mazzy]

Цитата:

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

Если по условию которое установлено в запросе есть индекс, то сканирование таблицы не должно делаться.

Может делаться и сканирование.
Если оптимизатор решит, что сканирование дешевле просмотра индекса.
А вот на основании каких данных оптимизатор принимает такое решение - вопрос.

[vesna dba]

Цитата:

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

..то сделай реиндексацию по таблице ..

На тестовой базе (недавняя копия рабочей) после изменений индексов (в части - один из индексов делал кластерным(см.начало)) решил вернуть все в исходное. Вернул, реиндексировал таблицу, и .. - НЕТ TABLE SCAN по этой таблице.

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

[vesna dba]

Цитата:

Сообщение от vesna dba

На тестовой базе (недавняя копия рабочей) после изменений индексов (в части - один из индексов делал кластерным(см.начало)) решил вернуть все в исходное. Вернул, реиндексировал таблицу, и .. - НЕТ TABLE SCAN по этой таблице.

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

Реиндексация базы данных не помогла.
Чето не то творится.. План запроса не изменился (TABLE SCAN остался). А в тестовой базе, с такими же свойствами таблицы (поля, индексы ..) нет сканирования. Размерами правда таблицы отличаются. Рабочая в 1,5 раза больше - >3Gb.
Буду делать свежую копию рабочей базы и повторять изменение индекса на кластерный и потом откат - для повтора\подтверждения эффекта.

[Vadik]

отчет стандартный?
все очень сильно зависит от кода
к примеру, между теми же строками и шапкой накладной связь далеко не по одному полю InvoiceId, и одному InvoiceId может соответствовать несколько накладных
так что (как вариант) - доля вины программистов здесь присутствует (может присутствовать)

[mazzy]

Цитата:

Сообщение от vesna dba

Наблюдаю отсутсвие PrimaryIndex, ClusterIndex
(и во многоих таблицах, по которым встречаю в планах запросов сканирование таблиц такое положение)

PrimaryIndex - это индекс который будет использоваться по умолчанию в формах и отчетах, если индекс не указан явно. Если не указан PrimaryIndex, то формы и отчеты не будут указывать индекс при получении записей.

Насчет ClusterIndex.
Таковая была стратегия разработчиков. Они считали, что кластерные индексы - это зло.
Исходя из этого тезиса, ядро может сделать уникальным любой индекс, добавив поле recid в к индексу.

[vesna dba]

Цитата:

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

Насчет ClusterIndex.
Таковая была стратегия разработчиков. Они считали, что кластерные индексы - это зло.

А где бы почитать про эту стратегию..?

[mazzy]

Цитата:

Сообщение от vesna dba

А где бы почитать про эту стратегию..?

Уже нигде. Теперь у них другая стратегия

[AlGol]

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

Ну, в качестве предположения, можно еще статистику по таблице обновить.

[fedka]

Цитата:

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

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

Ну, в качестве предположения, можно еще статистику по таблице обновить.

Разве индексация не включает в себя и статистику?

[vesna dba]

- Создал копию базы рабочей. Построил план запроса в QA (запроса, который отловил в Аксапте через мониторинг запросов SQL (см.первое сообщ.)). - есть сканирование таблицы.
- Делаю индекс (см.первое сообщ.) кластерным (кстати я был не точен в первом сообщ. - индекс составной по нескольким полям). - нет скана таблицы.
- Возвращаю все назад. - есть скан той же таблицы, план запроса тот же.
---
Чуть позже выложу скриншоты запроса и плана запроса до и после изменения индекса, спиок индексов на таблице и комментарий программиста по поводу отчета, в запросе которого получается скан таблицы.

[vesna dba]

Запрос и его план
SELECT MAX(A.NAMEALIAS),MAX(A.VSN_CUSTTYPE),MAX(A.LINEOFBUSINESSID),A.CUSTACCOUNT,MAX(B
.INVOICINGNAME),B.VSN_BONDCODE,SUM(C.VSN_QTY),SUM(C.LINEAMOUNTMST),SUM(C.TAXAMOU
N
TMST),SUM(C.VSN_QTYPCS),SUM(C.QTY),MAX(D.REFINVENTCLASS),MAX(D.REFINVENTRAITING)
,
MAX(D.REFFUNCTIONALSUBGROUP),MAX(D.REFTRADEMARK),MAX(D.VSN_AGGREGATPACKID),MAX(D
.
VSN_GENERALNAME),MAX(D.NAMEALIAS),MAX(D.ITEMNAME),MAX(D.PACKING_RU),D.ITEMID
FROM SMMBUSRELTABLE A,VSN_CUSTINVOICEJOURCONTR B,CUSTINVOICETRANS C,INVENTTABLE D
WHERE (A.DATAAREAID='VC')
AND (((B.DATAAREAID='DAT')
AND (B.DATAAREAID#2='VC'))
AND ((((((B.INVOICEDATE>={TS '2007-02-01 00:00:00.000'})
AND (B.INVOICEDATE<={TS '2007-02-28 00:00:00.000'}))
AND (B.INVOICETYPE=0))
AND (B.VSN_STORNED=0))
AND
NOT ((B.INVOICEACCOUNT=' КЛ-00235')))
AND (A.CUSTACCOUNT=B.INVOICEACCOUNT)))
AND ((C.DATAAREAID='DAT')
AND ((((B.NUMBERSEQUENCEGROUP=C.NUMBERSEQUENCEGROUP)
AND (B.INVOICEDATE=C.INVOICEDATE))
AND (B.INVOICEID=C.INVOICEID))
AND (B.SALESID=C.SALESID)))
AND ((D.DATAAREAID='VC')
AND (C.ITEMID=D.ITEMID))
GROUP BY A.CUSTACCOUNT,B.VSN_BONDCODE,D.ITEMID
ORDER BY A.CUSTACCOUNT,B.VSN_BONDCODE,D.ITEMID OPTION(FAST 15)
[attachment=605:Plan1_Cu...iceTrans.JPG]

Запрос и его план после изменения индекса INVOICEIDX (сделан кластерным)
[attachment=606:Plan2_Cu...iceTrans.JPG]

Индексы на таблице CUSTINVOICETRANS:
I_064INVOICEIDX
по полям:
- DATAAREAID
- SALESID
- INVOICEDATE
- INVOICEID
- LINENUM
- NUMBERSEQUENCEGROUP
I_064ITEMIDIDX
- DATAAREAID
- ITEMID
- INVOICEDATE
I_064RECID
- DATAAREAID
- RECID
I_064TRANSIDIDX
- DATAAREAID
- INVENTTRANSID
- INVOICEID
- INVOICEDATE
I_064VSN_INVOICEID
- DATAAREAID
- INVOICEID

Индексы кроме I_064VSN_INVOICEID из стандартного функционала.
--
Комментарий программиста относительно отчета, в котором разбираем запрос к базе данных, который вызывает TABLE SCAN:
"Отчет не из стандартного функционала Аксапты.
Создан сотрудниками отдела программирования.
Отчет создан стандартными визуальными средствами разработки с использованием объекта Query.
Запросы формируются на уровне ядра и возможности вмешаться в построение запроса к базе данных у нас нет."
---
Гуру, отзовитесь. Наверняка это касается многих.

[Vadik]

я бы для начала отказался от VIEW VSN_CUSTINVOICEJOURCONTR

[vesna dba]

Цитата:

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

я бы для начала отказался от VIEW VSN_CUSTINVOICEJOURCONTR

Это вьюшник по custinvoicejour и rcontracttable т.е . по шапкам накладных и договорам (Отчет в модуле РК, отчет по продажам).
Отказ невозможен.

[Vadik]

Цитата:

Сообщение от vesna dba

Это вьюшник по custinvoicejour и rcontracttable т.е . по шапкам накладных и договорам (Отчет в модуле РК, отчет по продажам).

это я понял

Цитата:

Отказ невозможен.

попробуйте вместо этого view использовать в запросе две таблицы

[vesna dba]

Цитата:

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

попробуйте вместо этого view использовать в запросе две таблицы

А смысл?
Сканирование таблицы вообще не в той стороне.

[vesna dba]

Цитата:

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

я бы для начала отказался от VIEW VSN_CUSTINVOICEJOURCONTR

А ведь вы попали пальцем в небо, или не попали.. ну короче пища для размышлений была.

Разобрались мы с этим запросом.
Проблема была следующая:
Пользователь, который формировал отчет, в своем наборе прав имел фильтры по номенклатуре. В результате указанный мною запрос изменялся - добавлялись условия выборки по INVENTTABLE и на этом все надолго повисало. У пользователя без таких фильтров отрабатывало за приемлемое время.
Решен вопрос был путем измения сборки вьюшника (INVENTTABLE засунули в него), условия по фильтрам по INVENTTABLE в запрос попадать не стали, изменили немного логику функционала (из за пересборки вьюшника). Теперь отчет формируется нормально.

[Vadik]

Цитата:

Сообщение от vesna dba

А ведь вы попали пальцем в небо

Цитата:

Пользователь, который формировал отчет, в своем наборе прав имел фильтры по номенклатуре. В результате указанный мною запрос изменялся

звиняйте, ну нет в роду телепатов..

[vesna dba]

Народ! Есть результат! Интересный и положительный! Завтра на работе буду - опишу все подробно!