Ошибки оптимизатора

Oblom
Дата: 02.02.2009 15:47:19
Доброе время суток!
Выполняется запрос следующего вида:
select t1.* from t1
inner join t2 on t1.ID=t2.t1ID
inner join t3 on t3.ID=t2.t3ID
inner join t4 on t4.ID=t3.t4ID
where t1.a>0 and t2.b=0
короче запрос с внутренними соединениями.
Таблицы по размеру достаточно приличные(каждая > 10 тысяч записей).
Выполняется долго, смотрим запрос - во всех inner join оптимизатор использует соединение Nested Loops.
Пишем во всех соединениях inner hash join - всё летает.
И это повторяется раз за разом, практичски все запросы идут с хинтами hash.
Почему оптимизатор может так лажать?

select @@version:
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Паганель
Дата: 02.02.2009 16:00:36
Oblom
Почему оптимизатор может так лажать?
Оговорюсь сразу, по части оптимизации я не спец,
но возник ряд замечаний по самому вопросу:

1) Насколько селективны условия t1.a>0 и t2.b=0
2) Вы ничего не сказали о присутствующих в Вашей БД индексах
3) Вы не привели планы выполнения
4) Вы ничего не сказали об актуальности статистики
Oblom
Дата: 02.02.2009 16:09:20
1) Насколько селективны условия t1.a>0 и t2.b=0
Они выбирают большую часть таблиц. то есть отсекают не так много записей

2) Вы ничего не сказали о присутствующих в Вашей БД индексах
Индексов кроме первичных и вторичных ключей нет

3) Вы не привели планы выполнения
К сожалению, не представляю как их можно из студии вытащить в текстовом виде и прилепить здесь к сообщению

4) Вы ничего не сказали об актуальности статистики
База новая, находится в стадии разработки, поэтому говорить об актуальности статистики пока не приходится
Glory
Дата: 02.02.2009 16:10:47
Oblom


3) Вы не привели планы выполнения
К сожалению, не представляю как их можно из студии вытащить в текстовом виде и прилепить здесь к сообщению

А в ссылке интересно про рассказано, как не про вытаскивание плана в текстовом виде ?
Паганель
Дата: 02.02.2009 16:11:20
Зачем выбирать все 10 тысяч записей?
Что Вы будете делать с таким огромным результатом?
Oblom
Дата: 02.02.2009 16:15:57
Паганель
Зачем выбирать все 10 тысяч записей?
Что Вы будете делать с таким огромным результатом?

например нумеровать и выводить постранично
Glory
Дата: 02.02.2009 16:16:48
Oblom
Паганель
Зачем выбирать все 10 тысяч записей?
Что Вы будете делать с таким огромным результатом?

например нумеровать и выводить постранично

Вы на клиенте собрались "нумеровать и выводить постранично" ?
Oblom
Дата: 02.02.2009 16:30:04
Glory

Вы на клиенте собрались "нумеровать и выводить постранично" ?

WITH CTE ([RowNumber], [1], [2], [3])
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY [4]),  [1], [2], [3]
FROM t1
)

SELECT [1], [2], [3] FROM CTE
WHERE RowNumber BETWEEN @StartPageElement AND @FinishPageElement

к примеру вот так.

И если вы скажете, что это долго и неправильно, то я скажу, что та секунда за которую обрабатывается 100.000 записей в таблице t1 меня вполне устраивает.

Как я понимаю, ваша магистральная мысль - что не надо выводить(обрабатывать) такое кол-во записей.
Но у меня к примеру стоит задача по 100.000 разновидностей товара посчитать наименее продаваемые. И я не представляю как это сделать не плюсуя таблицу продаж такой же величины.
Glory
Дата: 02.02.2009 16:31:41
Oblom
Glory

Вы на клиенте собрались "нумеровать и выводить постранично" ?

WITH CTE ([RowNumber], [1], [2], [3])
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY [4]),  [1], [2], [3]
FROM t1
)

SELECT [1], [2], [3] FROM CTE
WHERE RowNumber BETWEEN @StartPageElement AND @FinishPageElement

к примеру вот так.

И если вы скажете, что это долго и неправильно, то я скажу, что та секунда за которую обрабатывается 100.000 записей в таблице t1 меня вполне устраивает.

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

Так и не понятно, зачем на клиента тащить все 100000 записей, если ему сейчас из них нужна только одна "страница"
Anddros
Дата: 02.02.2009 16:36:12
Oblom
1) Насколько селективны условия t1.a>0 и t2.b=0
Они выбирают большую часть таблиц. то есть отсекают не так много записей

4) Вы ничего не сказали об актуальности статистики
База новая, находится в стадии разработки, поэтому говорить об актуальности статистики пока не приходится


При отсутствии статистики оптимизатор считает, что под условие t2.b=0 будет попадать 10-15% записей, а под условие t1.a>0 - 30-50%. Точные цифры процентов, по-моему, зависят от типов полей. Но не уверен - это так, просто мои наблюдения, а не подтвержденная чем-либо информация.

Из-за этого он вполне может ошибаться при выборе оптимального плана.