Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Какой индекс(ы) будет оптимально  [new]
Алексаша
Member

Откуда: Левый берег
Сообщений: 382
Здравствуйте.

Есть таблица лиц примерного содержания
Face
idFNMmmddyyyy
1ИвановИванИванович01011970
2ПетровПетрПетрович02021980
3СергеевСергейСергеевич03031990
4МарковМаркМаркович01021981


запрос

create procedure sp_face
  @F varchar(50) = null,
  @N varchar(50) = null,
  @M varchar(50) = null,
  @dd varchar(50) = null,
  @mm varchar(50) = null,
  @yyyy varchar(50) = null
as
begin
SET NOCOUNT ON; 

select *
  from Face 
  where 
   F = isnull(@F, F) and
   N = isnull(@N, N) and
   M = isnull(@M, M) and
   mm = isnull(@mm, mm) and
   dd = isnull(@dd, dd) and
   yyyy = isnull(@yyyy, yyyy)
end

как правильно поступить с индексами?
1. сделать индекс на все поля в разделе where
2. сделать один покрывающий индекс
3. сделать 1. и 2.
?
16 апр 19, 13:11    [21863619]     Ответить | Цитировать Сообщить модератору
 Re: Какой индекс(ы) будет оптимально  [new]
monsenior
Member

Откуда: Москва
Сообщений: 778
Алексаша
Здравствуйте.

Есть таблица лиц примерного содержания
Face
idFNMmmddyyyy
1ИвановИванИванович01011970
2ПетровПетрПетрович02021980
3СергеевСергейСергеевич03031990
4МарковМаркМаркович01021981


запрос

create procedure sp_face
  @F varchar(50) = null,
  @N varchar(50) = null,
  @M varchar(50) = null,
  @dd varchar(50) = null,
  @mm varchar(50) = null,
  @yyyy varchar(50) = null
as
begin
SET NOCOUNT ON; 

select *
  from Face 
  where 
   F = isnull(@F, F) and
   N = isnull(@N, N) and
   M = isnull(@M, M) and
   mm = isnull(@mm, mm) and
   dd = isnull(@dd, dd) and
   yyyy = isnull(@yyyy, yyyy)
end

как правильно поступить с индексами?
1. сделать индекс на все поля в разделе where
2. сделать один покрывающий индекс
3. сделать 1. и 2.
?


п.2, т.к. запрос всегда идет по всем полям.
16 апр 19, 14:18    [21863774]     Ответить | Цитировать Сообщить модератору
 Re: Какой индекс(ы) будет оптимально  [new]
msLex
Member

Откуда:
Сообщений: 8091
monsenior
Алексаша
Здравствуйте.

Есть таблица лиц примерного содержания
Face
idFNMmmddyyyy
1ИвановИванИванович01011970
2ПетровПетрПетрович02021980
3СергеевСергейСергеевич03031990
4МарковМаркМаркович01021981


запрос

create procedure sp_face
  @F varchar(50) = null,
  @N varchar(50) = null,
  @M varchar(50) = null,
  @dd varchar(50) = null,
  @mm varchar(50) = null,
  @yyyy varchar(50) = null
as
begin
SET NOCOUNT ON; 

select *
  from Face 
  where 
   F = isnull(@F, F) and
   N = isnull(@N, N) and
   M = isnull(@M, M) and
   mm = isnull(@mm, mm) and
   dd = isnull(@dd, dd) and
   yyyy = isnull(@yyyy, yyyy)
end


как правильно поступить с индексами?
1. сделать индекс на все поля в разделе where
2. сделать один покрывающий индекс
3. сделать 1. и 2.
?


п.2, т.к. запрос всегда идет по всем полям.


покрывающий что? все поля из таблицы?
каждый такой запрос будет читать весь этот индекс, чем это лучше скана кластерного индекса или кучи?
16 апр 19, 14:21    [21863781]     Ответить | Цитировать Сообщить модератору
 Re: Какой индекс(ы) будет оптимально  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
monsenior,

конструкция a = a не делает использование "всех" полей.


Алексаша
если нет обязательных полей, то в общем-то всё без разници, или создать комбинацию на большинство случаев ака
F,N,M
16 апр 19, 14:30    [21863795]     Ответить | Цитировать Сообщить модератору
 Re: Какой индекс(ы) будет оптимально  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20527
Функция в условиях отбора - это фуллскан, так что пофиг индексы.

Преобразуйте условия в комбинацию (field = @var OR @var IS NULL), тогда оптимизатор вычислит выражения с константой ещё на стадии анализа, одно из двух условий отбросит, и сможет использовать индекс(ы).

Впрочем, для полной оптимальности тех индексов потребуется штук 20 навскидку...
16 апр 19, 14:31    [21863796]     Ответить | Цитировать Сообщить модератору
 Re: Какой индекс(ы) будет оптимально  [new]
msLex
Member

Откуда:
Сообщений: 8091
Алексаша
Здравствуйте.

Есть таблица лиц примерного содержания
Face
idFNMmmddyyyy
1ИвановИванИванович01011970
2ПетровПетрПетрович02021980
3СергеевСергейСергеевич03031990
4МарковМаркМаркович01021981


запрос

create procedure sp_face
  @F varchar(50) = null,
  @N varchar(50) = null,
  @M varchar(50) = null,
  @dd varchar(50) = null,
  @mm varchar(50) = null,
  @yyyy varchar(50) = null
as
begin
SET NOCOUNT ON; 

select *
  from Face 
  where 
   F = isnull(@F, F) and
   N = isnull(@N, N) and
   M = isnull(@M, M) and
   mm = isnull(@mm, mm) and
   dd = isnull(@dd, dd) and
   yyyy = isnull(@yyyy, yyyy)
end


как правильно поступить с индексами?
1. сделать индекс на все поля в разделе where
2. сделать один покрывающий индекс
3. сделать 1. и 2.
?



Если хотите индексированного поиска, то

1. Перепишите isnull на (F = @F or @f is null)
2. Добавьте option(recompile) в конец запроса
3. Самое сложное.
Нужно понять, скорости работы при указании (не null) каких из параметров этой процедуры вы ожидаете.
Какие их комбинации устанавливаются одновременно.
Исходя из этого создать нужные индексы.

Например,
1. Вы выяснили, что самым частым случаем является поиск по фамилии, иногда добавляют имя
Сделали индекс по полям F, N

2. Дату рождения у вас всегда ищут как день + месяц, иногда уточняют год
Сделали индекс по полям dd, mm, yyy


Серебряной пули (одного индекса) которая решит все возможные кейсы - нет.
16 апр 19, 14:40    [21863821]     Ответить | Цитировать Сообщить модератору
 Re: Какой индекс(ы) будет оптимально  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
msLex
3. Самое сложное.
Нужно понять, скорости работы при указании (не null) каких из параметров этой процедуры вы ожидаете.
Какие их комбинации устанавливаются одновременно.
Исходя из этого создать нужные индексы.

Например,
1. Вы выяснили, что самым частым случаем является поиск по фамилии, иногда добавляют имя
Сделали индекс по полям F, N

2. Дату рождения у вас всегда ищут как день + месяц, иногда уточняют год
Сделали индекс по полям dd, mm, yyy
Помню, накапливал статистику для подобного случая. Потом сделал несколько индексов, покрывающих 99% запросов.
16 апр 19, 14:43    [21863831]     Ответить | Цитировать Сообщить модератору
 Re: Какой индекс(ы) будет оптимально  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
alexeyvg
msLex
3. Самое сложное.
Нужно понять, скорости работы при указании (не null) каких из параметров этой процедуры вы ожидаете.
Какие их комбинации устанавливаются одновременно.
Исходя из этого создать нужные индексы.

Например,
1. Вы выяснили, что самым частым случаем является поиск по фамилии, иногда добавляют имя
Сделали индекс по полям F, N

2. Дату рождения у вас всегда ищут как день + месяц, иногда уточняют год
Сделали индекс по полям dd, mm, yyy
Помню, накапливал статистику для подобного случая. Потом сделал несколько индексов, покрывающих 99% запросов.

если объёмы не фантастика, то можно и от обратного, наклепать, через месяц удалить не используемые :)
16 апр 19, 14:47    [21863840]     Ответить | Цитировать Сообщить модератору
 Re: Какой индекс(ы) будет оптимально  [new]
msLex
Member

Откуда:
Сообщений: 8091
alexeyvg
msLex
3. Самое сложное.
Нужно понять, скорости работы при указании (не null) каких из параметров этой процедуры вы ожидаете.
Какие их комбинации устанавливаются одновременно.
Исходя из этого создать нужные индексы.

Например,
1. Вы выяснили, что самым частым случаем является поиск по фамилии, иногда добавляют имя
Сделали индекс по полям F, N

2. Дату рождения у вас всегда ищут как день + месяц, иногда уточняют год
Сделали индекс по полям dd, mm, yyy
Помню, накапливал статистику для подобного случая. Потом сделал несколько индексов, покрывающих 99% запросов.

Спасибо за пример-подтверждение.
Значит подобный подход к поиску по разным комбинациям фильтров распространен.
16 апр 19, 14:48    [21863842]     Ответить | Цитировать Сообщить модератору
 Re: Какой индекс(ы) будет оптимально  [new]
Алексаша
Member

Откуда: Левый берег
Сообщений: 382
Парни, всем спасибо!
16 апр 19, 14:50    [21863847]     Ответить | Цитировать Сообщить модератору
 Re: Какой индекс(ы) будет оптимально  [new]
Алексаша
Member

Откуда: Левый берег
Сообщений: 382
msLex,


msLex
Например,
1. Вы выяснили, что самым частым случаем является поиск по фамилии, иногда добавляют имя
Сделали индекс по полям F, N


Индекс покрывающий т.е. 2 поля? Не два индекса по одному полю в каждом?
16 апр 19, 14:54    [21863859]     Ответить | Цитировать Сообщить модератору
 Re: Какой индекс(ы) будет оптимально  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
TaPaK
alexeyvg
Помню, накапливал статистику для подобного случая. Потом сделал несколько индексов, покрывающих 99% запросов.

если объёмы не фантастика, то можно и от обратного, наклепать, через месяц удалить не используемые :)
Дык сколько их делать, сотню? Комбинаций то много.
16 апр 19, 14:58    [21863864]     Ответить | Цитировать Сообщить модератору
 Re: Какой индекс(ы) будет оптимально  [new]
msLex
Member

Откуда:
Сообщений: 8091
Алексаша
msLex,


msLex
Например,
1. Вы выяснили, что самым частым случаем является поиск по фамилии, иногда добавляют имя
Сделали индекс по полям F, N


Индекс покрывающий т.е. 2 поля? Не два индекса по одному полю в каждом?


Для этого примера два поля, и именно в этом порядке
В этом случае, он будет работать как при фильтре только по фамилии так и при фильтре по фамилия + имя

Опять же, вполне может хватить и фильтра просто по фамилии, если она почти уникальна в ваших данных.
16 апр 19, 14:58    [21863865]     Ответить | Цитировать Сообщить модератору
 Re: Какой индекс(ы) будет оптимально  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Алексаша
msLex,


msLex
Например,
1. Вы выяснили, что самым частым случаем является поиск по фамилии, иногда добавляют имя
Сделали индекс по полям F, N


Индекс покрывающий т.е. 2 поля? Не два индекса по одному полю в каждом?
Да. Собстьвенно, индекс по мм вряд ли имеет смысл делать отдельно, селективность низкая.
Ну и вообще, индекс на несколько полей всегда лучше, если все эти поля используются в запросе.
16 апр 19, 14:59    [21863871]     Ответить | Цитировать Сообщить модератору
 Re: Какой индекс(ы) будет оптимально  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
alexeyvg
TaPaK
пропущено...

если объёмы не фантастика, то можно и от обратного, наклепать, через месяц удалить не используемые :)
Дык сколько их делать, сотню? Комбинаций то много.

ну если не думать то 120
16 апр 19, 15:14    [21863902]     Ответить | Цитировать Сообщить модератору
 Re: Какой индекс(ы) будет оптимально  [new]
uaggster
Member

Откуда:
Сообщений: 827
Кстати, а запрос не кривоватый?
А то ведь отчество иногда пустым бывает. Или там не NULL, а '' в таблице будет?
16 апр 19, 15:26    [21863924]     Ответить | Цитировать Сообщить модератору
 Re: Какой индекс(ы) будет оптимально  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
msLex
Серебряной пули (одного индекса) которая решит все возможные кейсы - нет.
Есть. Полнотекстовый.
16 апр 19, 15:41    [21863948]     Ответить | Цитировать Сообщить модератору
 Re: Какой индекс(ы) будет оптимально  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7758
Неужели планируются запросы, возвращающие строки только по первым числам или по декабрям?
16 апр 19, 15:57    [21863967]     Ответить | Цитировать Сообщить модератору
 Re: Какой индекс(ы) будет оптимально  [new]
msLex
Member

Откуда:
Сообщений: 8091
Владислав Колосов
Неужели планируются запросы, возвращающие строки только по первым числам или по декабрям?

Отдельно - вряд ли.
А вот, например, за 1-е декабря, независимо от года, запросто.
Стандартная задача поиск сотрудников, у которых сегодня ДР.

invm
Есть. Полнотекстовый.

Есть еще CS.
Они универсальнее, но обычно проигрывают по скорости специально созданным индексам.
16 апр 19, 16:42    [21864024]     Ответить | Цитировать Сообщить модератору
 Re: Какой индекс(ы) будет оптимально  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Владислав Колосов
Неужели планируются запросы, возвращающие строки только по первым числам или по декабрям?
Всё правильно, ну вот и нужно понять, какие сочетания обычны, а какие невероятны.
Как понять? Наверное, лучше всего собрать статистику, потому что для каждой предметной области типичны свои сочетания.

По первым числам - например, секретарша смотрит, у кого там день рождения сегодня, 16-го. (ввести 16 апреля, а не просто 16, ей не приходит в голову - она и так увидит в списке, у кого из них в апреле; все бухгалтеры и секретарши прекрасно знают, что проще выгрузить огромную таблицу, правильно отсортировать, и потом пролистать, чем накладывать фильтры) :-)
16 апр 19, 21:12    [21864270]     Ответить | Цитировать Сообщить модератору
 Re: Какой индекс(ы) будет оптимально  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
msLex
Есть еще CS.
Они универсальнее, но обычно проигрывают по скорости специально созданным индексам.
Да.

Только я не про вообще, а про конкретную задачу ТС.
Добавляется вычисляемый столбец с, например, таким содержимым
id:1 F:Иванов N:Иван M:Иванович mm:01 dd:01 yyyy:1970
и по нему строится полнотекстовый индекс с отключенным стоп-листом.
16 апр 19, 21:47    [21864283]     Ответить | Цитировать Сообщить модератору
 Re: Какой индекс(ы) будет оптимально  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
invm
Добавляется вычисляемый столбец с, например, таким содержимым
id:1 F:Иванов N:Иван M:Иванович mm:01 dd:01 yyyy:1970

и по нему строится полнотекстовый индекс с отключенным стоп-листом.
А чем это будет эффективнее отдельных индексов по полям?
Веть FTS будет искать в индексе наборы про нескольким значениям (словам), и искать пересечение.
И сиквел может искать по нескольким обычным индексам подмножества, и искать пересечение.
Может, в FTS это делается эффективнее, но мне не верится, ядро из 90-х они пока в своих надстройках не превзошли :-)
16 апр 19, 22:37    [21864306]     Ответить | Цитировать Сообщить модератору
 Re: Какой индекс(ы) будет оптимально  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
alexeyvg
Веть FTS будет искать в индексе наборы про нескольким значениям (словам), и искать пересечение.
И сиквел может искать по нескольким обычным индексам подмножества, и искать пересечение.
При использовании множества индексов по столбцам:
- как часто вы наблюдали план, в котором оптимизатор самостоятельно решил строить пересечение более чем двух индексов?
- каким образом обеспечить неизменность запроса при добавлении/удалении/усложнении критериев поиска?
17 апр 19, 09:34    [21864457]     Ответить | Цитировать Сообщить модератору
 Re: Какой индекс(ы) будет оптимально  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
invm
alexeyvg
Веть FTS будет искать в индексе наборы про нескольким значениям (словам), и искать пересечение.
И сиквел может искать по нескольким обычным индексам подмножества, и искать пересечение.
При использовании множества индексов по столбцам:
- как часто вы наблюдали план, в котором оптимизатор самостоятельно решил строить пересечение более чем двух индексов?
- каким образом обеспечить неизменность запроса при добавлении/удалении/усложнении критериев поиска?
Более двух не видел, но разве этого мало? Наверное, сервер считает, что быстрее просканировать небольшой результат, чем искать пересечение с третьим огромным подмножеством?
Вот как сервер ищет в FTS? Он точно будет джойнить три независимых поиска, или ограничится двумя, и отфильтрует результат?
Думаю, что оптимальный путь зависит от конкретных данных.

Второй пункт не понял, зачем обеспечивать неизменность?
17 апр 19, 10:16    [21864523]     Ответить | Цитировать Сообщить модератору
 Re: Какой индекс(ы) будет оптимально  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
alexeyvg
Наверное, сервер считает, что быстрее просканировать небольшой результат, чем искать пересечение с третьим огромным подмножеством?
Так вот это и проблема - получить пересечение двух индексов в объеме, достаточном, чтобы потом *Lookup с фильтрацией не убил производительность. Можете такое гарантировать без ручных оптимизаций?
alexeyvg
Второй пункт не понял, зачем обеспечивать неизменность?
Чтоб не переписывать всякий раз запрос.

К тому же, асинхронно поддерживать один FTI может оказаться банально выгоднее синхронной поддержки N индексов.
17 апр 19, 10:45    [21864572]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить