Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 ГЛЮЧНЫЙ ОПТИМИЗАТОР!!!  [new]
frantic
Member

Откуда: 45°03′ с. ш. 41°59′ в. д.
Сообщений: 155
Попытка: уйти от динамического SQL в хранимой процедуре.
Результат: ГЛЮЧНЫЙ ОПТИМИЗАТОР

1. выбираем 1 запись по id (индекс по полю построен):
declare @x int
set @x = 5
select * from myt where @x = 0 or myt.id = @x
План: table scan

2. переносим сравнение с константой вправо:
declare @x = 0 int
@x = 5
select * from myt where myt.id = @x or @x = 0
План: index seek

Причем во втором случае при @x = 0 (выведутся все записи) оптимизатор будет использовать индекс (индекс некластерный), что медленнее table scan.

Неужели и вправду MSSQL г...???
26 дек 03, 09:45    [474965]     Ответить | Цитировать Сообщить модератору
 Re: ГЛЮЧНЫЙ ОПТИМИЗАТОР!!!  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
А при чем здесь г... ?

Отнеси это на особенности работы оптимизатора.
26 дек 03, 09:52    [474982]     Ответить | Цитировать Сообщить модератору
 Re: ГЛЮЧНЫЙ ОПТИМИЗАТОР!!!  [new]
frantic
Member

Откуда: 45°03′ с. ш. 41°59′ в. д.
Сообщений: 155
2tpg
А при том, что не очень то хочется выполнять работу за него ;D
26 дек 03, 09:58    [474990]     Ответить | Цитировать Сообщить модератору
 Re: ГЛЮЧНЫЙ ОПТИМИЗАТОР!!!  [new]
Предводитель команчей
Member

Откуда: Днепропетровск
Сообщений: 321
Да не надо полагаться на оптимизатор. Так, принимайте к сведению план запроса. Можно ведь повлиять на план выполнения, когда оптимизатор "глючит". А качество кода и производительность запроса в ваших руках. Если руки кривые (ничего личного) то никакой оптимизатор не поможет.
26 дек 03, 10:02    [474999]     Ответить | Цитировать Сообщить модератору
 Re: ГЛЮЧНЫЙ ОПТИМИЗАТОР!!!  [new]
frantic
Member

Откуда: 45°03′ с. ш. 41°59′ в. д.
Сообщений: 155
Да! Выяснилось, что если этот statement поместить в ХП или UDF, то оптимизатор будет использовать индекс в обоих случаях.Ну это уже ваааще ;).
Может кто-нить из уважаемых гуру все таки объяснит поведение оптимизатора в этой достаточно простой ситуации?
26 дек 03, 10:32    [475051]     Ответить | Цитировать Сообщить модератору
 Re: ГЛЮЧНЫЙ ОПТИМИЗАТОР!!!  [new]
Николай --
Member

Откуда:
Сообщений: 48
1. А почему бы IF не использовать?
2. Обычно надо выражения располагать в порядке знаимости для поиска. То есть, сначала (@x=0) - столбцов таблицы нет, используется table scan, так как какой @x - неизвестно. Если наоборот - то поиск по индексу. Так всегда было. И в RULE based оптимизаторе ORACLE так же дела обстоят.
3. В хранимой процедуре - там предполагается, что все такие выражения @x=0 ложны и для построения плана используются остальные выражения -вот и получается поиск по индексу.
==========
У Вас ДОЛЖНО быть два запроса. Используйте IF!!!
26 дек 03, 11:01    [475118]     Ответить | Цитировать Сообщить модератору
 Re: ГЛЮЧНЫЙ ОПТИМИЗАТОР!!!  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Посмотри в мои два топика на этой странице.
Все объяснится.
Это глюк.
Причем от 7.0 последнего СП - точно.
26 дек 03, 12:34    [475327]     Ответить | Цитировать Сообщить модератору
 Re: ГЛЮЧНЫЙ ОПТИМИЗАТОР!!!  [new]
Hibernate
Member

Откуда: Киев
Сообщений: 1670
Да! Выяснилось, что если этот statement поместить в ХП или UDF, то оптимизатор будет использовать индекс в обоих случаях.Ну это уже ваааще ;).
странно - у меня все было наоборот - из QA используется индекс, а вот из хп -скан таблицы....
дело в том, что оптимизатор не знает значения переменной и поэтому ошибается - он не знает наперед какой план выполнения выбрать при компиляции - ведь в результате может получиться как выбока всей таблицы (table scan выгоднее) так и всего несоклько записей - тогда конечно индекс выгоднее. Вот вся проблема в том и состоит, что наперед выбрать план нереально.

Может кто-нить из уважаемых гуру все таки объяснит поведение оптимизатора в этой достаточно простой ситуации?

переменная в запросе в случае предварительной компиляции этого запроса - это уже непростая ситуация.
26 дек 03, 12:56    [475391]     Ответить | Цитировать Сообщить модератору
 Re: ГЛЮЧНЫЙ ОПТИМИЗАТОР!!!  [new]
Crimean
Member

Откуда:
Сообщений: 13148
> переменная в запросе в случае предварительной компиляции этого
> запроса - это уже непростая ситуация.

Гм...
Запрос вида

declare @x int
set @x = 1
select id from bills (nolock) where id = @x or @x = 0

пишет вполне вменяемый план - индекс сик по Ид (это ПК, вобще-то), но какого по ПК по Ид он 2 секунды думает, применяя фильтр "id = @x or @x = 0"

Переписываем

declare @x int
set @x = 1
select * from (select id from bills (nolock) where id = @x) as a where @x = 0

и все летает! Но - и план другой!
26 дек 03, 14:14    [475570]     Ответить | Цитировать Сообщить модератору
 Re: ГЛЮЧНЫЙ ОПТИМИЗАТОР!!!  [new]
Николай --
Member

Откуда:
Сообщений: 48
Crimean

Переписываем
declare @x int
set @x = 1
select * from (select id from bills (nolock) where id = @x) as a where @x = 0
и все летает! Но - и план другой!

И условие другое.
Вместо OR получилось AND.
26 дек 03, 14:32    [475618]     Ответить | Цитировать Сообщить модератору
 Re: ГЛЮЧНЫЙ ОПТИМИЗАТОР!!!  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Хорошо, сделаем план таким же!

declare @i int

select @i = 1

select * from bills where @i <> 0 and @i = id
union all
select * from bills where @i = 0


Или - нет?
26 дек 03, 14:51    [475677]     Ответить | Цитировать Сообщить модератору
 Re: ГЛЮЧНЫЙ ОПТИМИЗАТОР!!!  [new]
Николай --
Member

Откуда:
Сообщений: 48
Crimean


select * from bills where @i <> 0 and @i = id

union all

select * from bills where @i = 0

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

if @i=0
select * from bills
else
select * from bills where id=@i

Тут у оптимизатора гораздо меньше свободы выбора.
26 дек 03, 14:58    [475693]     Ответить | Цитировать Сообщить модератору
 Re: ГЛЮЧНЫЙ ОПТИМИЗАТОР!!!  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Так писать-то, конечно, лучше, но если параметров, скажем, не один, а 4-5, то и комбинаций будет достаточно много:

declare @a1 int , @a2 int

select @a1 = 0, @a2 = 0

if @a1 = 0 and @a2 = 0 select * from sysobjects
else if @a1 = 0 and @a2 <> 0 select * from sysobjects where parent_obj = @a2
else if @a1 <> 0 and @a2 = 0 select * from sysobjects where id = @a1
else if @a1 <> 0 and @a2 <> 0 select * from sysobjects where id = @a1 and parent_obj = @a2

Это здорово, но неужели это не может сделать оптимизатор?
Бо для 4 параметров комбинаций будет уже слишком. А через OR все красиво.
26 дек 03, 15:12    [475719]     Ответить | Цитировать Сообщить модератору
 Re: ГЛЮЧНЫЙ ОПТИМИЗАТОР!!!  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
А через OR все красиво.

Для кого? Для девелопера - да. Для оптимизатора, по всей видимости - нет.
Я, всё-таки, считаю, что надо как то учитывать особенности инструментов разработки, а не упавать на всесильность оптимизатора, ИМХО.
26 дек 03, 15:17    [475728]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить