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

Откуда: Сидней
Сообщений: 1049
Добрый день,

Есть такой запрос:

select * from mytable 
where id=1234567 and pkey=13707


pkey в данном случае ключ по которому таблица партицированна. Всего партиций 15000.


Если запустить подобный запрос на не партицированной таблице:
select * from mytable_no_partition 
where id=1234567

То время исполнения (из Джава приложения) в два-три раза ниже: 170ms вместо 400-500.

Но при этом у первого запроса в реальном плане исполнения Estimated subtree cost = 0.073 а у вторго 0.1366

Почему более низкая стоимость запроса не транслируется в более короткое время исполнения?

Спасибо.
3 окт 19, 11:20    [21985651]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
msLex
Member

Откуда:
Сообщений: 6674
Roust_m
Почему более низкая стоимость запроса не транслируется в более короткое время исполнения?

потому что стоимость плана это не время исполнения.
Не говоря уже, что это Estimated

Покажите планы
3 окт 19, 11:43    [21985671]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6788
Roust_m,

вы смотрите на предполагаемый план, смотрите на фактический. Сравните, например, объем просмотренных данных в том и ином случае.

Например, для второй таблицы есть индекс по ID и вы получите seek, а в первой таблице индекса нет и получите просмотр секции.
3 окт 19, 11:44    [21985677]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1049
Вот план по партицированной таблице. Ниже добавлю второй план по непартицированной По идее вся суть партицирования это зверски улучшить скорость чтения когда ты знаешь в какой партиции ищещь. А получается наоборот, улучшение в непартицированной таблице.

К сообщению приложен файл. Размер - 96Kb
4 окт 19, 02:41    [21986349]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1049
Вот непартицированная таблица

К сообщению приложен файл. Размер - 141Kb
4 окт 19, 02:41    [21986350]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36450
Roust_m
По идее вся суть партицирования это зверски улучшить скорость чтения когда ты знаешь в какой партиции ищещь
Тот евангелист, который вам это в уши напел, вас злостно обманул. Index seek по индексу даже с невероятной глубиной в 8-9 уровней уделает любой скан по партиции, если нет нормального seek predicate после elimination.
4 окт 19, 02:46    [21986351]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36450
Модератор: Короче, уважаемый Roust_m.

Давайте так. Или вы предоставляете скрипты ваших таблиц, ваши запросы и нормальные планы (обфусцируйте уж как-нибудь), или я к вам за троллинг санкции применю. А то вот сначала эти эпатажные вбросы в двух топиках, что секционирование вам все замедлило, а потом выясняется, что всего лишь у разных ваших запросов к разным вашим таблицам разный план.
4 окт 19, 02:54    [21986353]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36450
Модератор: Еще раз русскими буквами: скрипты таблиц с индексами (в текстовом виде), запросы к этим таблицам (в текстовом виде), планы этих запросов (в текстовом виде или в виде sqlplan-файлов). Cкриншоты будут стираться на месте.
4 окт 19, 03:15    [21986357]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1049
Вот скрипт партицированной таблицы. Там 15000 партиций. Ключ pkey есть функция от st_id: st_id % 14999 + 1


CREATE TABLE [dbo].[mypartitionedtable](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[sid] [bigint] NOT NULL,
	[node_id] [bigint] NOT NULL,
	[st_id] [bigint] NOT NULL,
	[col5] [bigint] NULL,
	[col6] [bigint] NULL,
	[col7] [varchar](2048) NULL,
	[col8] [varchar](512) NULL,
	[col9] [datetime] NULL,
	[col10] [varchar](16) NULL,
	[coll11] [bigint] NULL,
	[coll12] [char](1) NULL,
	[col13] [varchar](512) NULL,
	[col14] [smallint] NULL,
	[col15] [varchar](80) NOT NULL,
	[col16] [datetime] NOT NULL,
	[col17] [varchar](80) NULL,
	[col18] [datetime] NULL,
	[pkey] [smallint] NOT NULL,
 CONSTRAINT [PK_mypartitionedtable] PRIMARY KEY CLUSTERED 
(
	[id] ASC,
	[pkey] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [ps_partition_id]([pkey])
) ON [ps_partition_id]([pkey])
GO
CREATE NONCLUSTERED INDEX [IX_mypartitionedtable_sid] ON [dbo].[mypartitionedtable]
(
	[sid] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [ps_partition_id]([pkey])
GO
CREATE UNIQUE NONCLUSTERED INDEX [UX_mypartitionedtable] ON [dbo].[mypartitionedtable]
(
	[st_id] ASC,
	[node_id] ASC,
	[pkey] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [ps_partition_id]([pkey])
GO
4 окт 19, 03:25    [21986358]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1049
Вот непартицированная таблица:
CREATE TABLE [tmp].[my_not_partitionedtable](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[sid] [bigint] NOT NULL,
	[node_id] [bigint] NOT NULL,
	[st_id] [bigint] NOT NULL,
	[col5] [bigint] NULL,
	[sec_id] [bigint] NULL,
	[col7] [varchar](2048) NULL,
	[col8] [varchar](512) NULL,
	[col9] [datetime] NULL,
	[col10] [varchar](16) NULL,
	[coll11] [bigint] NULL,
	[coll12] [char](1) NULL,
	[col13] [varchar](512) NULL,
	[col14] [smallint] NULL,
	[col15] [varchar](80) NOT NULL,
	[col16] [datetime] NOT NULL,
	[col17] [varchar](80) NULL,
	[col18] [datetime] NULL,
 CONSTRAINT [PK_my_not_partitionedtable] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_my_not_partitionedtable] ON [tmp].[my_not_partitionedtable]
(
	[sid] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [UX_my_not_partitionedtable] ON [tmp].[my_not_partitionedtable]
(
	[sec_id] ASC,
	[node_id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO
4 окт 19, 03:33    [21986359]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36450
У вас вторые некластерные индексы UX_mypartitionedtable / UX_my_not_partitionedtable вообще не идентичны если убрать ключ секционирования.
4 окт 19, 03:35    [21986360]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1049
Вот запрос в партицированной таблице:

select * from [dbo].[mypartitionedtable] where sid=140862 and pkey=13707



А это к непратицированной:

select * from [tmp].[my_not_partitionedtable] where sid=131623 
4 окт 19, 03:37    [21986361]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36450
Удивительно, что хоть где-то index seek...

Чтобы было быстро выбирать все поля по sid, надо сделать индекс по (sid) include (все остальные поля, кроме ключей кластерного индекса).
А секционирование уберите. В вашем случае оно разве что могло бы быть похоже на борьбу с latch contention, которого, скорее всего, у вас и в помине нет. Рано еще оно вам.
4 окт 19, 03:43    [21986362]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1049
Гавриленко Сергей Алексеевич
У вас вторые некластерные индексы UX_mypartitionedtable / UX_my_not_partitionedtable вообще не идентичны если убрать ключ секционирования.


Хоть этот индекс и не используется, исправил, теперь непратицированная таблица выглядит так. (ниже). Непратицированная таблица по прежнему гораздо быстрее по времени. Щас зашишрую немного имена объектов и выложу планы.

CREATE TABLE [tmp].[my_not_partitionedtable](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[sid] [bigint] NOT NULL,
	[node_id] [bigint] NOT NULL,
	[st_id] [bigint] NOT NULL,
	[col5] [bigint] NULL,
	[sec_id] [bigint] NULL,
	[col7] [varchar](2048) NULL,
	[col8] [varchar](512) NULL,
	[col9] [datetime] NULL,
	[col10] [varchar](16) NULL,
	[coll11] [bigint] NULL,
	[coll12] [char](1) NULL,
	[col13] [varchar](512) NULL,
	[col14] [smallint] NULL,
	[col15] [varchar](80) NOT NULL,
	[col16] [datetime] NOT NULL,
	[col17] [varchar](80) NULL,
	[col18] [datetime] NULL,
 CONSTRAINT [PK_my_not_partitionedtable] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_my_not_partitionedtable] ON [tmp].[my_not_partitionedtable]
(
	[sid] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [UX_my_not_partitionedtable] ON [tmp].[my_not_partitionedtable]
(
	[st_id] ASC,
	[node_id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO
4 окт 19, 03:56    [21986363]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1049
Гавриленко Сергей Алексеевич
Удивительно, что хоть где-то index seek...

Чтобы было быстро выбирать все поля по sid, надо сделать индекс по (sid) include (все остальные поля, кроме ключей кластерного индекса).
А секционирование уберите. В вашем случае оно разве что могло бы быть похоже на борьбу с latch contention, которого, скорее всего, у вас и в помине нет. Рано еще оно вам.


Предполагалось, что таблица будет зверски расти и число одновременных пользователей тоже будет сильно расти. База в стадии дизайна, какие запросы будут к ней до конца неизвестно. Большая часть запросов герерится Джавой (там есть какая-то автоматическая приблуда для этого). В редких случаях, когда совсем никак эти запросы выносятся в хранимую процедуру, остальное автоматом, чтобы ускорить время разработки. Повлиять я на это не могу.

Еще один момент, который учитывался при решении применить секционирование, это то, что большинство запросов используют st_id или он известен. Поскольку диапазон st_id около миллиона, то, создали pkey, который есть остаток от деления st_id на 14999 плюс 1.

То есть предполагалось, что все данные для одного и того-же студента будут в одной партиции. На данный момент в партиции с pkey=13707 всего 353 записи, из которых выбирается 31 запись.

В непартицированной таблице 5843430 записей, из которых выбирается 52. Я не понимаю, почему выбрать 31 запись из 353 занимает больше времени, чем выбрать 52 записи из почти 6-ти миллионов. Я теряю веру в технологический прогресс и вообще во все человечество.

Решение убрать секционирование не очень простое, ибо это может затронуть другие запросы.
4 окт 19, 04:16    [21986364]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1049
Гавриленко Сергей Алексеевич
Удивительно, что хоть где-то index seek...

Чтобы было быстро выбирать все поля по sid, надо сделать индекс по (sid) include (все остальные поля, кроме ключей кластерного индекса).


Разные запросы будут использовать разные поля, то есть придется создать индекс по sid и включить туда почти все поля в таблице. Если следовать этому подходу и для других запросов, то размер индексов будет огромен, что замедлит вставку данных в таблицу а также изменение данных. Палка о двух конца.
4 окт 19, 04:40    [21986365]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1049
Вот зашифрованный план по партицированной таблице. Очень желательно найти способ сделать так, чтобы это работало быстро на партицированной таблице. Убирать партицирование будет означать проблемы в других местах.

К сообщению приложен файл (partitioned_table_plan2.sqlplan - 24Kb) cкачать
4 окт 19, 04:56    [21986367]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1049
Владислав Колосов
Roust_m,

вы смотрите на предполагаемый план, смотрите на фактический. Сравните, например, объем просмотренных данных в том и ином случае.

Например, для второй таблицы есть индекс по ID и вы получите seek, а в первой таблице индекса нет и получите просмотр секции.


В обеих таблицах есть индекс по ID (sid), в партицированной таблице он тоже партицирован, однако же в партицированной таблице такой индекс не используется и происходит скан по кластерному индексу.
4 окт 19, 05:18    [21986372]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Idol_111
Member

Откуда:
Сообщений: 552
Roust_m
Большая часть запросов герерится Джавой (там есть какая-то автоматическая приблуда для этого). В редких случаях, когда совсем никак эти запросы выносятся в хранимую процедуру, остальное автоматом, чтобы ускорить время разработки. Повлиять я на это не могу.

как это знакомо, а вот теперь огребаем по полной.
4 окт 19, 05:53    [21986374]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
StarikNavy
Member

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

партиционирование это в основном для удобства обслуживания, а не для ускорения запросов
4 окт 19, 09:49    [21986441]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6788
Roust_m,

у вас на плане просмотр секционированной таблицы и поиск в индексе по несекционированной. Отсюда и разница.
4 окт 19, 11:27    [21986549]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6788
Roust_m,

сканирование может происходить в результате несоблюдения приоритета типов или самих типов передаваемого в запрос параметра и аргумента функции секционирования.

И да, секционирование не всегда имеет смысл создавать для сокращения времени запросов. Нельзя утверждать, что оно предназначено именно для обслуживания, его назначение MS как раз и определяет как "средство, позволяющее ускорить выполнения запросов при больших размерах таблиц" в учебных курсах, например. Однако, они не рассматривают подробно сценарии, применимые для таких практик, поэтому возникает ощущение, что так следует делать всегда на любых больших таблицах.
4 окт 19, 11:36    [21986564]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
invm
Member

Откуда: Москва
Сообщений: 8797
Roust_m
Вот зашифрованный план по партицированной таблице
Покажите план для
select * from mypartitionedtable with (index = IX_mypartitionedtable_sid)
where sid = ... and pkey = ...
option (maxdop 1)


ЗЫ: Анонимайзер планов есть в SentryOne Plan Explorer
4 окт 19, 14:00    [21986745]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
aleks222
Member

Откуда:
Сообщений: 747
Roust_m
Я не понимаю, почему выбрать 31 запись из 353 занимает больше времени, чем выбрать 52 записи из почти 6-ти миллионов. Я теряю веру в технологический прогресс и вообще во все человечество.

В управдомы вам, батенька, надо переквалифицироваться.
Иначе бы знали, что выборка по уникальному индексу из индексированной таблицы от размеров таблицы, практически, не зависит.

Или другими словами: бесполезно ожидать ускорения выборки от партиционирования.
4 окт 19, 15:57    [21986876]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6612
aleks222,
автор
Или другими словами: бесполезно ожидать ускорения выборки от партиционирования.

деградацию так точно стоит ожидать
4 окт 19, 15:58    [21986878]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить