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

Откуда:
Сообщений: 113
Чего то я совсем запутался.
есть таблица порядка 150 000 записей
в ней два поля, в которых диапазоны значений - минимальное и максимальное значение (тип: bigint)
надо выбрать строки, в диапазон которых попадает переменная @var.
выбираться будет от 0 до максимум 100 строк.


какой лучше использовать индекс:
1) кластеризованный (поле1, поле2) и запрос типа
select поле1, поле2, поле3.... from tbl where  поле1 <=  @var AND @var  <= поле2


или

2) создать два простых и запрос изменить примерно так

select поле1, поле2, поле3.... from tbl where поле1 <=  @var ... IN (select поле2 from tbl where @var  <= поле2 
)
----------
Какой вариант создаст меньшую нагрузку чтения на диск ?
24 июл 16, 10:41    [19447296]     Ответить | Цитировать Сообщить модератору
 Re: какой лучше индекс при запросе поле1 <= @var AND @var <= поле2  [new]
iljy
Member

Откуда:
Сообщений: 8711
sahard,

а уникальный идентификатор в таблице есть? Если есть, то с хорошей вероятностью лучшим вариантом будет два некластерных индекса по полям и кластерный по ИД. Запрос менять точно не надо.

А вообще данных маловато. Нет инфы о ключах, о размере записи, о распределении значений полей и т.д. Но на 150т записей гораздо быстрее опроса на форуме будет просто проверить все варианты.
24 июл 16, 11:03    [19447315]     Ответить | Цитировать Сообщить модератору
 Re: какой лучше индекс при запросе поле1 <= @var AND @var <= поле2  [new]
sahard
Member

Откуда:
Сообщений: 113
iljy
sahard,

а уникальный идентификатор в таблице есть?

Можно сделать, не проблема

iljy
А вообще данных маловато. Нет инфы о ключах, о размере записи, о распределении значений полей и т.д.


Да самой таблицы пока нет. Есть в accesse c пару десятков предварительно созданных. Надо на mssql слить. Вот и думаю: в одну сливать или несколько оставить

iljy
А вообще данных маловато. Нет инфы о ключах, о размере записи, о распределении значений полей и т.д.


Видимо, да. Потому что в инете, чего только не написано. В том числе. что и при подобном запросе (неравенство) от индексов толку нет.
24 июл 16, 11:24    [19447351]     Ответить | Цитировать Сообщить модератору
 Re: какой лучше индекс при запросе поле1 <= @var AND @var <= поле2  [new]
iljy
Member

Откуда:
Сообщений: 8711
sahard
Видимо, да. Потому что в инете, чего только не написано. В том числе. что и при подобном запросе (неравенство) от индексов толку нет.


Это враки. Индекс прекрасно отрабатывает условие по неравенству, просто только по первому полю. Соответственно, при наличии двух отдельных индексов по полям оптимизатор сделает поиск по каждому, пересечет полученное множество ИД, после чего выберет недостающие поля из кластерного. И вот тут как раз и начинают работать вещи вроде распределения значений (количество полученных в результате ИД сильно влияет на производительность, Nested Loop + Key Lookup - довольно затратные операции). Но если таблица узкая, то скорее всего будет проще сделать кластерный по одному из полей и просто выбирать все записи за один проход.
24 июл 16, 11:33    [19447376]     Ответить | Цитировать Сообщить модератору
 Re: какой лучше индекс при запросе поле1 <= @var AND @var <= поле2  [new]
Alfred2016
Guest
IN - будет разбиваться на ИЛИ
Что существенно медленнее чем >= AND <=

При этом надо помнить что поиск использует индексы, в их отсутствии можно не заметить никакой разницы (см соответствующие разделы по оптимизации/индексам/итд)

А вообще чтобы проверить два разных запроса используйте клиентскую статистику/планы запросов и разбирайтесь в них
24 июл 16, 15:08    [19447807]     Ответить | Цитировать Сообщить модератору
 Re: какой лучше индекс при запросе поле1 <= @var AND @var <= поле2  [new]
sahard
Member

Откуда:
Сообщений: 113
iljy
Но если таблица узкая, то скорее всего будет проще сделать кластерный по одному из полей и просто выбирать все записи за один проход.


Я вот здесь [url=]https://habrahabr.ru/post/188704/[/url] такую вещь прочитал

Представим себе сканирование диапазона в 20 тысяч записей по некластеризованному индексу, построенному на кластеризованном. Теперь понадобится выполнить не 20 тысяч логических чтений страницы по известному RID, а 20 тысяч поисков в кластеризованном индексе – и каждый поиск потребует 3, а то и более, логических чтений.


В связи с этим возникла, наверное, глупая идея.

1) создать кластеризованный индекс. Соответственно строки таблицы следует в нужном мне порядке.
2) Удаляю этот индекс.
И вот тут у меня вопрос: Сама таблица сохранит порядок следования строк (как при наличии кл. индекса) или вернется в первоначальное состояние. ?
24 июл 16, 18:23    [19448215]     Ответить | Цитировать Сообщить модератору
 Re: какой лучше индекс при запросе поле1 <= @var AND @var <= поле2  [new]
iljy
Member

Откуда:
Сообщений: 8711
sahard,

я вам говорю о том же самом: если поиск в результате дает вам много записей, то KeyLookup будет дорогим.

Идея глупая. Порядок записей в файлах может физически не совпадать даже с кластерным индексом при его наличии, страницы перемешаны.
24 июл 16, 19:21    [19448369]     Ответить | Цитировать Сообщить модератору
 Re: какой лучше индекс при запросе поле1 <= @var AND @var <= поле2  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
sahard
В связи с этим возникла, наверное, глупая идея.

1) создать кластеризованный индекс. Соответственно строки таблицы следует в нужном мне порядке.
2) Удаляю этот индекс.

И вот тут у меня вопрос: Сама таблица сохранит порядок следования строк (как при наличии кл. индекса) или вернется в первоначальное состояние. ?
А идея-то в чем?
25 июл 16, 01:52    [19449355]     Ответить | Цитировать Сообщить модератору
 Re: какой лучше индекс при запросе поле1 <= @var AND @var <= поле2  [new]
aleks2
Guest
Идея

select поле1, поле2, поле3.... from tbl where  поле1 <=  @var AND @var  <= поле2


эквивалентно

select поле1, поле2, поле3.... from tbl where  поле1 <=  @var AND 1./поле2  <= 1/@var  


делаем новое поле [обр.поле2] = 1./поле2

select поле1, поле2, поле3.... from tbl where  поле1 <=  @var AND [обр.поле2]  <= 1./@var 


индекс (поле1, [обр.поле2]).

И фсе хорошо.

ЗЫ. Хотя я бы понадеялся на index intersection.
25 июл 16, 06:12    [19449398]     Ответить | Цитировать Сообщить модератору
 Re: какой лучше индекс при запросе поле1 <= @var AND @var <= поле2  [new]
TaPaK
Member

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

автор
индекс (поле1, [обр.поле2]).

И фсе хорошо.

так всё равно возьмёт до первого неравенства... или я не улавливаю суть этого извращения
25 июл 16, 09:29    [19449575]     Ответить | Цитировать Сообщить модератору
 Re: какой лучше индекс при запросе поле1 <= @var AND @var <= поле2  [new]
aleks2
Guest
TaPaK
aleks2,

автор
индекс (поле1, [обр.поле2]).

И фсе хорошо.

так всё равно возьмёт до первого неравенства... или я не улавливаю суть этого извращения


Извращения - они трудноуловимы.
В варианте ОДИНАКОВОГО неравенства поле1 <= @var AND [обр.поле2] <= 1./@var
поля ( поле1, [обр.поле2] ) можно рассматривать как "одно поле"

( поле1, [обр.поле2] ) <= (@var, 1./@var)

т.е. это сработает как

поле <= @переменная

для индекса (поле) в случае простого поля.

Ничего эффективнее тредстартеру не светит.

ЗЫ. Кстате, вместо 1./@var можно использовать смену знака -@var. Возможно, это будет универсальнее.
25 июл 16, 10:56    [19449872]     Ответить | Цитировать Сообщить модератору
 Re: какой лучше индекс при запросе поле1 <= @var AND @var <= поле2  [new]
iljy
Member

Откуда:
Сообщений: 8711
aleks2,

того же самого эффекта можно добиться, просто указав в индексе DESC для Поле2. Да, это немного сэкономит на установке начальной точки для поиска (сразу сместимся еще и на значения второго поля). Даст ли это реальный выигрыш... Хрен знает.
25 июл 16, 11:13    [19449986]     Ответить | Цитировать Сообщить модератору
 Re: какой лучше индекс при запросе поле1 <= @var AND @var <= поле2  [new]
sahard
Member

Откуда:
Сообщений: 113
Гавриленко Сергей Алексеевич
А идея-то в чем?


Чтоб записи шли по нужному мне порядку и соответсвенно было меньше обращений к диску.
А поиск осуществлять через простые индексе.
Но мне уже объяснили, что ничего не выйдет :))
---------

Протестил несколько вариантов. Самым шустрым оказался первый вариант с кластеризованным индексом.

При варианте с двумя простыми индексами оптимизатор не стал использовать индекс, а просто просканировал таблицу.

С точки зрения времени разница не слишком существенная: 2-3 раза. Но поскольку все это меряется в млсек. - то....

Меня больше волнует при каком варианте диск сильнее насилуется?
25 июл 16, 18:25    [19453308]     Ответить | Цитировать Сообщить модератору
 Re: какой лучше индекс при запросе поле1 <= @var AND @var <= поле2  [new]
sahard
Member

Откуда:
Сообщений: 113
aleks2
TaPaK
aleks2,

пропущено...

так всё равно возьмёт до первого неравенства... или я не улавливаю суть этого извращения


Извращения - они трудноуловимы.
В варианте ОДИНАКОВОГО неравенства поле1 <= @var AND [обр.поле2] <= 1./@var
поля ( поле1, [обр.поле2] ) можно рассматривать как "одно поле"

( поле1, [обр.поле2] ) <= (@var, 1./@var)

т.е. это сработает как

поле <= @переменная

для индекса (поле) в случае простого поля.

Ничего эффективнее тредстартеру не светит.

ЗЫ. Кстате, вместо 1./@var можно использовать смену знака -@var. Возможно, это будет универсальнее.


Мудрено как то
25 июл 16, 18:32    [19453353]     Ответить | Цитировать Сообщить модератору
 Re: какой лучше индекс при запросе поле1 <= @var AND @var <= поле2  [new]
sahard
Member

Откуда:
Сообщений: 113
iljy
aleks2,

того же самого эффекта можно добиться, просто указав в индексе DESC для Поле2. Да, это немного сэкономит на установке начальной точки для поиска (сразу сместимся еще и на значения второго поля). Даст ли это реальный выигрыш... Хрен знает.


Вряд ли будет заметный выигрыш или вряд ли я его ощутю. Поле2 больше поле Поле1 в среднем на 0,001 %. Хотя и есть с десяток записей, где разница максимальна.
Но попробую
25 июл 16, 18:42    [19453420]     Ответить | Цитировать Сообщить модератору
 Re: какой лучше индекс при запросе поле1 <= @var AND @var <= поле2  [new]
invm
Member

Откуда: Москва
Сообщений: 9413
sahard
Протестил несколько вариантов. Самым шустрым оказался первый вариант с кластеризованным индексом.
Если на таблице есть/будут другие индексы, то надо очень сильно подумать, прежде чем делать кластерный (наверняка еще и неуникальный) по (поле1, поле2).
а) создать обычный индекс - (поле1, поле2) include (поле3, поле4, ...).
б) или просто (поле1, поле2) и запрос переписать:
select поле1, поле2, поле3.... from tbl where  поле1 <=  @var AND @var <= поле2 option (recompile)

sahard
Меня больше волнует при каком варианте диск сильнее насилуется?
Включить statistics io и проанализировать результат.
25 июл 16, 18:49    [19453474]     Ответить | Цитировать Сообщить модератору
 Re: какой лучше индекс при запросе поле1 <= @var AND @var <= поле2  [new]
sahard
Member

Откуда:
Сообщений: 113
invm
наверняка еще и неуникальный

