Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
Nimua Member Откуда: Ростов-на-Дону Сообщений: 344 |
Привет! Вкратце о том, что выбирается - это отчет по действиям юзера из архивной базы. В нее собираются данные с нескольких серверов - каждый сервер льет данные в свою таблицу. Эти таблицы секционированы по дате по месяцу. Все эти таблицы объединены во view. Ключ секционирования присутствует в каждом индексе таблицы - последним полем. При написании запроса предполагалось, что сервер будет фильтровать по дате и NodeId. Однако фильтр используется только по дате при этом происходит index scan, если перестроить индекс и поставить дату в начало, будет происходить index seek но также без учета NodeId - что грустно. В случае теста во временной таблице 840 записей, а в основной таблице 729 млн. Вот схема и план USE [DBArchive] GO /****** Object: Table [dbo].[DirectoryUserAction_Archive_2_Partition] Script Date: 12.07.2017 20:33:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DirectoryUserAction_Archive_2_Partition]( [EventId] [bigint] NOT NULL, [NodeId] [int] NOT NULL, [EventDateUtc] [datetime] NOT NULL, [EventTypeId] [tinyint] NOT NULL, [DirId] [bigint] NULL, [FileName] [nvarchar](900) NULL, [FileId] [bigint] NULL, [FileLength] [bigint] NULL, [ParentId] [bigint] NULL, [OldParentId] [bigint] NULL, [OldName] [nvarchar](1800) NULL, CONSTRAINT [PK_DirectoryUserAction_Archive_2_Partition] PRIMARY KEY NONCLUSTERED ( [EventId] ASC, [EventDateUtc] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ) ON [schmUserActionMonthlyPartition]([EventDateUtc]) GO /****** Object: Index [IX_DirectoryUserAction_Archive_2_Partition_EventDate] Script Date: 12.07.2017 20:33:18 ******/ CREATE CLUSTERED INDEX [IX_DirectoryUserAction_Archive_2_Partition_EventDate] ON [dbo].[DirectoryUserAction_Archive_2_Partition] ( [NodeId] ASC, [EventDateUtc] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [schmUserActionMonthlyPartition]([EventDateUtc]) GO /****** Object: Index [IX_DirectoryUserAction_Archive_2_Partition_NodeId_EventId] Script Date: 12.07.2017 20:33:18 ******/ CREATE UNIQUE NONCLUSTERED INDEX [IX_DirectoryUserAction_Archive_2_Partition_NodeId_EventId] ON [dbo].[DirectoryUserAction_Archive_2_Partition] ( [NodeId] ASC, [EventId] ASC, [EventDateUtc] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [schmUserActionMonthlyPartition]([EventDateUtc]) GO /****** Object: Index [IX_DirectoryUserAction_Archive_2_Partition_Type_EventDate] Script Date: 12.07.2017 20:33:18 ******/ CREATE NONCLUSTERED INDEX [IX_DirectoryUserAction_Archive_2_Partition_Type_EventDate] ON [dbo].[DirectoryUserAction_Archive_2_Partition] ( [NodeId] ASC, [EventTypeId] ASC, [EventDateUtc] ASC ) INCLUDE ( [DataFileId], [DirId], [EventId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [schmUserActionMonthlyPartition]([EventDateUtc]) GO CREATE TABLE [dbo].[DirectoryUserActionEvent_Archive_2_Partition]( [DirectoryId] [int] NOT NULL, [EventId] [bigint] NOT NULL, [EventDateUtc] [datetime] NOT NULL, [DirectoryEventTypeId] [tinyint] NULL, CONSTRAINT [PK_DirectoryUserActionEvent_Archive_2_Partition] PRIMARY KEY CLUSTERED ( [DirectoryId] ASC, [EventId] ASC, [EventDateUtc] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ) ON [schmUserActionMonthlyPartition]([EventDateUtc]) GO /****** Object: Index [IX_DirectoryUserActionEvent_Archive_2_Partition_EventId] Script Date: 12.07.2017 20:34:22 ******/ CREATE NONCLUSTERED INDEX [IX_DirectoryUserActionEvent_Archive_2_Partition_EventId] ON [dbo].[DirectoryUserActionEvent_Archive_2_Partition] ( [EventId] ASC, [EventDateUtc] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [schmUserActionMonthlyPartition]([EventDateUtc]) CREATE VIEW [dbo].[vwDirectoryUserActionArchive] AS SELECT *, 1 AS serverId FROM dbo.DirectoryUserAction_Archive_1_Partition UNION ALL SELECT *, 2 AS serverId FROM dbo.DirectoryUserAction_Archive_2_Partition UNION ALL SELECT *, 3 AS serverId FROM dbo.DirectoryUserAction_Archive_3_Partition GO CREATE VIEW [dbo].[vwDirectoryUserActionEventArchive] AS SELECT *, 1 AS serverId FROM dbo.DirectoryUserActionEvent_Archive_1_Partition UNION ALL SELECT *, 2 AS serverId FROM dbo.DirectoryUserActionEvent_Archive_2_Partition UNION ALL SELECT *, 3 AS serverId FROM dbo.DirectoryUserActionEvent_Archive_3_Partition GO Сам запрос DECLARE @ldateBeginUtc DATETIME, @ldateEndUtc DATETIME, @lserverId INT, @UserDirectoryList VARCHAR(MAX) set @ldateBeginUtc = '20160401' set @ldateEndUtc = '20160701' set @lserverId = 2 set @UserDirectoryList = '<S DirectoryId="3503336" NodeId="3503336"/><S DirectoryId="3503337" NodeId="3503337"/><S DirectoryId="3503341" NodeId="3503341"/>' DECLARE @UserDirectoryListXml XML = CAST(@UserDirectoryList AS XML); CREATE TABLE #tmp_UserDirectorys_sd ( DirectoryId INT, [NodeId] INT ); INSERT INTO #tmp_UserDirectorys_sd (DirectoryId, [NodeId]) SELECT t.S.value('@DirectoryId','INT'), t.S.value('@NodeId','INT') FROM @UserDirectoryListXml.nodes('/S') AS t(S); select * from #tmp_UserDirectorys_sd SELECT NF.EventId, NF.SourceUserId, NE.DirectoryId, NF.DataFileId, NF.EventDateUtc, NF.[NodeId], NF.[FileName], NF.ParentId, NF.OldParentId FROM [dbo].[vwDirectoryUserActionArchive] NF WITH (NOLOCK) INNER JOIN #tmp_UserDirectorys_sd VF ON (VF.[NodeId] = NF.[NodeId]) INNER JOIN dbo.vwDirectoryUserActionEventArchive NE WITH (NOLOCK) ON (NE.EventId = NF.EventId AND NE.EventDateUtc = NF.EventDateUtc AND NE.DirectoryId = VF.DirectoryId) WHERE NF.EventDateUtc BETWEEN @ldateBeginUtc AND @ldateEndUtc AND NF.serverId = @lserverId AND NE.serverId = @lserverId OPTION (RECOMPILE) drop table #tmp_UserDirectorys_sd К сообщению приложен файл (plan.zip - 24Kb) cкачать ![]() |
13 июл 17, 11:28 [20639968] Ответить | Цитировать Сообщить модератору |
Добрый Э - Эх
Guest |
|
||
13 июл 17, 11:37 [20640006] Ответить | Цитировать Сообщить модератору |
Nimua Member Откуда: Ростов-на-Дону Сообщений: 344 |
Добрый Э - Эх, В теории можно было и так, было бы даже удобнее это все сливать, так как была бы одна таблица. |
13 июл 17, 11:52 [20640087] Ответить | Цитировать Сообщить модератору |
Nimua Member Откуда: Ростов-на-Дону Сообщений: 344 |
Про таблицу - я не могу переделать таблицы сейчас, но могу изменить сам запрос или индексы на таблице Поменяла temporary table на table variable и добавила в Where фильтр по дате и для event'ов тоже вот что получилось - теперь он делает seek на UserAction, но сканит event'ы К сообщению приложен файл (report audit index seek on ua and index scan on event v2.zip - 12Kb) cкачать ![]() |
13 июл 17, 12:20 [20640210] Ответить | Цитировать Сообщить модератору |
Remind Member Откуда: UK Сообщений: 523 |
А с чего вы взяли, что Seek будет лучше Scan'a в данном случае и оптимизатор должен выбрать его? Можете попробовать зафорсить Seek и Nested Loops заменив join на apply. |
||
13 июл 17, 12:45 [20640325] Ответить | Цитировать Сообщить модератору |
Remind Member Откуда: UK Сообщений: 523 |
Кстати лишнюю партицию не сканите случайно? |
||
13 июл 17, 13:02 [20640395] Ответить | Цитировать Сообщить модератору |
VladimirSQL Member Откуда: Киев Сообщений: 37 |
Вероятно предполагается что данные упорядочены их их не нужно сканировать, а достаточно просто провести поиск по ним (seek). От которого конечно бы хотелось получить меньшую ресурсоемкость и соответсвенно меньшее конечно время выполнения. Это если опираться на классику: https://www.sql.ru/articles/mssql/2006/102501scansvsseeks.shtml А в каком случае Scan может быть лучше Seek при работе с индексом? |
||||
13 июл 17, 13:50 [20640620] Ответить | Цитировать Сообщить модератору |
Владислав Колосов Member Откуда: Сообщений: 8484 |
VladimirSQL, При сравнимых объемах данных в обработке разных источников. |
13 июл 17, 13:58 [20640665] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9683 |
|
||||||
13 июл 17, 14:40 [20640863] Ответить | Цитировать Сообщить модератору |
Nimua Member Откуда: Ростов-на-Дону Сообщений: 344 |
Владислав Колосов, В таблице UserAction порядка 800 млн записей, в таблице с event - немного больше (где-то на 30 млн) в итоге в результате запроса возвращается чуть больше 1 млн записей. to Remind, Да, вы правы - в реальности граничная дата будет нормальной, то есть '2016-06-30 23:59:59.997' |
13 июл 17, 14:40 [20640866] Ответить | Цитировать Сообщить модератору |
Remind Member Откуда: UK Сообщений: 523 |
Я бы все-таки назвал это scan'ом только нужных секций. |
||
13 июл 17, 16:16 [20641503] Ответить | Цитировать Сообщить модератору |
aleks2
Guest |
Я фсегда поражался тупости адептов секционирования. Мало того, что они ожидают от чудесного ускорения фсего от самого факта "секционирования", дык оне еще и читать ленятся. Толком даже секционировнное View сделать не могут. https://technet.microsoft.com/en-us/library/ms190019(v=sql.105).aspx |
13 июл 17, 16:19 [20641522] Ответить | Цитировать Сообщить модератору |
Nimua Member Откуда: Ростов-на-Дону Сообщений: 344 |
Привет! В итоге прибили гвоздями - добавили хинт LOOP JOIN, в плане стал использовать nested loops. |
4 сен 17, 12:36 [20769455] Ответить | Цитировать Сообщить модератору |
aleks222
Guest |
Все в восторге!!! (с) Кот Бегемот. ЗЫ. А как же |
||
4 сен 17, 14:21 [20769868] Ответить | Цитировать Сообщить модератору |
dies irae Member Откуда: Сообщений: 78 |
а если так?CREATE TABLE #tmp_UserDirectorys_sd ( DirectoryId INT, [NodeId] INT primary key clustered(NodeId, DirectoryId) ); |
4 сен 17, 15:54 [20770214] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |