Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Key lookup vs RID Lookup / 17 сообщений из 17, страница 1 из 1
14.05.2019, 16:44
    #39813121
Cristiano_Rivaldo
Cristiano_Rivaldo
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Key lookup vs RID Lookup
Всем привет.
На текущем проекте абсолютно все таблицы - кластеризованные.
Меня, как бывшего ораклиста, мучает вопрос- почему никто не использует кучи или используют очень редко?
Оракловый аналог RID Lookup - это table access by index rowid и это там чуть ли не основной оператор для доступа к данным.
Решил замерить производительность оператора select на кучу и на кластеризованную таблицу:

Код: 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.
-- кластеризованная таблица
CREATE TABLE dbo.t_sample_clustered 
(
	[ID] INT IDENTITY(1,1) NOT NULL,
	A INT NOT NULL,
	B INT NOT NULL 
	CONSTRAINT [PK_t_sample_clustered] PRIMARY KEY CLUSTERED([ID] )
)

INSERT INTO dbo.t_sample_clustered  (a,b)
SELECT	TOP 1000000 
		ABS(CONVERT(INT, (CONVERT(BINARY(4), (NEWID()))))) % 1000 AS a,
		ABS(CONVERT(INT, (CONVERT(BINARY(4), (NEWID()))))) % 1000 AS b
FROM sysobjects A
CROSS JOIN sysobjects B
CREATE NONCLUSTERED INDEX [IDX_t_sample_clustered_a] ON dbo.t_sample_clustered (a)

UPDATE STATISTICS dbo.t_sample_clustered  WITH FULLSCAN

-- куча
CREATE TABLE dbo.t_sample 
(
	[ID] INT IDENTITY(1,1) NOT NULL,
	A INT NOT NULL,
	B INT NOT NULL 
	CONSTRAINT [PK_t_sample] PRIMARY KEY NONCLUSTERED([ID] )
)
-- заполнение
INSERT INTO dbo.t_sample  (a,b)
SELECT	A,B
FROM dbo.t_sample_clustered 
-- 
CREATE NONCLUSTERED INDEX [IDX_t_sample_a] ON dbo.t_sample (a)


UPDATE STATISTICS dbo.t_sample  WITH FULLSCAN

SELECT t.b
FROM dbo.t_sample_clustered t
WHERE t.a = 45

SELECT t.b
FROM dbo.t_sample t
WHERE t.a = 45


Результаты :
Для кластерной таблицы : Duration = 20,CPU Cost = 31, Reads = 3036
Для кучи : Duration = 8,CPU Cost = 0, Reads = 993
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.05.2019, 17:03
    #39813127
Гавриленко Сергей Алексеевич
Гавриленко Сергей Алексеевич
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Key lookup vs RID Lookup
А теперь сравните скорость выборки по полю id на вашей структуре.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.05.2019, 17:05
    #39813128
Slava_Nik
Slava_Nik
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Key lookup vs RID Lookup
результаты ожидаемы, поэтому вставку делают обычно в кучи или стедж таблицы.
Для каких-то задач используются кучи, для каких-то кластеризованные таблицы.
Вы попробуйте пожить с вашей кучей, удалить, вставить данные, перестроить и объем хороший сделать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.05.2019, 17:10
    #39813133
Cristiano_Rivaldo
Cristiano_Rivaldo
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Key lookup vs RID Lookup
Гавриленко Сергей Алексеевич,

Для кластерной таблицы : Duration = 1,CPU Cost = 16, Reads = 3
Для кучи : Duration = 1,CPU Cost = 0, Reads = 4

Запросы по id ,в общем случае, редкое явление при условии что таблица не является справочником.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.05.2019, 17:12
    #39813134
msLex
msLex
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Key lookup vs RID Lookup
Cristiano_Rivaldo

1. RID Lookup в MSSQL это доступ по физическому расположению (file_id + page_id + slot_id) записи в куче.
2. Вы сравниваете неэквивалентные случае, в первом у вас два индекса (один из них кластерный) и поиск можно осуществлять по любому из них. Сделайте и там и там по одному индексу и сравните.
3. При любых перемещении исходных данных в кластерном индексе не требуется обновлять некластерный.
4. В MSSQL у куч есть некоторое количество неприятных особенностей.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.05.2019, 17:23
    #39813141
Cristiano_Rivaldo
Cristiano_Rivaldo
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Key lookup vs RID Lookup
msLex,

В обоих случаях по два индекса.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.05.2019, 17:23
    #39813142
Гавриленко Сергей Алексеевич
Гавриленко Сергей Алексеевич
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Key lookup vs RID Lookup
Cristiano_RivaldoГавриленко Сергей Алексеевич,

Для кластерной таблицы : Duration = 1,CPU Cost = 16, Reads = 3
Для кучи : Duration = 1,CPU Cost = 0, Reads = 4Т.е. ровно на 25% хуже в вашем случе. Для деревьев глубиной 5-6 будет 10-15% ухудшения. Весьма существенно.

Cristiano_RivaldoЗапросы по id ,в общем случае, редкое явление при условии что таблица не является справочником.Открою секрет -- кластерный индекс можно делать не только по полю id.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.05.2019, 17:24
    #39813145
msLex
msLex
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Key lookup vs RID Lookup
Cristiano_RivaldomsLex,

В обоих случаях по два индекса.
да не заметил

тогда у вас в случае кучи оверхед на хранение.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.05.2019, 17:25
    #39813146
Cristiano_Rivaldo
Cristiano_Rivaldo
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Key lookup vs RID Lookup
Гавриленко Сергей Алексеевич]Открою секрет -- кластерный индекс можно делать не только по полю id.
Я в курсе :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.05.2019, 18:01
    #39813160
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Key lookup vs RID Lookup
авторМеня, как бывшего ораклиста, мучает вопрос- почему никто не использует кучи или используют очень редко?
если все задачи с бд сводятся к выбору a = 45, то и ms sql и скорее всего oracle проиграют наверное большиству nosql dbms
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.05.2019, 21:00
    #39813219
Mind
Mind
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Key lookup vs RID Lookup
Гавриленко Сергей АлексеевичCristiano_RivaldoГавриленко Сергей Алексеевич,

Для кластерной таблицы : Duration = 1,CPU Cost = 16, Reads = 3
Для кучи : Duration = 1,CPU Cost = 0, Reads = 4Т.е. ровно на 25% хуже в вашем случе. Для деревьев глубиной 5-6 будет 10-15% ухудшения. Весьма существенно.При поиске по некластерному индексу, куча будет работать быстрее на те же несколько чтений, так что я бы не сказал что у кластерного индекса прям существенное преимущество. Обсуждалось это уже много-много раз.
Основная проблема с кучей это её обслуживание. Не знаю как в Оракле, а в SQL Server все плохо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.05.2019, 21:12
    #39813222
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Key lookup vs RID Lookup
Cristiano_RivaldoЗапросы по id ,в общем случае, редкое явление при условии что таблица не является справочникомЗапросы по RID ещё более редкое явление, даже если таблица является справочником.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.05.2019, 21:18
    #39813226
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Key lookup vs RID Lookup
Cristiano_Rivaldo,

https://www.red-gate.com/simple-talk/sql/t-sql-programming/delete-operation-sql-server-heaps/
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.05.2019, 17:14
    #39814192
-- KAB --
-- KAB --
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Key lookup vs RID Lookup
MindГавриленко Сергей Алексеевичпропущено...
Т.е. ровно на 25% хуже в вашем случе. Для деревьев глубиной 5-6 будет 10-15% ухудшения. Весьма существенно.При поиске по некластерному индексу, куча будет работать быстрее на те же несколько чтений, так что я бы не сказал что у кластерного индекса прям существенное преимущество. Обсуждалось это уже много-много раз.
Основная проблема с кучей это её обслуживание. Не знаю как в Оракле, а в SQL Server все плохо.

Не всегда, в куче возможен Forwarding Pointers
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.05.2019, 22:55
    #39814322
Mind
Mind
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Key lookup vs RID Lookup
-- KAB --Mindпропущено...
При поиске по некластерному индексу, куча будет работать быстрее на те же несколько чтений, так что я бы не сказал что у кластерного индекса прям существенное преимущество. Обсуждалось это уже много-много раз.
Основная проблема с кучей это её обслуживание. Не знаю как в Оракле, а в SQL Server все плохо.

Не всегда, в куче возможен Forwarding Pointers+1 чтение, а индекс это как минимум 3-4 уровня.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.05.2019, 14:20
    #39814613
-- KAB --
-- KAB --
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Key lookup vs RID Lookup
Mind-- KAB --пропущено...


Не всегда, в куче возможен Forwarding Pointers+1 чтение, а индекс это как минимум 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.
30.
31.
--drop table #tn, #tc
create table #tn (id int identity primary key nonclustered, name varchar(900))
create table #tc (id int identity primary key clustered, name varchar(900))

;with cte as (
    select 1 i union all
    select i+1 from cte
)
insert #tn
    output inserted.name into #tc
    select top(20000) 'a'
    from cte option(maxrecursion 0)

set statistics io on
select max(name) from #tn
select max(name) from #tc
set statistics io off

update #tn set name = 'ab'
update #tc set name = 'ab'

update #tn set name = 'abc'
update #tc set name = 'abc'

update #tn set name = 'abcd'
update #tc set name = 'abcd'

set statistics io on
select max(name) from #tn
select max(name) from #tc
set statistics io off



Посмотрите результат скрипта на вкладке messages в SSMS - статистика ввода вывода таблица tn - nonclustered и tc - clustered
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.05.2019, 19:16
    #39814782
Mind
Mind
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Key lookup vs RID Lookup
-- KAB --Посмотрите результат скрипта на вкладке messages в SSMS - статистика ввода вывода таблица tn - nonclustered и tc - clusteredДа, согласен. Этого момента я не учел.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Key lookup vs RID Lookup / 17 сообщений из 17, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]