ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Key lookup vs RID Lookup
17 сообщений из 17, страница 1 из 1
Key lookup vs RID Lookup
    #39813121
Cristiano_Rivaldo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cristiano_Rivaldo
Гость
Всем привет.
На текущем проекте абсолютно все таблицы - кластеризованные.
Меня, как бывшего ораклиста, мучает вопрос- почему никто не использует кучи или используют очень редко?
Оракловый аналог 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, 16:44
| Ответить | Цитировать | Написать  
Key lookup vs RID Lookup
    #39813127
Гавриленко Сергей Алексеевич
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гавриленко Сергей Алексеевич
Участник
А теперь сравните скорость выборки по полю id на вашей структуре.
...
Рейтинг: 0 / 0
14.05.2019, 17:03
| Ответить | Цитировать | Написать  
Key lookup vs RID Lookup
    #39813128
Slava_Nik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Slava_Nik
Участник
результаты ожидаемы, поэтому вставку делают обычно в кучи или стедж таблицы.
Для каких-то задач используются кучи, для каких-то кластеризованные таблицы.
Вы попробуйте пожить с вашей кучей, удалить, вставить данные, перестроить и объем хороший сделать.
...
Рейтинг: 0 / 0
14.05.2019, 17:05
| Ответить | Цитировать | Написать  
Key lookup vs RID Lookup
    #39813133
Cristiano_Rivaldo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cristiano_Rivaldo
Гость
Гавриленко Сергей Алексеевич,

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

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

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

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

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

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

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

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

Не всегда, в куче возможен Forwarding Pointers+1 чтение, а индекс это как минимум 3-4 уровня.
...
Рейтинг: 0 / 0
16.05.2019, 22:55
| Ответить | Цитировать | Написать  
Key lookup vs RID Lookup
    #39814613
-- KAB --
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-- KAB --
Участник
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, 14:20
| Ответить | Цитировать | Написать  
Key lookup vs RID Lookup
    #39814782
Фотография Mind
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Mind
Участник
-- KAB --Посмотрите результат скрипта на вкладке messages в SSMS - статистика ввода вывода таблица tn - nonclustered и tc - clusteredДа, согласен. Этого момента я не учел.
...
Рейтинг: 0 / 0
17.05.2019, 19:16
| Ответить | Цитировать | Написать  
17 сообщений из 17, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Key lookup vs RID Lookup
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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