Еще какой не уникальный. Но другие поля в запросе не нужны, да и ничего кардинально не изменяют.

invm
Включить statistics io и проанализировать результат.


Результат : "записей возвращено: 128
Таблица "tbl_49". Число просмотров 1, логических чтений 94, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
(выполнение: 0 мс; всего: 125 мс)" */
--------

Т.е. вся моя база в памяти ?! А я переживал :))
25 июл 16, 20:05    [19453685]     Ответить | Цитировать Сообщить модератору
 Re: какой лучше индекс при запросе поле1 <= @var AND @var <= поле2  [new]
sahard
Member

Откуда:
Сообщений: 113
Всем большое спасибо за помощь и внимание!
25 июл 16, 20:09    [19453699]     Ответить | Цитировать Сообщить модератору
 Re: какой лучше индекс при запросе поле1 <= @var AND @var <= поле2  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
iljy
sahard
Видимо, да. Потому что в инете, чего только не написано. В том числе. что и при подобном запросе (неравенство) от индексов толку нет.


Это враки. Индекс прекрасно отрабатывает условие по неравенству, просто только по первому полю. Соответственно, при наличии двух отдельных индексов по полям оптимизатор сделает поиск по каждому, пересечет полученное множество ИД
sql.ru тоже интернет и тут тоже чушь всякую пишут. Типа пересечения индексов. Предположим значение оказалось ровно посредине. Тогда в результате 2х поисков по индексам (считай 1 полный скан) получится 2 НЕСОРТИРОВАННЫХ списка по 75 тысяч строк и потом их ещё нужно объединить. Hash-ем! Гениальное решение. Будет намного дешевле просканировать весь индекс где есть нужные два поля, чем так извращаться с пересечениями.
26 июл 16, 03:45    [19454570]     Ответить | Цитировать Сообщить модератору
 Re: какой лучше индекс при запросе поле1 <= @var AND @var <= поле2  [new]
aleks2
Guest
Mind
iljy
пропущено...


Это враки. Индекс прекрасно отрабатывает условие по неравенству, просто только по первому полю. Соответственно, при наличии двух отдельных индексов по полям оптимизатор сделает поиск по каждому, пересечет полученное множество ИД
sql.ru тоже интернет и тут тоже чушь всякую пишут. Типа пересечения индексов. Предположим значение оказалось ровно посредине. Тогда в результате 2х поисков по индексам (считай 1 полный скан) получится 2 НЕСОРТИРОВАННЫХ списка по 75 тысяч строк и потом их ещё нужно объединить. Hash-ем! Гениальное решение. Будет намного дешевле просканировать весь индекс где есть нужные два поля, чем так извращаться с пересечениями.


Говорят, что оно, все ж, существует... ажно с
http://www.databasejournal.com/features/mssql/article.php/1438821/Using-Index-Intersection.htm MS SQK 7.
26 июл 16, 05:58    [19454602]     Ответить | Цитировать Сообщить модератору
 Re: какой лучше индекс при запросе поле1 <= @var AND @var <= поле2  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31445
Mind
Предположим значение оказалось ровно посредине. Тогда в результате 2х поисков по индексам (считай 1 полный скан) получится 2 НЕСОРТИРОВАННЫХ списка по 75 тысяч строк и потом их ещё нужно объединить. Hash-ем! Гениальное решение
Это если каждое поле по отдельности даёт по половине таблицы.
А если по 1/1000, то вполне разумно.
26 июл 16, 10:42    [19455350]     Ответить | Цитировать Сообщить модератору
 Re: какой лучше индекс при запросе поле1 <= @var AND @var <= поле2  [new]
o-o
Guest
aleks2
Говорят, что оно, все ж, существует... ажно с
http://www.databasejournal.com/features/mssql/article.php/1438821/Using-Index-Intersection.htm MS SQK 7.

оно-то да, только условие там в примере на равенство,
а у ТС оба неравенства, да еще и значение для сравнения одно и то же.
поэтому очень вероятно, что если по одному условию мало строк отберется,
то по второму много
alexeyvg
Это если каждое поле по отдельности даёт по половине таблицы.
А если по 1/1000, то вполне разумно.

вот пример из вышеприведенной статьи,
те же тестовые данные.
первый запрос как у автора статьи,
а остальные -- тот же запрос, те же 2 индекса по каждому из полей,
только условия переделаны на неравенство.
беру 3 случая: такую фамилию, что уж больше ее -- нет.
либо имя такое, что меньше его нет
(неравенство беру строгое, чтоб уж вообще ничего не возвращать)
и случай "Livia" -- средний.
и что.
только в случае равенства было выбрано "пересечение индексов".
в неравенствах, даже когда одно из них дает заведомо пустой набор,
никакое пересечение не используется.
если с одной стороны неравенства мало строк, то с другой все равно много.
ну а если значение "посередине", то как и сказал Mind, проще всего просканировать

К сообщению приложен файл. Размер - 113Kb
26 июл 16, 14:37    [19457223]     Ответить | Цитировать Сообщить модератору
 Re: какой лучше индекс при запросе поле1 <= @var AND @var <= поле2  [new]
invm
Member

Откуда: Москва
Сообщений: 9413
И при неравенствах в предикатах пересечение индексов может быть задействовано.
Как говорится, - "зависит от". Например, от ширины таблицы:
+
use tempdb;
go

create table dbo.t (id int identity primary key, v1 int not null, v2 int not null, dummy char(200) null);

insert into dbo.t
 (v1, v2)
select top (100000)
 c.v * 1000, c.v * 2000
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b cross apply
 (select rand(checksum(newid()))) c(v);

create index IX_t__v1 on dbo.t (v1);
create index IX_t__v2 on dbo.t (v2);
go

set showplan_text on;
go

declare @v int;
select * from dbo.t where @v between v1 and v2;

/*
  |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[t].[PK__t__3213E83F222BD200]), WHERE:([@v]>=[tempdb].[dbo].[t].[v1] AND [@v]<=[tempdb].[dbo].[t].[v2]))
*/
go

set showplan_text off;
go

alter table dbo.t alter column dummy char(2000) null;
go

set showplan_text on;
go

declare @v int;
select * from dbo.t where @v between v1 and v2;

/*
  |--Nested Loops(Inner Join, OUTER REFERENCES:([tempdb].[dbo].[t].[id], [Expr1003]) OPTIMIZED WITH UNORDERED PREFETCH)
       |--Hash Match(Inner Join, HASH:([tempdb].[dbo].[t].[id])=([tempdb].[dbo].[t].[id]))
       |    |--Index Seek(OBJECT:([tempdb].[dbo].[t].[IX_t__v2]), SEEK:([tempdb].[dbo].[t].[v2] >= [@v]) ORDERED FORWARD)
       |    |--Index Seek(OBJECT:([tempdb].[dbo].[t].[IX_t__v1]), SEEK:([tempdb].[dbo].[t].[v1] <= [@v]) ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[t].[PK__t__3213E83F222BD200]), SEEK:([tempdb].[dbo].[t].[id]=[tempdb].[dbo].[t].[id]) LOOKUP ORDERED FORWARD)
*/
go

set showplan_text off;
go

drop table dbo.t;
go
26 июл 16, 15:15    [19457473]     Ответить | Цитировать Сообщить модератору
 Re: какой лучше индекс при запросе поле1 <= @var AND @var <= поле2  [new]
o-o
Guest
invm,

а зачем же вы так офигительно фрагментировали кластерный?
сделайте
alter table dbo.t REBUILD;

и снова план опросите.
/ну или сразу создайте таблицу с филлером в 2000 символов, все одно/
вот уж от кого-кого, но от вас такое издевательство прочесть это неожиданно
26 июл 16, 15:43    [19457683]     Ответить | Цитировать Сообщить модератору
 Re: какой лучше индекс при запросе поле1 <= @var AND @var <= поле2  [new]
invm
Member

Откуда: Москва
Сообщений: 9413
o-o
а зачем же вы так офигительно фрагментировали кластерный?
В демонстрационных целях это неважно.
Фрагментация конечно влияет на итоговую оценку. В моем окружении скан даже дефрагментированной таблицы дороже варианта с пересечением индексов.
26 июл 16, 15:56    [19457775]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить