Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
Топик располагается на нескольких страницах: [1] 2 3 вперед Ctrl→ все |
Roust_m Member Откуда: Сидней Сообщений: 1078 |
Добрый день, Есть такой запрос: 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] Ответить | Цитировать Сообщить модератору |
msLex Member Откуда: Сообщений: 7115 |
потому что стоимость плана это не время исполнения. Не говоря уже, что это Estimated Покажите планы |
||
3 окт 19, 11:43 [21985671] Ответить | Цитировать Сообщить модератору |
Владислав Колосов Member Откуда: Сообщений: 7031 |
Roust_m, вы смотрите на предполагаемый план, смотрите на фактический. Сравните, например, объем просмотренных данных в том и ином случае. Например, для второй таблицы есть индекс по ID и вы получите seek, а в первой таблице индекса нет и получите просмотр секции. |
3 окт 19, 11:44 [21985677] Ответить | Цитировать Сообщить модератору |
Roust_m Member Откуда: Сидней Сообщений: 1078 |
Вот план по партицированной таблице. Ниже добавлю второй план по непартицированной По идее вся суть партицирования это зверски улучшить скорость чтения когда ты знаешь в какой партиции ищещь. А получается наоборот, улучшение в непартицированной таблице. К сообщению приложен файл. Размер - 96Kb |
4 окт 19, 02:41 [21986349] Ответить | Цитировать Сообщить модератору |
Roust_m Member Откуда: Сидней Сообщений: 1078 |
Вот непартицированная таблица К сообщению приложен файл. Размер - 141Kb |
4 окт 19, 02:41 [21986350] Ответить | Цитировать Сообщить модератору |
Гавриленко Сергей Алексеевич Member Откуда: Moscow Сообщений: 36507 |
Сообщение было отредактировано: 4 окт 19, 03:00 |
||
4 окт 19, 02:46 [21986351] Ответить | Цитировать Сообщить модератору |
Гавриленко Сергей Алексеевич Member Откуда: Moscow Сообщений: 36507 |
|
|
4 окт 19, 02:54 [21986353] Ответить | Цитировать Сообщить модератору |
Гавриленко Сергей Алексеевич Member Откуда: Moscow Сообщений: 36507 |
Сообщение было отредактировано: 4 окт 19, 03:20 |
|
4 окт 19, 03:15 [21986357] Ответить | Цитировать Сообщить модератору |
Roust_m Member Откуда: Сидней Сообщений: 1078 |
Вот скрипт партицированной таблицы. Там 15000 партиций. Ключ pkey есть функция от st_id: st_id % 14999 + 1CREATE 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] Ответить | Цитировать Сообщить модератору |
Roust_m Member Откуда: Сидней Сообщений: 1078 |
Вот непартицированная таблица: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] Ответить | Цитировать Сообщить модератору |
Гавриленко Сергей Алексеевич Member Откуда: Moscow Сообщений: 36507 |
У вас вторые некластерные индексы UX_mypartitionedtable / UX_my_not_partitionedtable вообще не идентичны если убрать ключ секционирования.
Сообщение было отредактировано: 4 окт 19, 03:38 |
4 окт 19, 03:35 [21986360] Ответить | Цитировать Сообщить модератору |
Roust_m Member Откуда: Сидней Сообщений: 1078 |
Вот запрос в партицированной таблице:select * from [dbo].[mypartitionedtable] where sid=140862 and pkey=13707 А это к непратицированной: select * from [tmp].[my_not_partitionedtable] where sid=131623 |
4 окт 19, 03:37 [21986361] Ответить | Цитировать Сообщить модератору |
Гавриленко Сергей Алексеевич Member Откуда: Moscow Сообщений: 36507 |
Удивительно, что хоть где-то index seek... Чтобы было быстро выбирать все поля по sid, надо сделать индекс по (sid) include (все остальные поля, кроме ключей кластерного индекса). А секционирование уберите. В вашем случае оно разве что могло бы быть похоже на борьбу с latch contention, которого, скорее всего, у вас и в помине нет. Рано еще оно вам. Сообщение было отредактировано: 4 окт 19, 03:49 |
4 окт 19, 03:43 [21986362] Ответить | Цитировать Сообщить модератору |
Roust_m Member Откуда: Сидней Сообщений: 1078 |
Хоть этот индекс и не используется, исправил, теперь непратицированная таблица выглядит так. (ниже). Непратицированная таблица по прежнему гораздо быстрее по времени. Щас зашишрую немного имена объектов и выложу планы. 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] Ответить | Цитировать Сообщить модератору |
Roust_m Member Откуда: Сидней Сообщений: 1078 |
Предполагалось, что таблица будет зверски расти и число одновременных пользователей тоже будет сильно расти. База в стадии дизайна, какие запросы будут к ней до конца неизвестно. Большая часть запросов герерится Джавой (там есть какая-то автоматическая приблуда для этого). В редких случаях, когда совсем никак эти запросы выносятся в хранимую процедуру, остальное автоматом, чтобы ускорить время разработки. Повлиять я на это не могу. Еще один момент, который учитывался при решении применить секционирование, это то, что большинство запросов используют st_id или он известен. Поскольку диапазон st_id около миллиона, то, создали pkey, который есть остаток от деления st_id на 14999 плюс 1. То есть предполагалось, что все данные для одного и того-же студента будут в одной партиции. На данный момент в партиции с pkey=13707 всего 353 записи, из которых выбирается 31 запись. В непартицированной таблице 5843430 записей, из которых выбирается 52. Я не понимаю, почему выбрать 31 запись из 353 занимает больше времени, чем выбрать 52 записи из почти 6-ти миллионов. Я теряю веру в технологический прогресс и вообще во все человечество. Решение убрать секционирование не очень простое, ибо это может затронуть другие запросы. |
||
4 окт 19, 04:16 [21986364] Ответить | Цитировать Сообщить модератору |
Roust_m Member Откуда: Сидней Сообщений: 1078 |
Разные запросы будут использовать разные поля, то есть придется создать индекс по sid и включить туда почти все поля в таблице. Если следовать этому подходу и для других запросов, то размер индексов будет огромен, что замедлит вставку данных в таблицу а также изменение данных. Палка о двух конца. |
||
4 окт 19, 04:40 [21986365] Ответить | Цитировать Сообщить модератору |
Roust_m Member Откуда: Сидней Сообщений: 1078 |
Вот зашифрованный план по партицированной таблице. Очень желательно найти способ сделать так, чтобы это работало быстро на партицированной таблице. Убирать партицирование будет означать проблемы в других местах. К сообщению приложен файл (partitioned_table_plan2.sqlplan - 24Kb) cкачать ![]() |
4 окт 19, 04:56 [21986367] Ответить | Цитировать Сообщить модератору |
Roust_m Member Откуда: Сидней Сообщений: 1078 |
В обеих таблицах есть индекс по ID (sid), в партицированной таблице он тоже партицирован, однако же в партицированной таблице такой индекс не используется и происходит скан по кластерному индексу. |
||
4 окт 19, 05:18 [21986372] Ответить | Цитировать Сообщить модератору |
Idol_111 Member Откуда: Сообщений: 585 |
как это знакомо, а вот теперь огребаем по полной. |
||
4 окт 19, 05:53 [21986374] Ответить | Цитировать Сообщить модератору |
StarikNavy Member Откуда: Москва Сообщений: 2301 |
Roust_m, партиционирование это в основном для удобства обслуживания, а не для ускорения запросов |
4 окт 19, 09:49 [21986441] Ответить | Цитировать Сообщить модератору |
Владислав Колосов Member Откуда: Сообщений: 7031 |
Roust_m, у вас на плане просмотр секционированной таблицы и поиск в индексе по несекционированной. Отсюда и разница. |
4 окт 19, 11:27 [21986549] Ответить | Цитировать Сообщить модератору |
Владислав Колосов Member Откуда: Сообщений: 7031 |
Roust_m, сканирование может происходить в результате несоблюдения приоритета типов или самих типов передаваемого в запрос параметра и аргумента функции секционирования. И да, секционирование не всегда имеет смысл создавать для сокращения времени запросов. Нельзя утверждать, что оно предназначено именно для обслуживания, его назначение MS как раз и определяет как "средство, позволяющее ускорить выполнения запросов при больших размерах таблиц" в учебных курсах, например. Однако, они не рассматривают подробно сценарии, применимые для таких практик, поэтому возникает ощущение, что так следует делать всегда на любых больших таблицах. |
4 окт 19, 11:36 [21986564] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 8885 |
select * from mypartitionedtable with (index = IX_mypartitionedtable_sid) where sid = ... and pkey = ... option (maxdop 1) ЗЫ: Анонимайзер планов есть в SentryOne Plan Explorer |
||
4 окт 19, 14:00 [21986745] Ответить | Цитировать Сообщить модератору |
aleks222 Member Откуда: Сообщений: 769 |
В управдомы вам, батенька, надо переквалифицироваться. Иначе бы знали, что выборка по уникальному индексу из индексированной таблицы от размеров таблицы, практически, не зависит. Или другими словами: бесполезно ожидать ускорения выборки от партиционирования. |
||
4 окт 19, 15:57 [21986876] Ответить | Цитировать Сообщить модератору |
TaPaK Member Откуда: Kiev Сообщений: 6719 |
aleks222,
деградацию так точно стоит ожидать |
||
4 окт 19, 15:58 [21986878] Ответить | Цитировать Сообщить модератору |
Топик располагается на нескольких страницах: [1] 2 3 вперед Ctrl→ все |
Все форумы / Microsoft SQL Server | ![]() |