Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
 Re: Индексы + Where Not in  [new]
0-0
Guest
Glory
0-0
пропущено...


Там же Not in, а это куча AND, а не OR.

А есть разница ? Как в одиночном поиске обработать сразу все НЕравно ? Или все Равно ?


В данном вопросе разницы по большому счету нет.
Мне непонятно категоричное высказывание, что индекс не поможет.
11 июн 15, 12:31    [17759550]     Ответить | Цитировать Сообщить модератору
 Re: Индексы + Where Not in  [new]
Glory
Member

Откуда:
Сообщений: 104751
0-0
Мне непонятно категоричное высказывание, что индекс не поможет.

А мне непонятна подгонка тестов под свой ответ.
11 июн 15, 12:33    [17759564]     Ответить | Цитировать Сообщить модератору
 Re: Индексы + Where Not in  [new]
0-0
Guest
relief,

При not exists скорее всего будет index scan вашего индекса.
Перепишите запрос.
11 июн 15, 12:44    [17759625]     Ответить | Цитировать Сообщить модератору
 Re: Индексы + Where Not in  [new]
o-o
Guest
Glory
0-0
Мне непонятно категоричное высказывание, что индекс не поможет.

А мне непонятна подгонка тестов под свой ответ.

потому что когда кричат: такого не бывает, достаточно одного примера, чтоб это развеять.
с припиской, между прочим, корректности ради:
o-o
ответ сразу обоим, Glory и Добрый Э - Эх.
специально для вас смоделировано.
не скажу, что частый/нормальный случай, но что угодно бывает

а что имеется у ТС, знает только он
11 июн 15, 12:47    [17759646]     Ответить | Цитировать Сообщить модератору
 Re: Индексы + Where Not in  [new]
relief
Member

Откуда:
Сообщений: 1197
o-o
relief
пропущено...


create table Cities 
(
 name nvarchar(100),
 Population int
)
create nonclustered index ix_city on Cities(name) include (population)

в таблицу заливается 100К городов

Select Name, Population 
From Cities 
Where Name NOT IN (список из 100 городов)

ok,
a где ответ на вопрос, сколько из этих 100К городов различны?
сколько строк вам возвращает запрос, те же 100К или однy?
если у вас всего 2 различных города в самой таблице,
или в списке из 100 городов вообще нет ни одного из тех, что в таблице,
возвращать все равно надо все.

..a актуальный план бы на все ответил


все значения уникальны
запрос возвращает 90К
11 июн 15, 12:48    [17759654]     Ответить | Цитировать Сообщить модератору
 Re: Индексы + Where Not in  [new]
Glory
Member

Откуда:
Сообщений: 104751
o-o
потому что когда кричат: такого не бывает, достаточно одного примера, чтоб это развеять.
с припиской, между прочим, корректности ради:

Здесь разбирают конкретный случай ТС, а не ваш.
Так что не надо, как и a_voronin, самому задавать вопрос и самому на него отвечать.
11 июн 15, 12:50    [17759672]     Ответить | Цитировать Сообщить модератору
 Re: Индексы + Where Not in  [new]
0-0
Guest
relief
o-o
пропущено...

ok,
a где ответ на вопрос, сколько из этих 100К городов различны?
сколько строк вам возвращает запрос, те же 100К или однy?
если у вас всего 2 различных города в самой таблице,
или в списке из 100 городов вообще нет ни одного из тех, что в таблице,
возвращать все равно надо все.

..a актуальный план бы на все ответил


все значения уникальны
запрос возвращает 90К


Если бы вы искали из 90 тыс. городов 100, то оптимизатор скорее всего выбрал бы index scan, т.к. найти 1/900 по индексам выгоднее, чем сканировать всю таблицу.
В вашем же примере вы фактически выбираете 99% записей таблицы, поэтому логично, что в это случае план запроса будет таким же, как если бы вы сделали select * from из нее же.
11 июн 15, 12:53    [17759706]     Ответить | Цитировать Сообщить модератору
 Re: Индексы + Where Not in  [new]
o-o
Guest
Glory
o-o
потому что когда кричат: такого не бывает, достаточно одного примера, чтоб это развеять.
с припиской, между прочим, корректности ради:

Здесь разбирают конкретный случай ТС, а не ваш.
Так что не надо, как и a_voronin, самому задавать вопрос и самому на него отвечать.

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

