Нужен совет по построению индекса / Microsoft SQL Server

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Нужен совет по построению индекса

10 сообщений из 35, страница 2 из 2

все

Нужен совет по построению индекса

#38331992

rgwergwerg

Гость

Код: sql

1.
2.
3.
4.
5.
6.

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

(6 row(s) affected)
Таблица "t1". Число просмотров 16, логических чтений 29457, физических чтений 0, упреждающих чтений 4, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
=====================================================

до кучи

...

Рейтинг:

0 / 0

15.07.2013, 21:36

| Ответить | Цитировать | Написать

Нужен совет по построению индекса

#38331996

erghwetgwer

Гость

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

(ShopCode, saledate) 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)  where IsDeleted = 0
=====================================================

(6 row(s) affected)
Таблица "t1". Число просмотров 16, логических чтений 27784, физических чтений 0, упреждающих чтений 5, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
=====================================================

Такой вариант не плох (ShopCode, saledate) include(Amount, Qty) where IsDeleted = 0

до кучи

...

Рейтинг:

0 / 0

15.07.2013, 21:46

| Ответить | Цитировать | Написать

Нужен совет по построению индекса

#38332017

ья

Гость

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

Понятно, что не самоцель, но тонкость тут в том, что этот индекс нужен один, поскольку у ТС задача оптимизировать самый массовый запрос (как я понял по его словам). Так что добавится всего лишь лишний 1 бит на строку интекса, что в общем немного, для исключения лукапа не задумываясь бы сделал.

Один БАЙТ.

...

Рейтинг:

0 / 0

15.07.2013, 22:46

| Ответить | Цитировать | Написать

Нужен совет по построению индекса

#38332030

MasterZiv

Участник

Откуда: Питер

Сообщения: 32 427

Рейтинг: 0 / 0

qwetyfquwey,

Про тест.

0) Запрос у него был другой. Group by по дате ещё был.
1) диапазон по дате может какой-то реальный лучше взять, вряд ли товарищь за 12 лет данные брал.

...

Рейтинг:

0 / 0

15.07.2013, 23:11

| Ответить | Цитировать | Написать

Нужен совет по построению индекса

#38332033

fghnfghfghnf

Гость

MasterZivqwetyfquwey,

Про тест.

0) Запрос у него был другой. Group by по дате ещё был.
1) диапазон по дате может какой-то реальный лучше взять, вряд ли товарищь за 12 лет данные брал.

по 0. все равно этот индекс (ShopCode, saledate) include(Amount, Qty) where IsDeleted = 0 и в случае goup by shopcode, saledate выиграет.

1 лень, автор как партизан молчит... :)

...

Рейтинг:

0 / 0

15.07.2013, 23:18

| Ответить | Цитировать | Написать

Нужен совет по построению индекса

#38332075

invm

Участник

Откуда: Москва

Сообщения: 9 956

Рейтинг: 0 / 0

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

create view dbo.v_sale
with schemabinding
as
select
 SaleDate, ShopCode, sum(amount) as AmountSum, sum(qty) as QtySum, count_big(*) as cnt
from
 dbo.sale
where
 IsDeleted = 0
group by
 SaleDate, ShopCode;
go

create unique clustered index IXUQ_v_sale on dbo.v_sale (ShopCode, SaleDate);

Код: sql

1.
2.

select AmountSum, QtySum, SaleDate, ShopCode
From v_sales with (noexpand) where saledate between @s1 and @s2 and shopcode in(...);

...

Рейтинг:

0 / 0

16.07.2013, 00:33

| Ответить | Цитировать | Написать

Нужен совет по построению индекса

#38332229

alexeyvg

Участник

Откуда: Moscow

Сообщения: 26 926

Рейтинг: 0 / 0

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

Понятно, что не самоцель, но тонкость тут в том, что этот индекс нужен один, поскольку у ТС задача оптимизировать самый массовый запрос (как я понял по его словам). Так что добавится всего лишь лишний 1 бит на строку интекса, что в общем немного, для исключения лукапа не задумываясь бы сделал.

Один БАЙТ.Ну да, ошибся, один байт конечно.

...

Рейтинг:

0 / 0

16.07.2013, 09:18

| Ответить | Цитировать | Написать

Нужен совет по построению индекса

#38332658

create index

Гость

fghnfghfghnfMasterZivqwetyfquwey,

Про тест.

0) Запрос у него был другой. Group by по дате ещё был.
1) диапазон по дате может какой-то реальный лучше взять, вряд ли товарищь за 12 лет данные брал.

по 0. все равно этот индекс (ShopCode, saledate) include(Amount, Qty) where IsDeleted = 0 и в случае goup by shopcode, saledate выиграет.

1 лень, автор как партизан молчит... :)

взял на себя смелость сделать ваш тест чуть ближе к условиям автора, а именно
1. добавил группировку по saledate
2. уж если есть группировка по saledate, я думаю там храниться именно дата, соответственно округли его
3. по дате размазал данные чуть больше чем на год, и ограничил выборку одним месяцем
4. добавил статистику выполнения по времени

итого получилось

данные

Код: sql

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.

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 = '20110101'; 


;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) cast(dateadd(mi, n - 1, @StartTime) as DATE) as SaleDate 
	from rn
) sd

тест

Код: sql

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.

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

set statistics io on
set statistics time on
print '(saledate, ShopCode) include(Amount, Qty)'
print '====================================================='
select sum(amount), sum(qty), ShopCode, SaleDate
From t1 where saledate between cast('20111201'as datetime) and cast('20120101'as datetime) and shopcode in(2, 5, 8, 1, 3, 4)
and IsDeleted = 0
group by shopcode, SaleDate
print '====================================================='
set statistics time off
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
set statistics time on
print '(ShopCode, saledate) include(Amount, Qty, IsDeleted)'
print '====================================================='
select sum(amount), sum(qty), ShopCode, SaleDate
From t1 where saledate between cast('20111201'as datetime) and cast('20120101'as datetime) and shopcode in(2, 5, 8, 1, 3, 4)
and IsDeleted = 0
group by shopcode, SaleDate
print '====================================================='
set statistics time off
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
set statistics time on
print '(IsDeleted, ShopCode, saledate) include(Amount, Qty)'
print '====================================================='
select sum(amount), sum(qty), ShopCode, SaleDate
From t1 where saledate between cast('20111201'as datetime) and cast('20120101'as datetime) and shopcode in(2, 5, 8, 1, 3, 4)
and IsDeleted = 0
group by shopcode, SaleDate
print '====================================================='
set statistics time off
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
set statistics time on
print '(IsDeleted, saledate) include(Amount, Qty, ShopCode)'
print '====================================================='
select sum(amount), sum(qty), ShopCode, SaleDate
From t1 where saledate between cast('20111201'as datetime) and cast('20120101'as datetime) and shopcode in(2, 5, 8, 1, 3, 4)
and IsDeleted = 0
group by shopcode, SaleDate
print '====================================================='
set statistics time off
set statistics io off


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

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

все результаты

Код: plaintext

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.

(saledate, ShopCode) include(Amount, Qty)
=====================================================
(192 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 't1'. Scan count 9, logical reads 42017, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1747 ms,  elapsed time = 240 ms.
=====================================================

(ShopCode, saledate) include(Amount, Qty, IsDeleted)
=====================================================
(192 row(s) affected)
Table 't1'. Scan count 6, logical reads 1368, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 203 ms,  elapsed time = 201 ms.
=====================================================

(IsDeleted, ShopCode, saledate) include(Amount, Qty)
=====================================================
(192 row(s) affected)
Table 't1'. Scan count 6, logical reads 1324, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 110 ms,  elapsed time = 99 ms.
=====================================================

(IsDeleted, saledate) include(Amount, Qty, ShopCode)
=====================================================
(192 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 't1'. Scan count 1, logical reads 2171, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 141 ms,  elapsed time = 135 ms.
=====================================================

(ShopCode, saledate) include(Amount, Qty)  where IsDeleted = 0
=====================================================
(192 row(s) affected)
Table 't1'. Scan count 6, logical reads 1290, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 202 ms,  elapsed time = 199 ms.
=====================================================

в данном варианте изначальный вариант просто убог

Код: plaintext

1.
2.
3.
4.
5.
6.

(saledate, ShopCode) include(Amount, Qty)

Scan count 9, logical reads 42017

 SQL Server Execution Times:
   CPU time = 1747 ms,  elapsed time = 240 ms.

против например

Код: plaintext

1.
2.
3.
4.

(IsDeleted, ShopCode, saledate) include(Amount, Qty)
Scan count 6, logical reads 1324
 SQL Server Execution Times:
   CPU time = 110 ms,  elapsed time = 99 ms.

вариант

Код: plaintext

1.
2.
3.
4.

(ShopCode, saledate) include(Amount, Qty)  where IsDeleted = 0
Scan count 6, logical reads 1290
 SQL Server Execution Times:
   CPU time = 202 ms,  elapsed time = 199 ms.

дает еще меньше чтение (что объяснимо, IsDeleted = 0 хранится только на уровне метаданных индекса), но не намного , но почему-то в два раз больше съедает процессорного времени, и как следствие выполняется в два раза дольше.

...

Рейтинг:

0 / 0

16.07.2013, 13:18

| Ответить | Цитировать | Написать

Нужен совет по построению индекса

#38332911

Критик

Участник

Откуда: Москва / Калуга

Сообщения: 35 963

Рейтинг: 0 / 0

create indexно почему-то в два раз больше съедает процессорного времени, и как следствие выполняется в два раза дольше.

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

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4263.0 (X64)
Aug 23 2012 15:56:56
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

автор (IsDeleted, ShopCode, saledate) include(Amount, Qty)
Table 't1'. Scan count 6, logical reads 1325, <остальные все 0>

SQL Server Execution Times:
CPU time = 172 ms, elapsed time = 172 ms.

(ShopCode, saledate) include(Amount, Qty) where IsDeleted = 0

Table 't1'. Scan count 6, logical reads 1289, <остальные все 0>

SQL Server Execution Times:
CPU time = 172 ms, elapsed time = 168 ms.

...

Рейтинг:

0 / 0

16.07.2013, 15:01

| Ответить | Цитировать | Написать

Нужен совет по построению индекса

#38332947

create index

Гость

Критикcreate indexно почему-то в два раз больше съедает процессорного времени, и как следствие выполняется в два раза дольше.

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

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4263.0 (X64)
Aug 23 2012 15:56:56
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

автор (IsDeleted, ShopCode, saledate) include(Amount, Qty)
Table 't1'. Scan count 6, logical reads 1325, <остальные все 0>

SQL Server Execution Times:
CPU time = 172 ms, elapsed time = 172 ms.

(ShopCode, saledate) include(Amount, Qty) where IsDeleted = 0

Table 't1'. Scan count 6, logical reads 1289, <остальные все 0>

SQL Server Execution Times:
CPU time = 172 ms, elapsed time = 168 ms.
цифры малы, и на них вполне могут оказывать влияние внешние, а т.к. другого объяснения увеличению времени выполнения я придумать не могу, скорее всего ваши результаты ближе к истине

а по поводу индекса

Код: plaintext

1.
2.
3.

(IsDeleted, saledate) include(Amount, Qty, ShopCode)
Table 't1'. Scan count 1, logical reads 2171reads 0.
   CPU time = 141 ms,  elapsed time = 135 ms.

Код: plaintext

(saledate) include(Amount, Qty, ShopCode) where IsDeleted = 0
-- нет в расчетах

то они вполне себе могут выдавать лучшие результаты при увеличении числа ShopCode в "in (...)" благодаря "Scan count 1"

...

Рейтинг:

0 / 0

16.07.2013, 15:11

| Ответить | Цитировать | Написать

10 сообщений из 35, страница 2 из 2

все

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Нужен совет по построению индекса

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

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

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=46&msg=38332947&tid=1706129]:	0ms
get settings:	9ms
get forum list:	18ms
check forum access:	4ms
check topic access:	4ms
track hit:	25ms
get topic data:	12ms
get forum data:	3ms
get page messages:	52ms
get tp. blocked users:	2ms
others:	244ms

total:	373ms