Проблемы с Exists Join

[vanokh]

Цитата:

Сообщение от Владимир Максимов

Выполните в Managment Studio два следующих запроса. Точнее, даже выполнять не обязательно. Просто посмотрите план выполнения...
А потом ответьте, откуда в первом запросе этого плана по SalesLine взялось скалярное значение для SalesId?

Ну наверняка он сращивает вместе два условия

(B.SalesId=[COLOR=blue]right[/COLOR](space(20)+[COLOR=red]'827137'[/COLOR],20)) AND (B.SalesId=A.SALESID)

и выводит по логике, что A.SALESID = Скаляр

Проверил у себя.
Для обоих запросов выполняется Clustered Index Seek, в первом - сначала по SalesLine, потом по SalesTable и Join; во втором наоборот. Если добавить OPTION (FORCE ORDER), то планы запросов станут практически идентичными - как по использованию таблиц, так и по времени выполнения.



С курсорами планы такие же, но с одним существенным отличием - поверх добавляется вставка в Tempdb, которая занимает столько же времени, сколько cам запрос, в результате время выполнения увеличивается чуть больше чем в два (!) раза. Хотя общее время выполнения все равно осталось 0,02 с

OPTION (FORCE ORDER) для курсоров не сработал



Поскольку WMSBILLOFLADINGORDER в нашей базе пустая, то провел эксперимент для связки SalesTable (около 100 тыс в одной компании) и VendTable (3500 записей). Можете привести свои планы по запросу с WMSBILLOFLADINGORDER для сравнения?

Первый запрос -

SELECT A.CUSTACCOUNT,A.INVOICEACCOUNT,A.SALESID,A.RECID 
    FROM SALESTABLE A WHERE ((A.DATAAREAID='dvc') AND (A.SALESTYPE=3)) 
    AND EXISTS (SELECT 'x' FROM VENDTABLE B 
            WHERE ((B.DATAAREAID='com') AND ((B.ACCOUNTNUM='СПЦ') 
            AND (B.ACCOUNTNUM = A.CONSIGNORACCOUNT_RU))))

SELECT A.CUSTACCOUNT,A.INVOICEACCOUNT,A.SALESID,A.RECID 
FROM SALESTABLE A, VENDTABLE B
WHERE ((A.DATAAREAID='dvc') AND (A.SALESTYPE=3)) 
     AND ((B.DATAAREAID='com') AND ((B.ACCOUNTNUM='СПЦ') 
    AND (B.ACCOUNTNUM = A.CONSIGNORACCOUNT_RU)))

План запроса:

Estimated Subtree Cost: 19 и 16 соответственно

Он же + FORCE ORDER:

Estimated Subtree Cost: оба 19

Теперь с курсорами:

Результаты одинаковы, что без. что с FORCE ORDER: 22,6 и 18,4

Выводы:

1. для простых запросов SQL строит планы хорошо и вмешательство FORCE ORDER только ухудшает дело
2. в курсорах нельзя поменять порядок FORCE ORDER (у меня во всяком случае неполучилось, может кто из грамотных спецов по SQL подскажет почему?)
3. в курсорах запрос ко внутренней таблице стал выполняться дольше (0% - 19%, хотя данные вроде те же самые) - порядок поменять нельзя см п.2. видимо дело в логике работы Left Semi Join
4. В курсорах добавляется вставка во временную таблицу, что тоже дает небольшое замедление (FETCH возвращает одну строку)

[Владимир Максимов]

Планы выполнения запросов

SELECT A.ITEMID,A.SALESQTY,A.LINEAMOUNT,A.SALESID,A.RECID 
FROM SALESLINE A 
WHERE ((A.DATAAREAID='цтр') 
	AND (A.SALESQTY<>0)) 
	AND EXISTS (SELECT 'x' FROM WMSBILLOFLADINGORDER B 
		WHERE ((B.DATAAREAID='цтр') 
			AND ((B.INVENTTRANSREFID=A.SALESID) 
			AND (B.BILLOFLADINGID='101'))))

SELECT A.ITEMID,A.SALESQTY,A.LINEAMOUNT,A.SALESID,A.RECID 
FROM SALESLINE A,WMSBILLOFLADINGORDER B 
WHERE ((A.DATAAREAID='цтр') 
	AND (A.SALESQTY<>0)) 
	AND ((B.DATAAREAID='цтр') 
	AND ((B.INVENTTRANSREFID=A.SALESID) 
	AND (B.BILLOFLADINGID='101')))

То же самое, но "обернутое" в курсоры



Лично мне кажется, что использования хинтов - это порочная практика. Как правило, даже если удается оптимизировать запрос, то с течением времени, с изменением объема таблицы и статистики ее использования, хинты начинают не ускорять, а замедлять работу запроса.

Поэтому, лучше оставить построение плана "на усмотрение" автоматического построителя запросов, чтобы потом не вычищать собственноручно сделанные модификации...

Вставка во временную таблицу при работе курсоров - это настолько незначительная задержка, что не стоит обращать на нее внимание. Посмотри прилагаемые планы исполнения. При работе через Exists вставка в курсор стоит 0%, а при раьоте через Inner - 35%. Тем не менее, общая стоимость запроса с Exists составляет 100% по сравнению с 0% стоиомости запроса с Inner.

[Wamr]

Господа, что-то мы топчемся на месте. Есть ещё какие-нибудь идеи куда копать в поисках такого странного поведения оптимизатора?

[vanokh]

Цитата:

Сообщение от Владимир Максимов

Лично мне кажется, что использования хинтов - это порочная практика...

Насчет хинтов согласен - порочная, я использовал хинт, только чтобы проверить гипотезу о перемене мест "слагаемых"

Цитата:

Сообщение от Владимир Максимов

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

Да, временные таблицы в данном случае несущественная задержка.


А теперь по существу Мне удалось воспроизвести ваш случай с использованием других таблиц - SalesTable (100 тыс.) и RContractTable (10 тыс.). Также как и у вас - просто запросы выполняются быстро, в курсорах exists жутко тормозит (стоимость запроса 99%). И мне кажется я нашел причину

Чем отличаются курсоры от простых запросов? Тем, что запросы возвращают сразу все записи, а в курсорах записи выбираются по одной путем FETCH. Логично предположить, что оптимизатор строит план запроса для курсора с учетом этой особенности - быстрая выборка одной записи.

Гипотеза подтвердилась - если курсор сделать STATIC (с хранением всех выбранных результатов в tempdb) или FAST_FORWARD (с оптимизацией), то план построится более оптимальный и тормозить не будет (затраты 50/50). Попробуйте у себя.

Остается вопрос - использует ли Ax такие курсоры?...

Дополнение - курсор по умолчанию создается с возможностью редактирования, если поставить READ_ONLY, тоже будет быстро (все результаты так же выбираются сразу и хранятся в tempdb)

[Wamr]

Владимир Максимов,
скажите, пожалуйста, какое значение свойства Parameterization в ваше БД?

[Владимир Максимов]

Цитата:

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

Владимир Максимов,
скажите, пожалуйста, какое значение свойства Parameterization в ваше БД?

Simple

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

[Wamr]

Владимир, а я и не предлагал играться хинтами. Поведение ваших запросов нехарактерно - я пытаюсь понять в чем причина.

Я подозреваю, что при исполнении запроса с Exist у вас не происходит перестроение плана (компиляция запроса), а берется закешированный вариант, который был построен для других параметров, например, в другой компании.
Но все это возможно, только если запрос был параметризован (используется процедура sp_cursorprepare ), либо используется внутренняя параметризация сервера (параметр Parameterization БД).

[Владимир Максимов]

Почему же. Очень даже характерно. Ведь vanokh удалось повторить описанное поведение.

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

У нас ведь, транспортные накладные раньше не использовали и начали использовать только недавно. Вот и нет еще достаточной статистики. Вот и мало записей транспортных накладных по сравнению с заказами. Думаю, с течением времени, запрос по EXISTS с транспортными накладными изменит свой план выполнения и станет работать быстрее. Вот тогда его можно будет переписать на Exists.

[vanokh]

Цитата:

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

Я подозреваю, что при исполнении запроса с Exist у вас не происходит перестроение плана (компиляция запроса), а берется закешированный вариант...

Для того, чтобы проверить закешированный вариант или нет, можно добавить OPTION(RECOMPILE) - план каждый раз будет перестраиваться. Я для всех запросов проверил - планы не отличаются.

Parametrization установлен в Simple.