Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: 1 2 3      [все]
 Запрос к партицированной таблице и к обычной таблице  [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]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
aleks222
Member

Откуда:
Сообщений: 747
Владислав Колосов
его назначение MS как раз и определяет как "средство, позволяющее ускорить выполнения запросов при больших размерах таблиц"

Поздравляю вас, соврамши.
Нигде, никогда MS такого не обещала.
4 окт 19, 15:59    [21986880]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36450
aleks222
Владислав Колосов
его назначение MS как раз и определяет как "средство, позволяющее ускорить выполнения запросов при больших размерах таблиц"

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

Откуда: Kiev
Сообщений: 6612
автор
ускорить выполнения запросов при больших размерах таблиц

удалять хорошо, обслуживать индексы, иногда вставкоу разруливают.. В общем как и сказали это чисто административная штука, не для селектов :)
4 окт 19, 16:07    [21986886]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Владислав Колосов
Member

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

Вы сочиняете, а MS пишет в справке раздел Секционированные таблицы и индексы: Можно повысить скорость выполнения запросов в зависимости от запросов, которые часто выполняются в вашей конфигурации оборудования. Например, оптимизатор запросов может быстрее выполнять запросы на эквисоединение двух и более секционированных таблиц, если в этих таблицах одни и те же столбцы секционирования, потому что можно соединить сами секции.

Замечу, что в другом случае, выигрыш производительности получаем при HASH JOIN соединении, т.к. запрос может просмотреть не весь объём данных, а только одну секцию при подборе строк для сравнения.

В случае выбора SEEK стратегии разделение на секции не дает преимуществ.
4 окт 19, 17:45    [21986991]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
alexeyvg
Member

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

Вы сочиняете, а MS пишет в справке раздел Секционированные таблицы и индексы: Можно повысить скорость выполнения запросов в зависимости от запросов, которые часто выполняются в вашей конфигурации оборудования. Например, оптимизатор запросов может быстрее выполнять запросы на эквисоединение двух и более секционированных таблиц, если в этих таблицах одни и те же столбцы секционирования, потому что можно соединить сами секции.

Замечу, что в другом случае, выигрыш производительности получаем при HASH JOIN соединении, т.к. запрос может просмотреть не весь объём данных, а только одну секцию при подборе строк для сравнения.

В случае выбора SEEK стратегии разделение на секции не дает преимуществ.
По моему, диапазон кластерного индекса даст то же самое.
4 окт 19, 22:13    [21987160]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36450
alexeyvg
По моему, диапазон кластерного индекса даст то же самое.
Тут рассказывают про то, что в некоторых случаях можно секционированием избежать создания дополнительного индекса. Есть ситуации, когда сканить пару необольших секций терпимо, а делать индекс ради ускорения этого дофига накладно.
4 окт 19, 22:38    [21987175]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29246
Гавриленко Сергей Алексеевич
alexeyvg
По моему, диапазон кластерного индекса даст то же самое.
Тут рассказывают про то, что в некоторых случаях можно секционированием избежать создания дополнительного индекса. Есть ситуации, когда сканить пару необольших секций терпимо, а делать индекс ради ускорения этого дофига накладно.
Так секционирование концептуально то же самое, что и кластерный индекс по тому же полю, что и поле секционирования. Индекс не дополнительный, а один, заменяющий секционирование.
Для постоянной вставки в секции ещё можно увидеть преимущества секционирования, но для стабильных данных я их не вижу, будет такой же "скан пары небольших секций".

Конечно, есть какие то совсем особые случаи, как описал Владислав Колосов, но это всё таки совсем редкость, на практике...
5 окт 19, 00:48    [21987204]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Roust_m
Member

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


ЗЫ: Анонимайзер планов есть в SentryOne Plan Explorer


Спасибо за подсказку, я предыдущий руками анонимизровал, прилагаю план.

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

Откуда: Сидней
Сообщений: 1049
А вот такой же план по не партицированной таблице. Стоимость дерева похожа, а время выполнения все равно быстрее в не партицированной таблице.

К сообщению приложен файл (not_partitioned_table_plan2_Anonymized.sqlplan - 11Kb) cкачать
8 окт 19, 04:25    [21988995]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
aleks222
Member

Откуда:
Сообщений: 747
Не знаю, как там у антиподов, в австралии.
А в посконной завсегда поиск по ДВУМ полям ключа дольше, чем по одномую
8 окт 19, 06:27    [21989003]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29246
Roust_m
Стоимость дерева похожа, а время выполнения все равно быстрее в не партицированной таблице.
Стоимость - это большая условность, вычисляется исходя из, например, определённой скорости HDD.

А время выполнения разных запросов может быть разная, что тут удивительного?
Они даже разное количество строк возвращают. Хотя вроде бы смотрим разницу одинаковых запросов на одинаковых данных, и разница должна быть только в партицировании.

Далее, если вы ищете по sid:
Roust_m
invm
Покажите план для
select * from mypartitionedtable with (index = IX_mypartitionedtable_sid)
where sid = ... and pkey = ...
option (maxdop 1)

ЗЫ: Анонимайзер планов есть в SentryOne Plan Explorer

Спасибо за подсказку, я предыдущий руками анонимизровал, прилагаю план.
В плане написано INDEX(Column1) ... WHERE Column2=... AND Column3=...
Это ошибка анонимайзера, или ваша? Вроде индекс должен быть по той же колонке, по которой вы ищите (то есть по sid)?



И третье, вам уже писали, повторю ещё раз - если у вас есть индекс по полю, сервер идёт по этому дереву, достаёт записи, какое преимущество будет, если ему указать партицию?

Он всё равно считает те страницы данных, которые нашёл.

Если бы сервер понял, что выгоднее скан, поскольку селективность индекса низкая, то понятно, можно отсканить одну партицию, но если делается поиск, то почему вы ожидаете ускорение?
8 окт 19, 08:15    [21989040]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1049
alexeyvg
Roust_m
Стоимость дерева похожа, а время выполнения все равно быстрее в не партицированной таблице.
Стоимость - это большая условность, вычисляется исходя из, например, определённой скорости HDD.

А время выполнения разных запросов может быть разная, что тут удивительного?
Они даже разное количество строк возвращают. Хотя вроде бы смотрим разницу одинаковых запросов на одинаковых данных, и разница должна быть только в партицировании.

Далее, если вы ищете по sid:
Roust_m
пропущено...

Спасибо за подсказку, я предыдущий руками анонимизровал, прилагаю план.
В плане написано INDEX(Column1) ... WHERE Column2=... AND Column3=...
Это ошибка анонимайзера, или ваша? Вроде индекс должен быть по той же колонке, по которой вы ищите (то есть по sid)?



И третье, вам уже писали, повторю ещё раз - если у вас есть индекс по полю, сервер идёт по этому дереву, достаёт записи, какое преимущество будет, если ему указать партицию?

Он всё равно считает те страницы данных, которые нашёл.

Если бы сервер понял, что выгоднее скан, поскольку селективность индекса низкая, то понятно, можно отсканить одну партицию, но если делается поиск, то почему вы ожидаете ускорение?


Это ошибка анонимайзера, на самом деле там и индекс и один из столбцов по sid.

Я пытаюсь понять как можно сервер заставить сканировать одну партицию, или другим способом заставить запрос работать быстрее.
8 окт 19, 08:36    [21989057]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29246
Roust_m
Я пытаюсь понять как можно сервер заставить сканировать одну партицию, или другим способом заставить запрос работать быстрее.
Что бы заставить сервер сканировать одну партицию, нужно убрать те индексы, которые позволяют серверу быстро получить данные. тогда сервер свалится в скан.

Попробую объяснить ещё раз.

Вот у вас есть индекс, там указано, что ваши данные находятся на диске в секторе № 123321
Сервер читает сектор 123321, готово!

Далее, вы задаёте вопрос: "как можно сервер заставить сканировать одну партицию"

Вы понимаете, что такой вопрос абсурден, в контексте задачи "прочитать сектор 123321"?
8 окт 19, 08:57    [21989077]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
invm
Member

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

План со сканом секции банально дешевле плана с поиском в секции индекса.
Стоимость плана с несекционированным индексом аналогична плану с секционированным.

Можете ради интереса попробовать
select * from mypartitionedtable 
where sid = ... and pkey = ...
option (maxdop 1, use hint('FORCE_LEGACY_CARDINALITY_ESTIMATION'))


Учитывая, что у вас бета 2019-го сервера, не вижу смысла в дальнейших разбирательствах.
8 окт 19, 09:23    [21989100]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1049
alexeyvg
Roust_m
Я пытаюсь понять как можно сервер заставить сканировать одну партицию, или другим способом заставить запрос работать быстрее.
Что бы заставить сервер сканировать одну партицию, нужно убрать те индексы, которые позволяют серверу быстро получить данные. тогда сервер свалится в скан.

Попробую объяснить ещё раз.

Вот у вас есть индекс, там указано, что ваши данные находятся на диске в секторе № 123321
Сервер читает сектор 123321, готово!

Далее, вы задаёте вопрос: "как можно сервер заставить сканировать одну партицию"

Вы понимаете, что такой вопрос абсурден, в контексте задачи "прочитать сектор 123321"?


Ну контекст задачи немного шире. Еще одной целью партицирования было собрать все записи по одному и тому же студенту в одной партиции, вместо того, чтобы им быть разбросанными по всей таблице. У одного студента может быть несколько sid. Но pkey есть функция от id студента (st_id). В приложении этот запрос не единственный, поэтому задача заставить это запрос работать на секционированной таблице как можно быстрее. Это на не секционрованной таблице по этому запросу есть индекс где указано, что данные находятся в секторе № 123321. А на секционированной так не получется. Но поскольку секционирование в нашем случае нужно (IMHO), то приодится искать компромисы.
8 окт 19, 10:14    [21989138]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Yasha123
Member

Откуда:
Сообщений: 1553
[quot Roust_m]
alexeyvg
Еще одной целью партицирования было собрать все записи по одному и тому же студенту в одной партиции, вместо того, чтобы им быть разбросанными по всей таблице. У одного студента может быть несколько sid. Но pkey есть функция от id студента (st_id). В приложении этот запрос не единственный, поэтому задача заставить это запрос работать на секционированной таблице как можно быстрее.

ну так соберите их в индексе по pkey,
если запросам это надо.

одно ваше " Всего партиций 15000" заставляется задуматься о неоправданном оверхеде.
вы же своей таблице добавили метаданных больше некуда,
вместо 3 строк allocation units на таблицу типа inrow,row overflow, lob
у вас их 45000.
это не считая индексов, каждый из них еще столько же добавляет.
все это добро надо каждый раз просматривать и оно отгрызет место в buffer pool.
какое уж тут ускорение процесса...
---
ну и у вас что, на каждого студента своя партиция?
8 окт 19, 10:53    [21989186]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1049
[quot Yasha123]
Roust_m
пропущено...

ну так соберите их в индексе по pkey,
если запросам это надо.

одно ваше " Всего партиций 15000" заставляется задуматься о неоправданном оверхеде.
вы же своей таблице добавили метаданных больше некуда,
вместо 3 строк allocation units на таблицу типа inrow,row overflow, lob
у вас их 45000.
это не считая индексов, каждый из них еще столько же добавляет.
все это добро надо каждый раз просматривать и оно отгрызет место в buffer pool.
какое уж тут ускорение процесса...
---
ну и у вас что, на каждого студента своя партиция?


Нет, студентов всего около миллиона.
8 окт 19, 14:09    [21989423]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Yasha123
Member

Откуда:
Сообщений: 1553
а какой у вас объем таблицы в гигабайтах?
8 окт 19, 14:50    [21989484]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
aleks222
Member

Откуда:
Сообщений: 747
Roust_m
Но поскольку секционирование в нашем случае нужно (IMHO), то приодится искать компромисы.

Бред.
Сам себе не может объяснить: нахера ему секционирование.
А туда же.
Создает трудности и героически их преодолевает.
8 окт 19, 18:08    [21989748]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1049
Yasha123
а какой у вас объем таблицы в гигабайтах?


На данный момент, пока все еще в зачатаочном состоянии 10ГБ, но в среднесрочтной перспективе 1.5ТБ
9 окт 19, 01:55    [21989965]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Roust_m
Member

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

План со сканом секции банально дешевле плана с поиском в секции индекса.
Стоимость плана с несекционированным индексом аналогична плану с секционированным.

Можете ради интереса попробовать
select * from mypartitionedtable 
where sid = ... and pkey = ...
option (maxdop 1, use hint('FORCE_LEGACY_CARDINALITY_ESTIMATION'))


Учитывая, что у вас бета 2019-го сервера, не вижу смысла в дальнейших разбирательствах.


Это Azure, я так понимаю, что Майкрософт тестирует на своих клиентах новые версии сиквела. У мне контроля над версией нет.
9 окт 19, 01:57    [21989967]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Гавриленко Сергей Алексеевич
Member

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

"Все татары кроме я." (с)

Вы можете понятнее выражаться?
Особенно на тему у кого там Azure случился?
И очень интересно, когда вы сообщаете, что у вас контроля над версией нет. Вы уже готовитесь к тому, что вам MS SQL 4.2 раскатают? Или 7.0?
9 окт 19, 02:13    [21989968]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36450
aleks222
Не знаю, как там у антиподов, в австралии.
А в посконной завсегда поиск по ДВУМ полям ключа дольше, чем по одномую
Aleks, я вот даже не знаю. Поиск по двум полям ключа индекса тупо быстрее, потому что поиск по одному, первому, полю ключа того того же индекса в лучшем случае будет плюс-минус так же, а в худшем приведет к Range-скану. А уж если мы говорим про разные индексы, то по тормознутости будет весьма сложно уделать поиск по индексу с одним ключом в 900 байт.
9 окт 19, 02:26    [21989969]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1049
aleks222
Roust_m
Но поскольку секционирование в нашем случае нужно (IMHO), то приодится искать компромисы.

Бред.
Сам себе не может объяснить: нахера ему секционирование.
А туда же.
Создает трудности и героически их преодолевает.


Ну хорошо, как тогда в терабайтной таблице данные искать?
9 окт 19, 02:34    [21989970]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36450
Roust_m
Ну хорошо, как тогда в терабайтной таблице данные искать?
Физическим оператором Index Seek. Для одного поиска вы запаритесь делать таблицу, для которой понадобится больше 7-8 чтений. Упомянутые мною выше индексы с 900-байтовыми ключами оставим за скобками.
9 окт 19, 02:36    [21989972]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1049
Гавриленко Сергей Алексеевич
Roust_m,

"Все татары кроме я." (с)

Вы можете понятнее выражаться?
Особенно на тему у кого там Azure случился?
И очень интересно, когда вы сообщаете, что у вас контроля над версией нет. Вы уже готовитесь к тому, что вам MS SQL 4.2 раскатают? Или 7.0?


В нашей среде, если нужна база, то отвечающий за это человек заходит на Azure портал и нажимает несколько кнопок, указывая какого размера и производительности нужна база. Выбора версии там нет. Старых версий у нас не раскатают, наоборот, в такой среде Майкрософт использует самую новую версию, даже если она не вышла еще на рынок. Вы разве про это не знаете? Да, они на клиентах как на хомячках обкатывают новую версию, зато потом, когда она готова, получается гораздо стабильнее.
Я только что сам туда зашел, кнопки выбрать версию (2016 к примеру) не увидел.
9 окт 19, 04:02    [21989979]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29246
Roust_m
Ну хорошо, как тогда в терабайтной таблице данные искать?
Какая разница, сколько байт в таблице, если вам нужно считать сектор №123321?
9 окт 19, 07:31    [21989990]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29246
Roust_m
alexeyvg
Вот у вас есть индекс, там указано, что ваши данные находятся на диске в секторе № 123321
Сервер читает сектор 123321, готово!

Далее, вы задаёте вопрос: "как можно сервер заставить сканировать одну партицию"

Вы понимаете, что такой вопрос абсурден, в контексте задачи "прочитать сектор 123321"?

Ну контекст задачи немного шире. Еще одной целью партицирования было собрать все записи по одному и тому же студенту в одной партиции, вместо того, чтобы им быть разбросанными по всей таблице. У одного студента может быть несколько sid. Но pkey есть функция от id студента (st_id). В приложении этот запрос не единственный, поэтому задача заставить это запрос работать на секционированной таблице как можно быстрее. Это на не секционрованной таблице по этому запросу есть индекс где указано, что данные находятся в секторе № 123321. А на секционированной так не получется. Но поскольку секционирование в нашем случае нужно (IMHO), то приодится искать компромисы.
Между тем, что все записи по одному и тому же студенту в одной партиции, или они разбросаны по всей таблице, нет никакой разницы.

У вас всего лишь 3 варианта:

1) Вы делаете так, что бы все данные запроса читались в одном блоке (как - вам уже писали).
Тогда неважно, в одной партиции у вас данные, или в нескольких. Вы читаете их одной дисковой операцией.

2) Данные разбросаны по разным секторам, и для получения данных вам нужно прочитать N секторов из некоего набора (№123321, №765567, №444333, .........).
В этом случае тоже неважно, в одной партиции у вас данные, или в нескольких. Вы просто читаете с диска сектора из списка. Если данные в одной партиции, то сектора будут поближе друг к другу, ну и что?

3) У вас нет списка секторов. Для получения данных вы должны просканировать фрагмент таблицы по какому то условию, и потом их отфильтровать.
Да, в этом случае, как одно из решений, можно использовать секционирование.
Но можно использовать и другие варианты, никакого преимущества секционирования перед ними нет (за исключением неких тонкостей, но глупо, не зная их, цепляться за секционарование).
Однако, тут (вариант 3) важно понимать, что такое сканирование (пусть даже одной секции) - признак грубейшей ошибки проектирования, которая сделает невозможным практическое использование базы данных (почти всегда, хотя есть и исключения).
9 окт 19, 07:44    [21989996]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к партицированной таблице и к обычной таблице  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2300
Roust_m
Вот скрипт партицированной таблицы. Там 15000 партиций. Ключ pkey есть функция от st_id: st_id % 14999 +1
Сорри, не стал читать весь топик, но это самое идиотское секционирование которое я когда либо видел в жизни.
10 окт 19, 22:45    [21991741]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2 3      [все]
Все форумы / Microsoft SQL Server Ответить