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

Откуда:
Сообщений: 1197
Привет

Есть таблица Продажи

в ней есть поля:

SaleDate, ShopCode, Amount, Qty, IsDeleted


запросы почти всегда строятся такого вида

select sum(amount), sum(qty), SaleDate, ShopCode
From sales where saledate between @s1 and @s2 and shopcode in(...)
and IsDeleted = 0


изначально был некластерный индекс по полям saledate и ShopCode. Также было IncludeColumns по Amount, Qty
Сейчас встал вопрос показывать только неудаленные записи.
после того как добавил and IsDeleted = 0 скорость сильно упала. т.к. общее число удаленных продаж не более 3% от всех записей.

Вопрос: как ускоритьс скорость, отдельный индекс по полю IsDeleted делать?
15 июл 13, 15:05    [14568037]     Ответить | Цитировать Сообщить модератору
 Re: Нужен совет по построению индекса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Добавьте IsDeleted в ключ индекса.
15 июл 13, 15:07    [14568049]     Ответить | Цитировать Сообщить модератору
 Re: Нужен совет по построению индекса  [new]
relief
Member

Откуда:
Сообщений: 1197
Гавриленко Сергей Алексеевич
Добавьте IsDeleted в ключ индекса.


у меня запросы во всех процедурах делаюстя на view, и чтобы не писать во всех процедурах условие isDeleted = 0 я его добавил просто во view.
к чему я это написал. будет нормально если поле isDeleted в индексе будет идти после полей SaleDate, ShopCode, но при этом получается будет применятсья раньше (на уровне view - до вызова view из процедуры)?
15 июл 13, 15:14    [14568106]     Ответить | Цитировать Сообщить модератору
 Re: Нужен совет по построению индекса  [new]
create index
Guest
relief



1. вариант
IsDeleted, ShopCode, saledate include (amount, qty)

2. вариант
IsDeleted, saledate include (amount, qty, ShopCode)


зависит от количества элементов за in(...)
15 июл 13, 15:19    [14568155]     Ответить | Цитировать Сообщить модератору
 Re: Нужен совет по построению индекса  [new]
baracs
Member

Откуда: Москва
Сообщений: 7219
create index
1. вариант
IsDeleted, ShopCode, saledate include (amount, qty)

2. вариант
IsDeleted, saledate include (amount, qty, ShopCode)
Зачем IsDeleted ставить первым, если
relief
общее число удаленных продаж не более 3% от всех записей.
а ищут IsDeleted = 0 (как я понимаю неудаленные)?
15 июл 13, 15:29    [14568260]     Ответить | Цитировать Сообщить модератору
 Re: Нужен совет по построению индекса  [new]
create index
Guest
baracs
create index
1. вариант
IsDeleted, ShopCode, saledate include (amount, qty)

2. вариант
IsDeleted, saledate include (amount, qty, ShopCode)
Зачем IsDeleted ставить первым, если
relief
общее число удаленных продаж не более 3% от всех записей.
а ищут IsDeleted = 0 (как я понимаю неудаленные)?

и что?
15 июл 13, 15:31    [14568275]     Ответить | Цитировать Сообщить модератору
 Re: Нужен совет по построению индекса  [new]
baracs
Member

Откуда: Москва
Сообщений: 7219
create index
baracs
пропущено...
Зачем IsDeleted ставить первым, если пропущено...
а ищут IsDeleted = 0 (как я понимаю неудаленные)?

и что?
Зачем IsDeleted ставить первым?
15 июл 13, 15:39    [14568367]     Ответить | Цитировать Сообщить модератору
 Re: Нужен совет по построению индекса  [new]
create index
Guest
baracs
create index
пропущено...

и что?
Зачем IsDeleted ставить первым?

ну давайте разбираться

начнем со 2-го варианта
IsDeleted, saledate include (amount, qty, ShopCode)

если IsDeleted поставить на второе место - то в запросе автора это поле "уйдет" из seek предиката.


теперь с 1-ым вариантом
IsDeleted, ShopCode, saledate include (amount, qty)

ставить IsDeleted после saledate нельзя по причине описанной выше

для запроса автора порядок первых двух полей(IsDeleted, ShopCode ) неважен, при этом смотрим что
ТС
у меня запросы во всех процедурах делаюстя на view, и чтобы не писать во всех процедурах условие isDeleted = 0 я его добавил просто во view.

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



ЗЫ
в принципе IsDeleted = 0 можно вынести в where секцию индекса
15 июл 13, 15:46    [14568427]     Ответить | Цитировать Сообщить модератору
 Re: Нужен совет по построению индекса  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35871
Блог
select sum(amount), sum(qty), SaleDate, ShopCode
From sales where saledate between @s1 and @s2 and shopcode in(...)
and IsDeleted = 0



такой попробуйте
saledate, shopcode include (amount, qty) where IsDeleted = 0
или скорее такой
shopcode, saledate include (amount, qty) where IsDeleted = 0
15 июл 13, 15:56    [14568510]     Ответить | Цитировать Сообщить модератору
 Re: Нужен совет по построению индекса  [new]
relief
Member

Откуда:
Сообщений: 1197
Критик
select sum(amount), sum(qty), SaleDate, ShopCode
From sales where saledate between @s1 and @s2 and shopcode in(...)
and IsDeleted = 0



такой попробуйте
saledate, shopcode include (amount, qty) where IsDeleted = 0
или скорее такой
shopcode, saledate include (amount, qty) where IsDeleted = 0


а почему скорей второй вариант?

одно уточнение еще.
на вход всех процедур поступает параметр @Stores NVARCHAR(MAX) который потом успешно парсится во временную таблицу #stores (storeCode) и которая потом джойнится на view.
Но насколько я понимаю это роли не играет на порядок SaleDate, StoreCode
15 июл 13, 16:13    [14568653]     Ответить | Цитировать Сообщить модератору
 Re: Нужен совет по построению индекса  [new]
baracs
Member

Откуда: Москва
Сообщений: 7219
create index
baracs
пропущено...
Зачем IsDeleted ставить первым?
если IsDeleted поставить на второе место - то в запросе автора это поле "уйдет" из seek предиката.
Ну и бог с ним. Толку-то от этого Seek, если он найдет 97% строк...

У автора просела производительность после добавления условия IsDeleted = 0, т.к. IsDeleted никак не присутствует в индексе и серверу приходится делать Lookup.
Полегчает, даже если засунуть IsDeleted в Include.
create index
для запроса автора порядок первых двух полей(IsDeleted, ShopCode ) неважен,
Важно, что у него первой идет дата и, это правильно. Количество магазинов конечно и, скорее всего, невелико. Поэтому наиболее селективным полем будет дата продажи. Причем, по мере накопления данных, поиск по дате будет все эффективнее.
create index
при этом смотрим что
ТС
у меня запросы во всех процедурах делаюстя на view, и чтобы не писать во всех процедурах условие isDeleted = 0 я его добавил просто во view.
т.е. условие по IsDeleted есть всегда, и этот индекс может быть использован для других запросов.
Но
relief
запросы почти всегда строятся такого вида
select sum(amount), sum(qty), SaleDate, ShopCode
From sales where saledate between @s1 and @s2 and shopcode in(...)
and IsDeleted = 0
Т.е. по дате поиск тоже - почти всегда.

Я за: SaleDate, ShopCode, IsDeleted INCLUDE (amount, qty).
15 июл 13, 16:27    [14568778]     Ответить | Цитировать Сообщить модератору
 Re: Нужен совет по построению индекса  [new]
create index
Guest
baracs
Я за: SaleDate, ShopCode, IsDeleted INCLUDE (amount, qty).



В запросе автора последние два поля из индекса не будут в seek предикате, по ним не будет поиска (как и при любом другом запросе с условием большем/меньше по полю SaleDate), а следовательно предложенный вами индекс - ужасен.
Если вы хотите оставить SaleDate на первом месте - то переносите ShopCode, IsDeleted в INCLUDE секцию
15 июл 13, 16:33    [14568830]     Ответить | Цитировать Сообщить модератору
 Re: Нужен совет по построению индекса  [new]
create index
Guest
relief
Но насколько я понимаю это роли не играет на порядок SaleDate, StoreCode

влияет, и очень сильно
у вас неравенство (between) по SaleDate, а значит все поля в индексе за ним не будут использоваться в seek предикате (т.е. бесполезны в ключе индекса).
15 июл 13, 16:36    [14568858]     Ответить | Цитировать Сообщить модератору
 Re: Нужен совет по построению индекса  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34709
relief

изначально был некластерный индекс по полям saledate и ShopCode. Также было IncludeColumns по Amount, Qty


Вопрос: как ускоритьс скорость, отдельный индекс по полю IsDeleted делать?



НЕ делай, оставь как было, (saledate,ShopCode)
15 июл 13, 16:46    [14568956]     Ответить | Цитировать Сообщить модератору
 Re: Нужен совет по построению индекса  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
create index
relief
Но насколько я понимаю это роли не играет на порядок SaleDate, StoreCode

влияет, и очень сильно
у вас неравенство (between) по SaleDate, а значит все поля в индексе за ним не будут использоваться в seek предикате (т.е. бесполезны в ключе индекса).


не то чтобы совсем бесполезны, там же group by автор потерял
15 июл 13, 16:48    [14568988]     Ответить | Цитировать Сообщить модератору
 Re: Нужен совет по построению индекса  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34709
baracs
create index
пропущено...

и что?
Зачем IsDeleted ставить первым?


IsDeleted ни добавлять в индекс, ни ставить первым полем в него нет ссмысла.
15 июл 13, 16:49    [14569007]     Ответить | Цитировать Сообщить модератору
 Re: Нужен совет по построению индекса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
MasterZiv
baracs
пропущено...
Зачем IsDeleted ставить первым?


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

Сообщение было отредактировано: 15 июл 13, 17:08
15 июл 13, 16:52    [14569037]     Ответить | Цитировать Сообщить модератору
 Re: Нужен совет по построению индекса  [new]
create index
Guest
MasterZiv
baracs
пропущено...
Зачем IsDeleted ставить первым?


IsDeleted ни добавлять в индекс, ни ставить первым полем в него нет ссмысла.

вы конечно это можете подтвердить это тестом, да?

потестируйте на досуге 4 варианта индекса для запроса автора

1. saledate, ShopCode. IncludeColumns(Amount, Qty) текущий (и вами отстаиваемый)
2. ShopCode, saledate. IncludeColumns(Amount, Qty, IsDeleted)
3. IsDeleted, ShopCode, saledate. IncludeColumns(Amount, Qty)
4. IsDeleted, saledate. IncludeColumns(Amount, Qty, ShopCode)

для разных количеств ShopCode под in (...)
15 июл 13, 16:56    [14569084]     Ответить | Цитировать Сообщить модератору
 Re: Нужен совет по построению индекса  [new]
NickAlex66
Member

Откуда:
Сообщений: 319
relief

Гадание бесполезное занятие! У вас все данные и статистика. Стройте индекс и см план выполнения запроса.
15 июл 13, 17:46    [14569509]     Ответить | Цитировать Сообщить модератору
 Re: Нужен совет по построению индекса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32174
MasterZiv
baracs
пропущено...
Зачем IsDeleted ставить первым?


IsDeleted ни добавлять в индекс, ни ставить первым полем в него нет ссмысла.
Имеет смысл хотя бы потому, что бы не было лукапов. Остальные-то нужные поля у индекса в include.

Т.е., если IsDeleted будет в условиях всегда, то нужно либо добавить его первым полем в инедксе, либо сделать фильтрованный инедкс (если условие всегда IsDeleted=0)

А если IsDeleted будет в условиях НЕ всегда, то нужно его добавить в INCLUDE
15 июл 13, 18:43    [14569784]     Ответить | Цитировать Сообщить модератору
 Re: Нужен совет по построению индекса  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34709
alexeyvg
Имеет смысл хотя бы потому, что бы не было лукапов. Остальные-то нужные поля у индекса в include.

Т.е., если IsDeleted будет в условиях всегда, то нужно либо добавить его первым полем в инедксе, либо сделать фильтрованный инедкс (если условие всегда IsDeleted=0)

А если IsDeleted будет в условиях НЕ всегда, то нужно его добавить в INCLUDE


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

Не всё нужно тестировать на практике, чтобы понять, что это глупость.

Как сообщил нам топикстартер, условие IsDeleted = 0 используется для отсева 3% удалённых записей.
При этом для остальных 97% записей размер индексной записи увеличится на размер одного флага, на 1 байт.

На счёт INCLUDE ... Ну, давай, запихивай вообще в каждый индекс таблицы все неключевые поля.
Ты так делаешь на практике? Уверен, нет.
Покрывающий индекс -- не самоцель, на него место тратится.
15 июл 13, 20:02    [14570026]     Ответить | Цитировать Сообщить модератору
 Re: Нужен совет по построению индекса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
MasterZiv
Индекс покрывающим не будет, в запросе ещё другие поля используются.
Покрывающий индекс, это такой индекс в составе которого присутствуют все необходимые данные и не надо ничего больше лукапить.

MasterZiv
При этом для остальных 97% записей размер индексной записи увеличится на размер одного флага, на 1 байт.
А если индекс не увеличивать, то при выполнении каждого запроса для каждой записи надо делать лукап или в кластерный индекс или в кучу. Индексы как бы и нужны, чтобы сокращать объем чтений с диска за счет места.
15 июл 13, 20:09    [14570050]     Ответить | Цитировать Сообщить модератору
 Re: Нужен совет по построению индекса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32174
MasterZiv
alexeyvg
Имеет смысл хотя бы потому, что бы не было лукапов. Остальные-то нужные поля у индекса в include.

Т.е., если IsDeleted будет в условиях всегда, то нужно либо добавить его первым полем в инедксе, либо сделать фильтрованный инедкс (если условие всегда IsDeleted=0)

А если IsDeleted будет в условиях НЕ всегда, то нужно его добавить в INCLUDE


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

saledate, ShopCode, IncludeColumns, Amount, Qty
из них первые 2 в условиях.


MasterZiv
Как сообщил нам топикстартер, условие IsDeleted = 0 используется для отсева 3% удалённых записей.
При этом для остальных 97% записей размер индексной записи увеличится на размер одного флага, на 1 байт.
Да хоть 99.97%

Если использование поля есть, то придётся лезть в таблицу, а так можно обойтись одним индексом.
MasterZiv
На счёт INCLUDE ... Ну, давай, запихивай вообще в каждый индекс таблицы все неключевые поля.
Ты так делаешь на практике? Уверен, нет.
Покрывающий индекс -- не самоцель, на него место тратится.
Делаю, если нужно, разные же бывают запросы и данные, иногда такое может ускорить многочасовой отчёт в разы.

Понятно, что не самоцель, но тонкость тут в том, что этот индекс нужен один, поскольку у ТС задача оптимизировать самый массовый запрос (как я понял по его словам). Так что добавится всего лишь лишний 1 бит на строку интекса, что в общем немного, для исключения лукапа не задумываясь бы сделал.
15 июл 13, 20:26    [14570100]     Ответить | Цитировать Сообщить модератору
 Re: Нужен совет по построению индекса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32174
MasterZiv
На счёт INCLUDE ... Ну, давай, запихивай вообще в каждый индекс таблицы все неключевые поля.
Ты так делаешь на практике? Уверен, нет.
У меня для многих небольшх таблиц индексы намного больше самих данных. ИМХО это нормально, если обновлений достаточно немного, а таблички маленькие (но недостаточно маленькие, что бы вообще забить на индексы).
15 июл 13, 20:28    [14570108]     Ответить | Цитировать Сообщить модератору
 Re: Нужен совет по построению индекса  [new]
qwetyfquwey
Guest
create index
MasterZiv
пропущено...


IsDeleted ни добавлять в индекс, ни ставить первым полем в него нет ссмысла.

вы конечно это можете подтвердить это тестом, да?

потестируйте на досуге 4 варианта индекса для запроса автора

1. saledate, ShopCode. IncludeColumns(Amount, Qty) текущий (и вами отстаиваемый)
2. ShopCode, saledate. IncludeColumns(Amount, Qty, IsDeleted)
3. IsDeleted, ShopCode, saledate. IncludeColumns(Amount, Qty)
4. IsDeleted, saledate. IncludeColumns(Amount, Qty, ShopCode)

для разных количеств ShopCode под in (...)


+ заливка 10 000 000 записей

create table t1(SaleDate datetime, ShopCode int, Amount money, Qty int, IsDeleted bit);
go

declare @ShopCodeCnt int = 10;
declare @TranCnt int = 1000000;
declare @StartTime datetime = '19990101';


;with
l0(n) as (select 1 union all select 1),
l1(n) as (select 1 from l0 t1, l0 t2),
l2(n) as (select 1 from l1 t1, l1 t2),
l3(n) as (select 1 from l2 t1, l2 t2),
l4(n) as (select 1 from l3 t1, l3 t2),
l5(n) as (select 1 from l4 t1, l4 t2),
rn(n) as (select row_number() over(order by (select 0)) from l5)

insert into t1(SaleDate, ShopCode, Amount, Qty, IsDeleted)
select sd.SaleDate, sc.ShopCode, rand(checksum(newid())) * 1000 as Amount, rand(checksum(newid())) * 1000 as Qty, case when rand(checksum(newid())) <= 0.97 then 0 else 1 end as IsDeleted
from
(
	select top(@ShopCodeCnt) n as ShopCode
	from rn
) sc
cross join
(
	select top(@TranCnt) dateadd(s, n - 1, @StartTime) as SaleDate
	from rn
) sd



+ тест io

drop index t1.idx_tst;
create nonclustered index idx_tst on t1(saledate, ShopCode) include(Amount, Qty)

set statistics io on
print '(saledate, ShopCode) include(Amount, Qty)'
print '====================================================='
select sum(amount), sum(qty), ShopCode
From t1 where saledate between cast('19990101'as datetime) and cast('20120101'as datetime) and shopcode in(2, 5, 8, 1, 3, 4)
and IsDeleted = 0
group by shopcode
print '====================================================='
set statistics io off


drop index t1.idx_tst;
create nonclustered index idx_tst on t1(ShopCode, saledate) include(Amount, Qty, IsDeleted)

set statistics io on
print '(ShopCode, saledate) include(Amount, Qty, IsDeleted)'
print '====================================================='
select sum(amount), sum(qty), ShopCode
From t1 where saledate between cast('19990101'as datetime) and cast('20120101'as datetime) and shopcode in(2, 5, 8, 1, 3, 4)
and IsDeleted = 0
group by shopcode
print '====================================================='
set statistics io off

drop index t1.idx_tst;
create nonclustered index idx_tst on t1(IsDeleted, ShopCode, saledate) include(Amount, Qty)

set statistics io on
print '(IsDeleted, ShopCode, saledate) include(Amount, Qty)'
print '====================================================='
select sum(amount), sum(qty), ShopCode
From t1 where saledate between cast('19990101'as datetime) and cast('20120101'as datetime) and shopcode in(2, 5, 8, 1, 3, 4)
and IsDeleted = 0
group by shopcode
print '====================================================='
set statistics io off

drop index t1.idx_tst;
create nonclustered index idx_tst on t1(IsDeleted, saledate) include(Amount, Qty, ShopCode)

set statistics io on
print '(IsDeleted, saledate) include(Amount, Qty, ShopCode)'
print '====================================================='
select sum(amount), sum(qty), ShopCode
From t1 where saledate between cast('19990101'as datetime) and cast('20120101'as datetime) and shopcode in(2, 5, 8, 1, 3, 4)
and IsDeleted = 0
group by shopcode
print '====================================================='
set statistics io off



=====================================================
(saledate, ShopCode) include(Amount, Qty)
=====================================================

(6 row(s) affected)
Таблица "Worktable". Число просмотров 0, логических чтений 0, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "t1". Число просмотров 3, логических чтений 42017, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
=====================================================
(ShopCode, saledate) include(Amount, Qty, IsDeleted)
=====================================================

(6 row(s) affected)
Таблица "t1". Число просмотров 18, логических чтений 29441, физических чтений 0, упреждающих чтений 7, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
=====================================================
(IsDeleted, ShopCode, saledate) include(Amount, Qty)
=====================================================

(6 row(s) affected)
Таблица "t1". Число просмотров 15, логических чтений 28604, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
=====================================================
(IsDeleted, saledate) include(Amount, Qty, ShopCode)
=====================================================

(6 row(s) affected)
Таблица "Worktable". Число просмотров 0, логических чтений 0, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "t1". Число просмотров 3, логических чтений 47289, физических чтений 0, упреждающих чтений 1, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
=====================================================
15 июл 13, 21:28    [14570282]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить