Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 отключение индекса  [new]
KVic
Guest
Есть ли возможность отключить определенный индекс при выполнении
одного запроса.
29 мар 04, 17:08    [602457]     Ответить | Цитировать Сообщить модератору
 Re: отключение индекса  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
Обычно, наоборот боряться за использование индекса в запросе, а у вас что за ситуация? Можно по-подробней?
29 мар 04, 17:09    [602465]     Ответить | Цитировать Сообщить модератору
 Re: отключение индекса  [new]
KVic
Guest
Программа работае с interbase и MSSql.
Пишутся универсальные запросы и специальный транслятор адаптирует
их под конкретную DB.
Существует специальный комментарий для отключения индекса по полю.
Например для interbase результирующий код будет таким

SELECT * FROM CDO_Rx T_CDO_Rx
WHERE 0 +
T_CDO_Rx.CID IN (100030, 100035, 100031, 100033, 100039, 100034, 100036, 100038) AND
(T_CDO_Rx.UpperDescription LIKE 'TE%') and
0 + T_CDO_Rx.RxSystem = 2 and
0 + T_CDO_Rx.Created <= '03/29/2004 14:03:29' and
0 + T_CDO_Rx.DELETED > '03/29/2004 14:03:29'

Хотелось бы изменяя сильно тратслятор получить подобный вариант для MSSQL
29 мар 04, 17:32    [602537]     Ответить | Цитировать Сообщить модератору
 Re: отключение индекса  [new]
Glory
Member

Откуда:
Сообщений: 104760
Оптимизатор MSSQL сам решает какие индексы из возможных лучше всего использовать(или не использовать!) для выполнения запроса.
Но можно указать принудительное сканирование таблицы с помощью WITH(INDEX=0). Только это может привести к потереи производительности.
29 мар 04, 17:36    [602551]     Ответить | Цитировать Сообщить модератору
 Re: отключение индекса  [new]
KVic
Guest
Sorry,
Последняя фраза
Хотелось бы не изменяя сильно транслятор получить подобный результат для MSSQL
29 мар 04, 17:36    [602553]     Ответить | Цитировать Сообщить модератору
 Re: отключение индекса  [new]
Glory
Member

Откуда:
Сообщений: 104760
Хотелось бы не изменяя сильно транслятор получить подобный результат для MSSQL

Синтаксическую проверку в QA предложенный вами запрос проходит. Скорее всего и выполняться будет (могут возникнуть решаемые проблемы с конвертацией символьных констант к типу datetime).

И где кстати в данном запросе "специальный комментарий для отключения индекса по полю" ?
29 мар 04, 17:39    [602558]     Ответить | Цитировать Сообщить модератору
 Re: отключение индекса  [new]
KVic
Guest
Я не знаю, может Microsoft программеры считают себя слишком умными,
но явное включение индекса (index(...)) приводит к желаемому результату.
время выборки увеличивается на порядок.
Меня смущает, что estimate rows в плане сильно отличается от реальной выборки. Есть ли еще возможность как-то повлиять на оптимизатор.
29 мар 04, 17:42    [602564]     Ответить | Цитировать Сообщить модератору
 Re: отключение индекса  [new]
KVic
Guest
Универсальный запрос выглядел так

SELECT * FROM CDO_Rx T_CDO_Rx
WHERE /*indexoff*/
T_CDO_Rx.CID IN (100030, 100035, 100031, 100033, 100039, 100034, 100036, 100038) AND
(T_CDO_Rx.UpperDescription LIKE 'TE%') and
/*indexoff*/ T_CDO_Rx.RxSystem = 2 and
/*indexoff*/ T_CDO_Rx.Created <= '03/29/2004 14:03:29' and
/*indexoff*/ T_CDO_Rx.DELETED > '03/29/2004 14:03:29'
29 мар 04, 17:45    [602571]     Ответить | Цитировать Сообщить модератору
 Re: отключение индекса  [new]
KVic
Guest
С датами проблем не будет.
На сомом деле они передаются как параметры.
Кстати, почему QA не понимает параметров в запросе?
Может есть какой-либо сторонний продукт (более функциональный)?
29 мар 04, 17:49    [602580]     Ответить | Цитировать Сообщить модератору
 Re: отключение индекса  [new]
Glory
Member

Откуда:
Сообщений: 104760
С датами проблем не будет.
На сомом деле они передаются как параметры.

Конвертация символьной строки в дату заивсит не от способа передачи этой строки, а от установок конекта/логина.


Кстати, почему QA не понимает параметров в запросе?
Локальные переменные понимает. А спрашивать их так же как делает скажем построител запросов в Access - не будет. Ибо в TSQL нет команд для интерактивного общения сервера с ползователем

время выборки увеличивается на порядок.
Ну атк вы же сами запретили серверу использовать индекс ?!

Меня смущает, что estimate rows в плане сильно отличается от реальной выборки.
estimate вообще-то и переводится как приблизительный/ориентировочный

Есть ли еще возможность как-то повлиять на оптимизатор.
Есть. Например использовать вместо IN соединение по INNER JOIN
29 мар 04, 17:58    [602604]     Ответить | Цитировать Сообщить модератору
 Re: отключение индекса  [new]
Gena G.
Member

Откуда: Oz
Сообщений: 977
автор
Кстати, почему QA не понимает параметров в запросе?


А почему он должен их понимать?
29 мар 04, 18:10    [602640]     Ответить | Цитировать Сообщить модератору
 Re: отключение индекса  [new]
KVic
Guest
Конвертация символьной строки в дату заивсит не от способа передачи этой строки, а от установок конекта/логина.
Реальная программа использует параметрые и соответственно драйвер все конвертит нормально. Кроме того я не говорил, что запрос не работает.

Локальные переменные понимает. А спрашивать их так же как делает скажем построител запросов в Access - не будет. Ибо в TSQL нет команд для интерактивного общения сервера с ползователем
Существуют продукты, которые предоставляют подобный сервис (см. IBExpert)

Ну атк вы же сами запретили серверу использовать индекс ?!
Конструкции 0+ не работают для MSSQL, поэтому все индексы включены.

Например использовать вместо IN соединение по INNER JOIN
Если вы обратили внимание, выборка идет по одной таблице. Поле CID просто
идентификатор класса.
29 мар 04, 18:17    [602657]     Ответить | Цитировать Сообщить модератору
 Re: отключение индекса  [new]
Glory
Member

Откуда:
Сообщений: 104760
Реальная программа использует параметрые и соответственно драйвер все конвертит нормально. Кроме того я не говорил, что запрос не работает.
По-моему мы про разные вещи
set dateformat mdy

go
select cast('03/29/2004 14:03:29' as datetime)
go
set dateformat dmy
go
select cast('03/29/2004 14:03:29' as datetime)


Конструкции 0+ не работают для MSSQL, поэтому все индексы включены.

Да неужели ? Ну если только складывать с символьными строками

Если вы обратили внимание, выборка идет по одной таблице. Поле CID просто
идентификатор класса.

И что из этого следует ? Что нельзя заменить IN на INNER JOIN с derived table ?

Существуют продукты, которые предоставляют подобный сервис (см. IBExpert)
Это логика конкретной утилиты.
Если вы в QA в спсике объектов выберите процедуры и в меню на правой кнопки мыши пункт Open, QA также "спросит" у вас значения для входных параметров процедуры
29 мар 04, 18:28    [602681]     Ответить | Цитировать Сообщить модератору
 Re: отключение индекса  [new]
злой шаман
Member

Откуда: Питер
Сообщений: 1253
Вот так, например, можно индексы вырубить. Точнее сделать их непригодными для оптимизатора.

SELECT * FROM CDO_Rx T_CDO_Rx 

WHERE isnull(
T_CDO_Rx.CID,null) IN (100030, 100035, 100031, 100033, 100039, 100034, 100036, 100038) AND
(isnull(T_CDO_Rx.UpperDescription,null) LIKE 'TE%') and
isnull(T_CDO_Rx.RxSystem,null) = 2 and
isnull(T_CDO_Rx.Created,null) <= '03/29/2004 14:03:29' and
isnull(T_CDO_Rx.DELETED,null) > '03/29/2004 14:03:29'
29 мар 04, 18:32    [602691]     Ответить | Цитировать Сообщить модератору
 Re: отключение индекса  [new]
KVic
Guest
Thanks Шаман,
и никакой ты не злой :-)

Я использовал ранее
ISNULL(NULLIF(1, 1), 0) + 

думал это не работает, но оказывается просто оптимизатор не хочет использвать индекс по UpperDescription. Пока ему явно не скажешь.
29 мар 04, 18:48    [602730]     Ответить | Цитировать Сообщить модератору
 Re: отключение индекса  [new]
Gena G.
Member

Откуда: Oz
Сообщений: 977
Просто ради интереса: а на кой надо отключать индексы? Что бы время от времени приходить и доить клиента новыми "улучшениями работоспособности"?
29 мар 04, 19:07    [602751]     Ответить | Цитировать Сообщить модератору
 Re: отключение индекса  [new]
KVic
Guest
Что бы получить оптимальный запрос.
Оптимизатор не всегда справляется с этой задачей в пределах тех критериев
которые необходимо выдержать.
29 мар 04, 19:18    [602765]     Ответить | Цитировать Сообщить модератору
 Re: отключение индекса  [new]
Gena G.
Member

Откуда: Oz
Сообщений: 977
Хм... Индексы только тогда не имеют смысла когда плотность распределения значения выше 10%

https://www.sql.ru/articles/mssql/03013101Indexes.shtml#12

Но с задачей принятия решения какой индекс брать или нет прекрасно справляется оптимизатор. Если не справляется - можно ему подсказать хинтами... Но что бы вот так, без индекса гнать запрос... Это хорошо работает только в одном случае - см. выше про плотность распределения значений.
29 мар 04, 19:58    [602804]     Ответить | Цитировать Сообщить модератору
 Re: отключение индекса  [new]
Suv
Guest
Спасибо Шаману. Наконец-то дельный совет дал. Но к сожалению, он тоже не всегда работает.
Рассмотрим пример:
select * from Cdo_RxLink 

where RxSource = 23921 or RxTarget = 23921

Оба поля имеют индексы с хорошей селективностью. Запрос отрабатывает, как надо: происходит поиск по первому индексу, потом по второму, результаты «слаживаются». Физически читается при этом не более десятка записей.
Теперь ужесточим условие
select * from Cdo_RxLink

where (RxSource = 23921 or RxTarget = 23921)
AND ('1/1/2004' BETWEEN /*inx_off*/ CREATED AND /*inx_off*/ DELETED)
Как правило запросы с более жёсткими условиями должны отрабатывать быстрее при правильном выборе плана. Но MSSQL решает, что лучше воспользоваться плохими индексами по CREATED и DELETED. И отговорить от этой затеи достаточно трудно:
1) Метод хорошо показал себя на Interbase. Вместо «/*inx_off*/» пишем выражение “ISNULL(NULLIF(1, 1), 0) +”. Как показало пару экспериментов, этот приём полностью эквивалентен предложению Шамана - это лишает MSSQL возможности использовать индекс по CREATED и DELETED полям. В этом случае MSSQL решает, что лучший выход – сделать full scan таблицы (???).
2) Принудительно в Hint указываю использовать индексы по RxSource и RxTarget. При этом в Плане MSSQL говорит, что эти индексы используются, но при этом почему-то читаются сотни тысяч записей(???).

Единственный способ уговорить MSSQL начинать нормально фильтровать с полей RxSource и RxTarget – сделать составной индекс по ним. Что для меня тоже несколько удивительно. И я не хочу менять структуру БД ради одного запроса.

Кто подскажет выход?

PS: По поводу отсутствия поддержки параметров в QA – это огромный недостаток, так как MSSQL имеет привычку кардинально менять план при смене параметра на его значение в SQL.
29 мар 04, 20:00    [602805]     Ответить | Цитировать Сообщить модератору
 Re: отключение индекса  [new]
Gena G.
Member

Откуда: Oz
Сообщений: 977
автор
Единственный способ уговорить MSSQL начинать нормально фильтровать с полей RxSource и RxTarget – сделать составной индекс по ним. Что для меня тоже несколько удивительно. И я не хочу менять структуру БД ради одного запроса.


Индексную вьюшку вы тоже делать не хотите?
29 мар 04, 20:14    [602817]     Ответить | Цитировать Сообщить модератору
 Re: отключение индекса  [new]
Gena G.
Member

Откуда: Oz
Сообщений: 977
автор
PS: По поводу отсутствия поддержки параметров в QA – это огромный недостаток, так как MSSQL имеет привычку кардинально менять план при смене параметра на его значение в SQL.


А чем SP не выход из положения?
29 мар 04, 20:15    [602818]     Ответить | Цитировать Сообщить модератору
 Re: отключение индекса  [new]
Suv
Guest
Gena G
Хм... Индексы только тогда не имеют смысла когда плотность распределения значения выше 10%

Речь идёт о выборе лучшего индекса, а не о отключения всех. В частности для самого первого примера нам необходимо использовать индекс по полю Description
29 мар 04, 20:21    [602822]     Ответить | Цитировать Сообщить модератору
 Re: отключение индекса  [new]
rst
Member

Откуда: Йобурк
Сообщений: 1005
нам необходимо использовать индекс

Странные цели..
Может все-таки стоит задача добиться большей скорости??

Возможно количество записей настолько мало - что дешевле использовать
table scan или кластерный индекс - нежели какой-то другой..

в принципе и до 100 000 записей бывает дешевле считать без индекса..
Это же копейки если таблица уже в памяти..

Я думаю отключение индексов в MSSQL - это лишнее..
Скорее всего это не даст никакого ускорения работы!

В итоге - для оптимизации по скорости - над каждым запросом все равно придется работать индивидуально..
29 мар 04, 20:34    [602836]     Ответить | Цитировать Сообщить модератору
 Re: отключение индекса  [new]
Gena G.
Member

Откуда: Oz
Сообщений: 977
автор
Речь идёт о выборе лучшего индекса, а не о отключения всех. В частности для самого первого примера нам необходимо использовать индекс по полю Description


Для этого существует index hint:

INDEX ( index_val [ ,...n ] )

Specifies the name or ID of the indexes to be used by SQL Server when processing the statement. Only one index hint per table can be specified.
29 мар 04, 20:35    [602837]     Ответить | Цитировать Сообщить модератору
 Re: отключение индекса  [new]
Suv
Guest
Gena G
Индексную вьюшку вы тоже делать не хотите?

Чесно говоря я слаб в MSSQL и не знаю, что такое индексная вьюшка и поможет ли она в данном случае, но думаю, что её необходимо будет создавать для каждой таблицы (а может даже каждого SQL запроса) для которой MSSQL неоптимально выбирает план. Хотелось бы иметь механизм управления индексами для каждого запроса отдельно.

Спасибо за совет. Я почитаю о индексных вьюшках.
29 мар 04, 20:40    [602845]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить