Оптимизация запросов

[psv]

Я не стал бы делать столь однозначные выводы на основе одного примера неграмотного использования хинтов.К сожалению, оптимизатор имеет свои ограничения и не всегда выдает оптимальный результат, именно поэтому и были введены хинты. Пользоваться ими нужно в крайнем случае, но иногда это дает значительный выигрыш.

[Vadik]

Цитата:

Изначально опубликовано psv
Я не стал бы делать столь однозначные выводы на основе одного примера неграмотного использования хинтов.

Перечитал еще раз - однозначных выводов не вижу, вижу информацию для размышления. Насчет одного примера - могу подкинуть еще, их есть у меня, не жалко

Цитата:

К сожалению, оптимизатор имеет свои ограничения и не всегда выдает оптимальный результат, именно поэтому и были введены хинты.

не спорю, но как правило, выдает гораздо чаще, чем не выдает

Цитата:

Пользоваться ими нужно в крайнем случае, но иногда это дает значительный выигрыш.

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

для затравки:

MSSQL SP3a, аксапта 3.0 SP3CIS

игрушечная БД

Заказы \ Функции \ Создание строк

имею
InventTable - порядка 10000 строк, InventSum - 120000 строк, InventDim - 100000 строк

смотрим executeQuery на InventTable в SalesQuickQuote

PHP код:

    if (inventTable_ds.queryRun())
        query = inventTable_ds.queryRun().query();
    else
        query = inventTable_ds.query();

    query.forceSelectOrder(true);
    query.datasourceTable(tableNum(inventDim)).addSortIndex(IndexNum(InventDim, DimIdIdx));
    query.datasourceTable(tableNum(inventDim)).indexIsHint(true);
    query.datasourceTable(tableNum(inventSum)).addSortIndex(IndexNum(InventSum, ItemDimIdx));
    query.datasourceTable(tableNum(inventSum)).indexIsHint(true); 


ловим запрос и мучаем его в QA:

вот это - оригинальный (весьма) запрос:

SELECT A.ITEMID,SUM(B.AVAILPHYSICAL),MIN(B.INVENTDIMID),B.ITEMID,C.INVENTLOCATIONID
FROM INVENTTABLE A(NOLOCK) ,INVENTSUM B(INDEX(I_174ITEMDIMIDX) NOLOCK) ,INVENTDIM C(INDEX(I_698DIMIDIDX) NOLOCK)
WHERE (A.DATAAREAID='bmd') AND ((B.DATAAREAID='bmd') AND (A.ITEMID=B.ITEMID)) AND ((C.DATAAREAID='bmd') AND (B.INVENTDIMID=C.INVENTDIMID))
GROUP BY A.ITEMID,B.ITEMID,C.INVENTLOCATIONID
ORDER BY A.ITEMID,B.ITEMID,C.INVENTLOCATIONID
OPTION(FAST 1,FORCE ORDER)

(9717 row(s) affected)

Table 'INVENTTABLE'. Scan count 2, logical reads 838, physical reads 33, read-ahead reads 821.
Table 'INVENTSUM'. Scan count 2, logical reads 112409, physical reads 1011, read-ahead reads 4579.
Table 'INVENTDIM'. Scan count 2, logical reads 102426, physical reads 876, read-ahead reads 745.

SQL Server Execution Times:
CPU time = 17891 ms, elapsed time = 28548 ms.

убираем хинты

SELECT A.ITEMID,SUM(B.AVAILPHYSICAL),MIN(B.INVENTDIMID),B.ITEMID,C.INVENTLOCATIONID
FROM INVENTTABLE A(NOLOCK) ,INVENTSUM B(NOLOCK) ,INVENTDIM C(NOLOCK)
WHERE (A.DATAAREAID='bmd') AND ((B.DATAAREAID='bmd') AND (A.ITEMID=B.ITEMID)) AND ((C.DATAAREAID='bmd') AND (B.INVENTDIMID=C.INVENTDIMID))
GROUP BY A.ITEMID,B.ITEMID,C.INVENTLOCATIONID
ORDER BY A.ITEMID,B.ITEMID,C.INVENTLOCATIONID

(9717 row(s) affected)

Table 'INVENTDIM'. Scan count 2, logical reads 1055, physical reads 0, read-ahead reads 1055.
Table 'INVENTTABLE'. Scan count 2, logical reads 40, physical reads 2, read-ahead reads 39.
Table 'INVENTSUM'. Scan count 2, logical reads 4651, physical reads 0, read-ahead reads 4658.

SQL Server Execution Times:
CPU time = 3891 ms, elapsed time = 8697 ms.

для чистоты эксперимента, ясное дело, кэш перед запуском обоих запросов чистится

сравниваем execution times, logical reads, physical reads, начинаем плеваться в монитор

примеров таких - вагон и маленькая тележка

[psv]

Еще один пример бездумного использования хинтов.

1.Применяем хинты и изменяем порядок joinoв

SQL Server Execution Times:
CPU time = 50 ms, elapsed time = 68 ms.

SELECT A.ITEMID,SUM(B.AVAILPHYSICAL),MIN(B.INVENTDIMID),B.ITEMID,C.INVENTLOCATIONID
FROM
[bmssa].INVENTDIM C(INDEX(I_698DIMIDIDX)NOLOCK),
[bmssa].INVENTSUM B (NOLOCK),
[bmssa].INVENTTABLE A(NOLOCK)

WHERE (A.DATAAREAID='dat') AND ((B.DATAAREAID='dat') AND
(A.ITEMID=B.ITEMID)) AND
((C.DATAAREAID='dat') AND (B.INVENTDIMID=C.INVENTDIMID))
GROUP BY A.ITEMID,B.ITEMID,C.INVENTLOCATIONID
ORDER BY A.ITEMID,B.ITEMID,C.INVENTLOCATIONID
OPTION(--FAST 1,
FORCE ORDER)

Table 'INVENTTABLE'. Scan count 50, logical reads 103, physical reads 4, read-ahead reads 0.
Table 'INVENTSUM'. Scan count 8, logical reads 58, physical reads 4, read-ahead reads 0.
Table 'INVENTDIM'. Scan count 1, logical reads 9, physical reads 2, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 10 ms, elapsed time = 92 ms.

------------------------------------------------------------------------------------------------
2. Без хинтов
SQL Server Execution Times:
CPU time = 50 ms, elapsed time = 62 ms.

SELECT A.ITEMID,SUM(B.AVAILPHYSICAL),MIN(B.INVENTDIMID),B.ITEMID,C.INVENTLOCATIONID
FROM [bmssa].INVENTTABLE A(NOLOCK) ,[bmssa].INVENTSUM B(NOLOCK) ,[bmssa].INVENTDIM C(NOLOCK)
WHERE (A.DATAAREAID='dat') AND ((B.DATAAREAID='dat') AND (A.ITEMID=B.ITEMID)) AND
((C.DATAAREAID='dat') AND (B.INVENTDIMID=C.INVENTDIMID))
GROUP BY A.ITEMID,B.ITEMID,C.INVENTLOCATIONID
ORDER BY A.ITEMID,B.ITEMID,C.INVENTLOCATIONID


(50 row(s) affected)

Table 'INVENTTABLE'. Scan count 50, logical reads 103, physical reads 4, read-ahead reads 0.
Table 'INVENTSUM'. Scan count 8, logical reads 58, physical reads 4, read-ahead reads 0.
Table 'INVENTDIM'. Scan count 1, logical reads 9, physical reads 2, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 30 ms, elapsed time = 92 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

Те кол-во чтений тоже, но время выполнения уменьшилось.К сожалению,у меня тестовая БД с меньшим объемом поэтому результаты могут отличаться.

Цитата:

не спорю, но как правило, выдает гораздо чаще, чем не выдает

Именно для этого и нужны хинты, а особенно, когда приходиться бороться с чужой БД, у которой нет исходников клиента. Вылизывание иногда позволяет сократить время выполнения с 30-40 с до 3-7(было у меня и такое).
Возможно, я не совсем правильно понял автора, если призывается вдумчиво использовать подсказки, которые на разных объемах работаю по-разному, то я полностью согласен.На мой взгляд, это последнее средство для оптимизации, прежде всего: правильная структура БД, индексов(для этого запроса напрашиваются кластерные индексы, которых нет, что в общем - моветон) и тд.

[mazzy]

Цитата:

Изначально опубликовано psv
для этого запроса напрашиваются кластерные индексы, которых нет

Что здесь имелось в виду?


Поскольку Вадим сейчас в полях, отвечу я.
Статья была призвана не перечислить все способы оптимизации.
Статья была призвана показать, что очень часто небольшими изменениями можно увеличить производительность в разы.

В большинстве случаев в Аксапте это так.
Нужно этим просто заниматься.

[psv]

Кластерный индекс позволил бы убрать лишний Bookmark Lookup для InventDim в плане выполнения запроса. Отсутсвие кластерных индексов, при детальном рассмотрении, полностью обосновано, тк размеры небольшие и(или) данные в них не удаляются.

Статья полезная и интересная, но на мой взгляд, предложение

Цитата:

Но вариант отказа от них может быть полезен при оптимизации самых «тяжелых» запросов

следует заменить, поскольку для самых тяжелых запросов и нужна граммотная оптимизация.

Возникает вопрос.
Чем вызвано примение необоснованных хинтов в Axapte?

[Vadik]

А нас модератор "Новостей партнеров MBS и рынка ERP-систем" за оффтопик не побьет?

Предлагаю на выбор:

- создать новую тему в соответствующем форуме
- перенести в соответствующий форум часть этой ветки (у меня таких прав нет, насколько я понимаю)
- продолжить ветку здесь - http://forum.mazzy.ru/index.php?showtopic=1023

[mazzy]

эта ветка была выделена из обяъвления
Услуги по повышению производительности работы Axapta и Navision

здесь обсуждается советы Вадима Гончаренко
http://axapta.mazzy.ru/lib/indexhints/
http://axapta.mazzy.ru/lib/querytuning/

Извините, что не смог сделать это сообщение первым в ветке.
Предлагаю обсуждение технических вопросов перенести сюда.