и как видите, вам вот очевидно, а ему НЕТ, что если из 100К надо возвратить 90К,
да при индексе, превышающем по размеру саму таблицу,
на индекс никто и не посмотрит
11 июн 15, 12:59    [17759763]     Ответить | Цитировать Сообщить модератору
 Re: Индексы + Where Not in  [new]
Glory
Member

Откуда:
Сообщений: 104751
o-o
и случай никакой не мой, а просто пример того, как может быть использован индекс.

Вы придумали свою структуру и набор данных, которые никак не отражают струкутуру ТС-а. Для того, чтобы потешить свое эго и доказать придуманному оппоненту, что он что-то там "кричал"
11 июн 15, 13:03    [17759785]     Ответить | Цитировать Сообщить модератору
 Re: Индексы + Where Not in  [new]
relief
Member

Откуда:
Сообщений: 1197
0-0
relief
пропущено...


все значения уникальны
запрос возвращает 90К


Если бы вы искали из 90 тыс. городов 100, то оптимизатор скорее всего выбрал бы index scan, т.к. найти 1/900 по индексам выгоднее, чем сканировать всю таблицу.
В вашем же примере вы фактически выбираете 99% записей таблицы, поэтому логично, что в это случае план запроса будет таким же, как если бы вы сделали select * from из нее же.


понял. Спасибо!
11 июн 15, 13:05    [17759802]     Ответить | Цитировать Сообщить модератору
 Re: Индексы + Where Not in  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Вообще, если индекс покрывающий и есть условие поиска, то оптимизатор все-равно выберет Index Seek (который, впрочем, будет по факту Index Seek+Range Scan, где Range Scan - это весь индекс, т.е. одинаково что в плане Index Scan, что Idex Seek.
Вот пример:
+
--drop table Cities
create table Cities 
(
 name nvarchar(100), 
 Population int,
 padding char(100) not null default('')
);
create nonclustered index ix_city on Cities(name) include (population);

with cte as (
select top(100000) rn = row_number() over(order by(select null))%1000 from master..spt_values v1,master..spt_values v2,master..spt_values v3
)
insert Cities(name, population)
select str(rn), rn from cte;


set showplan_text on
go
Select Name, Population From Cities where Name > ''
go
set showplan_text off


  |--Index Seek(OBJECT:([opt].[dbo].[Cities].[ix_city]), SEEK:([opt].[dbo].[Cities].[name] > CONVERT_IMPLICIT(nvarchar(4000),[@1],0)) ORDERED FORWARD)


Это нормально. Если индекс покрывающий и есть SARG условие поиска - то оптимизатор показывает в плане поиск по индексу. Безотносительно селективности, т.к. ему не нужно делать лукап в основную таблицу.

Вы пишете, что этот индекс не используется, а какой используется? Скан кластерного?
По выданным вами условию, ситуация не воспроизводится.
+
--select top(100) ''''+name+''',' from Cities
Select Name, Population 
From Cities 
Where Name NOT IN 
(
'         1',
'         2',
'         3',
'         4',
'         5',
'         6',
'         7',
'         8',
'         9',
'        10',
'        11',
'        12',
'        13',
'        14',
'        15',
'        16',
'        17',
'        18',
'        19',
'        20',
'        21',
'        22',
'        23',
'        24',
'        25',
'        26',
'        27',
'        28',
'        29',
'        30',
'        31',
'        32',
'        33',
'        34',
'        35',
'        36',
'        37',
'        38',
'        39',
'        40',
'        41',
'        42',
'        43',
'        44',
'        45',
'        46',
'        47',
'        48',
'        49',
'        50',
'        51',
'        52',
'        53',
'        54',
'        55',
'        56',
'        57',
'        58',
'        59',
'        60',
'        61',
'        62',
'        63',
'        64',
'        65',
'        66',
'        67',
'        68',
'        69',
'        70',
'        71',
'        72',
'        73',
'        74',
'        75',
'        76',
'        77',
'        78',
'        79',
'        80',
'        81',
'        82',
'        83',
'        84',
'        85',
'        86',
'        87',
'        88',
'        89',
'        90',
'        91',
'        92',
'        93',
'        94',
'        95',
'        96',
'        97',
'        98',
'        99',
'       100'
)



Если вы разобрались уже, то ок. Но если хотите точный ответ на свой вопрос, то нужен действительный план + желательно, структура таблиц.
11 июн 15, 13:19    [17759896]     Ответить | Цитировать Сообщить модератору
 Re: Индексы + Where Not in  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
relief,

Для not in Index Seek в значении "поиск по ключу" невозможен. Зато возможно сканирование диапазонов индекса, а не полное сканирование индекса/таблицы.
Для этого оптимизатор вычисляет x = min(значения в списке not in).
Оценивает два диапазона Ключ индекса < x и Ключ индекса > x.
Если сканирование этих диапазонов выгоднее полного сканирования индекса/таблицы, то в плане вы увидите Index Seek, который на самом деле Range Scan двух диапазонов.
11 июн 15, 14:53    [17760683]     Ответить | Цитировать Сообщить модератору
 Re: Индексы + Where Not in  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Для ускорения запроса с NOT IN() достаточно добавить в список всего один NULL!
11 июн 15, 14:56    [17760717]     Ответить | Цитировать Сообщить модератору
 Re: Индексы + Where Not in  [new]
o-o
Guest
relief
все значения уникальны
запрос возвращает 90К
... Where Name NOT IN (список из 100 городов)...

граждане, а у меня цифры-то не сходятся.
если у нас в таблице значения городов *уникальны*,
мы отбираем из таблицы города, не входящие в *список из 100 городов*
и получаем при этом 90K,
то всего было максимум 90.100 городов (rows), совсем не 100K
--> мозги нам пудрят.

но скорее всего, там в таблице 190 городов,
в списке 100, на выходе 90.
а в плане скан
+
create table Cities 
(
 name nvarchar(100), 
 Population int,
);
create nonclustered index ix_city on Cities(name) include (population);


insert Cities(name, population)
select str(n), n 
from Nums.dbo.nums
where n <= 190;


Select Name, Population 
From Cities 
Where Name NOT IN 
(
'         1',
'         2',
'         3',
'         4',
'         5',
'         6',
'         7',
'         8',
'         9',
'        10',
'        11',
'        12',
'        13',
'        14',
'        15',
'        16',
'        17',
'        18',
'        19',
'        20',
'        21',
'        22',
'        23',
'        24',
'        25',
'        26',
'        27',
'        28',
'        29',
'        30',
'        31',
'        32',
'        33',
'        34',
'        35',
'        36',
'        37',
'        38',
'        39',
'        40',
'        41',
'        42',
'        43',
'        44',
'        45',
'        46',
'        47',
'        48',
'        49',
'        50',
'        51',
'        52',
'        53',
'        54',
'        55',
'        56',
'        57',
'        58',
'        59',
'        60',
'        61',
'        62',
'        63',
'        64',
'        65',
'        66',
'        67',
'        68',
'        69',
'        70',
'        71',
'        72',
'        73',
'        74',
'        75',
'        76',
'        77',
'        78',
'        79',
'        80',
'        81',
'        82',
'        83',
'        84',
'        85',
'        86',
'        87',
'        88',
'        89',
'        90',
'        91',
'        92',
'        93',
'        94',
'        95',
'        96',
'        97',
'        98',
'        99',
'       100'
)
11 июн 15, 15:14    [17760854]     Ответить | Цитировать Сообщить модератору
 Re: Индексы + Where Not in  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
invm
Если сканирование этих диапазонов выгоднее полного сканирования индекса/таблицы, то в плане вы увидите Index Seek, который на самом деле Range Scan двух диапазонов.

Можно пример покрывающего индекса с описанным условием, когда выгоднее Index Scan (показывается Index Scan)?
11 июн 15, 15:14    [17760859]     Ответить | Цитировать Сообщить модератору
 Re: Индексы + Where Not in  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31990
Glory, вы сказали, что индекс никогда не поможет, для IN:
Glory
o-o
to Glory:
почему в другой таблице, у него наверняка списком города перечислены

Тогда тем более индекс не поможет
Потому что это куча OR

o-o показал, что это не так, что и при перечислении в IN индекс может прекрасно использоваться.

Разумеется, для конкретного случая ТС это не так, и нужно разбираться, почему, и как это поправить (или понять, что поправить нельзя)

Зачем тогда так писать:
Glory
o-o
и случай никакой не мой, а просто пример того, как может быть использован индекс.

Вы придумали свою структуру и набор данных, которые никак не отражают струкутуру ТС-а. Для того, чтобы потешить свое эго и доказать придуманному оппоненту, что он что-то там "кричал"
, я вижу какой то спор ради спора.
11 июн 15, 15:38    [17761008]     Ответить | Цитировать Сообщить модератору
 Re: Индексы + Where Not in  [new]
o-o
Guest
SomewhereSomehow
invm
Если сканирование этих диапазонов выгоднее полного сканирования индекса/таблицы, то в плане вы увидите Index Seek, который на самом деле Range Scan двух диапазонов.

Можно пример покрывающего индекса с описанным условием, когда выгоднее Index Scan (показывается Index Scan)?

конечно можно
это и был мой случай.
в примере, сделанном "под заказ",
неизменно выбирался INDEX SEEK,
причем, хоть там в результате RANGE SKAN, это совсем не полный скан индекса,
потому что вот моя статистика и вот реальное число страниц в индексе:
(хотите отгадать, что внутри таблицы, или сразу сказать?
вы будете разочарованы )

К сообщению приложен файл. Размер - 109Kb
11 июн 15, 15:39    [17761019]     Ответить | Цитировать Сообщить модератору
 Re: Индексы + Where Not in  [new]
Glory
Member

Откуда:
Сообщений: 104751
alexeyvg
Glory, вы сказали, что индекс никогда не поможет, для IN:

Где я сказал "никогда" ?

alexeyvg
, я вижу какой то спор ради спора.

Потому что вы и о-о придумали слово "никогда" и бросились доказывать, что "когда"
11 июн 15, 15:41    [17761032]     Ответить | Цитировать Сообщить модератору
 Re: Индексы + Where Not in  [new]
o-o
Guest
alexeyvg
o-o показал, что это не так, что и при перечислении в IN индекс может прекрасно использоваться.

не-не-не, все еще трагичнее, на самом деле прав SomewhereSomehow, покрывающий индекс при условии not in будет выбран вообще всегда
что меня даже возмущает, сейчас расскажу, почему.
у меня есть пример, когда именно с типами данных ТС, при достаточном числе строк,
индекс начинает чуть не в 2 раза превышать исходную таблицу размером, но УПОРНО выбирается оптимизатором,
и именно что не зависит от селективности.
т.е. уж лучше исходную таблицу просканировать, но нет, он лезет в индекс и в результате в 2 раза больше читает
11 июн 15, 15:47    [17761076]     Ответить | Цитировать Сообщить модератору
 Re: Индексы + Where Not in  [new]
0-0
Guest
o-o
т.е. уж лучше исходную таблицу просканировать, но нет, он лезет в индекс и в результате в 2 раза больше читает


Это на какой версии сервера так происходит, с каким compability level?
11 июн 15, 15:50    [17761105]     Ответить | Цитировать Сообщить модератору
 Re: Индексы + Where Not in  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
SomewhereSomehow
Можно пример покрывающего индекса с описанным условием, когда выгоднее Index Scan (показывается Index Scan)?
use tempdb;
go

create table dbo.t (id int identity primary key, v int, dummy char(10) null default 'a');

insert into dbo.t (v) select top (100000) 1 from master.dbo.spt_values a cross apply master.dbo.spt_values b;
insert into dbo.t (v) values (2), (2);

create index IX_t__v on dbo.t(v) include (dummy);
go

set statistics profile on;
go

select count(dummy) from dbo.t where v not in (1, 3, 4, 5);
select count(dummy) from dbo.t where v not in (0, 1, 3, 4, 5);
select count(dummy) from dbo.t with (forceseek) where v not in (0, 1, 3, 4, 5);
go

set statistics profile off;
go

drop table dbo.t;
+ Планы
select count(dummy) from dbo.t where v not in (1, 3, 4, 5)
|--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1005],0)))
|--Stream Aggregate(DEFINE:([Expr1005]=COUNT([tempdb].[dbo].[t].[dummy])))
|--Index Seek(OBJECT:([tempdb].[dbo].[t].[IX_t__v]), SEEK:([tempdb].[dbo].[t].[v] < (1) OR [tempdb].[dbo].[t].[v] > (1)), WHERE:(([tempdb].[dbo].[t].[v]<(3) OR [tempdb].[dbo].[t].[v]>(3)) AND ([tempdb].[dbo].[t].[v]<(4) OR [tempdb].[dbo].[t].[v]>(4)) AND ([tempdb].[dbo].[t].[v]<(5) OR [tempdb].[dbo].[t].[v]>(5))) ORDERED FORWARD)

select count(dummy) from dbo.t where v not in (0, 1, 3, 4, 5)
|--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1005],0)))
|--Stream Aggregate(DEFINE:([Expr1005]=COUNT([tempdb].[dbo].[t].[dummy])))
|--Index Scan(OBJECT:([tempdb].[dbo].[t].[IX_t__v]), WHERE:([tempdb].[dbo].[t].[v]<>(0) AND [tempdb].[dbo].[t].[v]<>(1) AND [tempdb].[dbo].[t].[v]<>(3) AND [tempdb].[dbo].[t].[v]<>(4) AND [tempdb].[dbo].[t].[v]<>(5)))

select count(dummy) from dbo.t with (forceseek) where v not in (0, 1, 3, 4, 5)
|--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1005],0)))
|--Stream Aggregate(DEFINE:([Expr1005]=COUNT([tempdb].[dbo].[t].[dummy])))
|--Index Seek(OBJECT:([tempdb].[dbo].[t].[IX_t__v]), SEEK:([tempdb].[dbo].[t].[v] < (0) OR [tempdb].[dbo].[t].[v] > (0)), WHERE:(([tempdb].[dbo].[t].[v]<(1) OR [tempdb].[dbo].[t].[v]>(1)) AND ([tempdb].[dbo].[t].[v]<(3) OR [tempdb].[dbo].[t].[v]>(3)) AND ([tempdb].[dbo].[t].[v]<(4) OR [tempdb].[dbo].[t].[v]>(4)) AND ([tempdb].[dbo].[t].[v]<(5) OR [tempdb].[dbo].[t].[v]>(5))) ORDERED FORWARD)
11 июн 15, 15:56    [17761148]     Ответить | Цитировать Сообщить модератору
 Re: Индексы + Where Not in  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
invm,

Спасибо, принимается.
Сделаю себе поправочку для неравенств.
11 июн 15, 16:16    [17761277]     Ответить | Цитировать Сообщить модератору
 Re: Индексы + Where Not in  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
o-o
не-не-не, все еще трагичнее, на самом деле прав SomewhereSomehow, покрывающий индекс при условии not in

Я не точно выразился и попросил неправильное репро =)

То, что я описал
Это нормально. Если индекс покрывающий и есть SARG условие поиска - то оптимизатор показывает в плане поиск по индексу. Безотносительно селективности, т.к. ему не нужно делать лукап в основную таблицу.

Работает для SARG условие поиска. Если посмотреть на пример invm, то там (даже в плане с поиском) есть еще свойство Predicate. Из-за которого есть выбор. А появляется оно, т.к. оптимизатор делает манипуляции с неравенством. Скажем так. Чтобы я оказался прав, нужно было попросить план где есть только поиск по предикату Seek Predicate. Но формально invm прав =) Клевый нюанс, обожаю форум за такие вещи, записал репро себе в копилку =)
11 июн 15, 16:27    [17761364]     Ответить | Цитировать Сообщить модератору
 Re: Индексы + Where Not in  [new]
o-o
Guest
0-0
o-o
т.е. уж лучше исходную таблицу просканировать, но нет, он лезет в индекс и в результате в 2 раза больше читает


Это на какой версии сервера так происходит, с каким compability level?

да все стандартно,
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (Intel X86) Aug 19 2014 12:21:07 Copyright (c) Microsoft Corporation Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)

compability level = 100.

ничего особо делать не надо, запустить скрипт SomewhereSomehow, навалив в 10 раз больше строк.

он же ведь и объяснил, что так будет,
ну а довести ситуацию до абсурда, т.е. нарастить так таблицу, чтоб индекс ее переплюнул в размерах, моя идея.
т.е. это было в порядке проверки слов SomewhereSomehow, при слегка изменившихся объемах

К сообщению приложен файл. Размер - 68Kb
11 июн 15, 16:31    [17761388]     Ответить | Цитировать Сообщить модератору
 Re: Индексы + Where Not in  [new]
o-o
Guest
SomewhereSomehow
o-o
не-не-не, все еще трагичнее, на самом деле прав SomewhereSomehow, покрывающий индекс при условии not in

Я не точно выразился и попросил неправильное репро =)

То, что я описал
Это нормально. Если индекс покрывающий и есть SARG условие поиска - то оптимизатор показывает в плане поиск по индексу. Безотносительно селективности, т.к. ему не нужно делать лукап в основную таблицу.


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

понятно, что лукап ему не нужен, но если исходная таблица МЕНьШЕ, зачем лезть в то, что БОЛьШЕ,
чтобы все равно сканить?
11 июн 15, 16:35    [17761414]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить