Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Оптимизация запроса  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35381
Блог
Коллеги,

Что можете предложить для оптимизации запроса?

Скрипт для заполнения тестовыми данными и сам тестовый запрос:
+

use tempdb
go

/******** Блок заполнения ************************************************************************************/

IF NOT EXISTS (SELECT  schema_name FROM INFORMATION_SCHEMA.SCHEMATA WHERE schema_name = 'buf' ) 
EXEC sp_executesql N'CREATE SCHEMA buf'


CREATE PARTITION FUNCTION [ACTIVE](bit) AS RANGE LEFT FOR VALUES (0)
GO
CREATE PARTITION SCHEME [ACTIVE] AS PARTITION [ACTIVE] TO ([PRIMARY], [PRIMARY])
GO


IF OBJECT_ID('buf.DIC_TEST', 'U') IS NULL
BEGIN
CREATE TABLE buf.DIC_TEST
(
	[SOURCE_SYSTEM] int not null,
	[SOURCE_KEY] int not null,
	[ATTR] varchar(200)
)

CREATE CLUSTERED INDEX [IX] ON [buf].[DIC_TEST] ([SOURCE_KEY] ASC, [SOURCE_SYSTEM] ASC)


insert into [buf].[DIC_TEST]
select 1,	21,	'апельсин' union all
select 1,	22,	'яблоко Гала' union all
select 1,	23,	'вишня 5' union all
select 2,	21,	'слива' union all
select 2,	22,	'яблоко (антоновка)' union all
select 2,	23,	'вишня 5' union all
select 2,	24,	'апельсин Марокко' union all
select 2,	25,	'апельсин'
set nocount on
DECLARE @i int = 5000000
WHILE @i > 0
BEGIN
  INSERT INTO [buf].[DIC_TEST]([SOURCE_SYSTEM],[SOURCE_KEY],[ATTR])
  VALUES ((@i/3)+5, @i, CONVERT(varchar(200),NEWID()))
  SET @i = @i - 1
END
set nocount off
END



IF OBJECT_ID('dbo.DIC_TEST_LINK_HISTORY', 'U') IS NULL
CREATE TABLE [dbo].[DIC_TEST_LINK_HISTORY](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[SOURCE_SYSTEM] [int] NOT NULL,
	[SOURCE_KEY] [int] NOT NULL,
	[ATTR] [varchar](200) NULL,
	[VALID_FROM] [date] NULL,
	[VALID_TO] [date] NULL,
	[ACTIVE] [bit] NULL,
	[DATE_CHANGE] [datetime2](3) NULL,
    CONSTRAINT [PK_DIC_TEST_LINK_HISTORY] PRIMARY KEY NONCLUSTERED ([ID]) ON [PRIMARY]
) ON [ACTIVE]([ACTIVE])

CREATE CLUSTERED INDEX [IX] ON [dbo].[DIC_TEST_LINK_HISTORY]
([SOURCE_KEY] ASC,[SOURCE_SYSTEM] ASC) ON [ACTIVE]([ACTIVE])



/*
update [dbo].[DIC_TEST_LINK_HISTORY]
  set ACTIVE = 0, VALID_TO = getdate()-1, DATE_CHANGE = getdate()-1
  where not (SOURCE_SYSTEM = 1 AND SOURCE_KEY = 21) and ACTIVE = 1
*/
go
create view [dbo].[v_DIC_TEST_LINK_HISTORY]
as 
select * from [dbo].[DIC_TEST_LINK_HISTORY]
 where $PARTITION.ACTIVE(ACTIVE) = 2
go



/******** Блок тестирования ************************************************************************************/
update [buf].[DIC_TEST]
  set ATTR = 'апельсин ' + convert(varchar(20),RAND())
  where SOURCE_SYSTEM	= 1 AND SOURCE_KEY = 21

DECLARE @d datetime2(3) = getdate()+(select count(*) from [dbo].[DIC_TEST_LINK_HISTORY] where SOURCE_SYSTEM = 1 AND SOURCE_KEY = 21)

INSERT INTO [dbo].[DIC_TEST_LINK_HISTORY] ([SOURCE_SYSTEM],[SOURCE_KEY],[ATTR],[VALID_FROM],[VALID_TO],[ACTIVE],[DATE_CHANGE])
SELECT [SOURCE_SYSTEM], [SOURCE_KEY], [ATTR],
       CONVERT(date, @d)         as [VALID_FROM],
       CONVERT(date, '99991231') as [VALID_TO],
       CONVERT(bit, 1)           as [ACTIVE],
       @d                        as [DATA_CHANGE]
  FROM (
        MERGE [dbo].[v_DIC_TEST_LINK_HISTORY] AS target
        USING [buf].[DIC_TEST] AS source ON target.SOURCE_SYSTEM = source.SOURCE_SYSTEM AND
	                                        target.SOURCE_KEY    = source.SOURCE_KEY
                                            --AND target.ACTIVE = 1 -- заменено на представление, так намного быстрее
        -- 1) если (активная!) запись изменена в первоисточнике, то помечаем тут
        --    эту запись как неактуальную и (выше) вставляем новую запись во внешнем INSERT,
        --    также, если это сегодняшняя запись, то у нее правим атрибуты и дату изменения,
        --    а все остальное не трогаем
        WHEN MATCHED AND --(target.ATTR <> source.ATTR)
             EXISTS (SELECT Source.ATTR
                     EXCEPT
                     SELECT Target.ATTR)
             THEN UPDATE SET [ACTIVE] = CASE
                                         WHEN [VALID_FROM] = CONVERT(date, @d) THEN target.[ACTIVE]
                                         ELSE 0
                                       END,
                            [VALID_TO] = CASE
                                           WHEN [VALID_FROM] = CONVERT(date, @d) THEN target.[VALID_TO]
                                           ELSE DATEADD(dd, -1, @d)
                                         END,
                            [ATTR] = CASE
                                       WHEN [VALID_FROM] = CONVERT(date, @d) THEN source.[ATTR]
                                       ELSE target.ATTR
                                     END,
	    				    [DATE_CHANGE] = @d
        -- 2) если записи в приемнике нет, то вставляем новую
        WHEN NOT MATCHED BY TARGET
          THEN INSERT ([SOURCE_SYSTEM],[SOURCE_KEY],[ATTR],[DATE_CHANGE],[VALID_FROM],[VALID_TO],[ACTIVE])
               VALUES (source.[SOURCE_SYSTEM],
                       source.[SOURCE_KEY],
                       source.[ATTR],
                       @d,                        -- [DATA_CHANGE]
	     			  CONVERT(date, @d),         -- [VALID_FROM]
                       CONVERT(date, '99991231'), -- [VALID_TO]
				       CONVERT(bit, 1))           -- [ACTIVE]
        -- 3) если запись удалена в первоисточнике, то помечаем старую как неактуальную 
        WHEN NOT MATCHED BY SOURCE AND target.[ACTIVE] = 1
          THEN UPDATE SET [ACTIVE]      = CONVERT(bit, 0),
                          [VALID_TO]    = CONVERT(date, @d),
                          [DATE_CHANGE] = @d
        OUTPUT source.*,
               Deleted.[VALID_FROM] as [VALID_FROM_OLD],
               $action              as [ACTION]
  ) AS MergeOutput
  WHERE MergeOutput.[ACTION] = 'UPDATE' AND
        -- если скрипт выполняется в один и тот же день, то [VALID_FROM] останется без изменений и для старой записи,
        -- и для новой, такие записи не нужно вставлять (и нужно обновлять в п1),
        -- новую же запись вставляем только при различии [VALID_FROM] (то есть, если изменение атрибутов произошло в разные дни),
        -- поэтому добавляем такой фильтр
        MergeOutput.[VALID_FROM_OLD] <> CONVERT(date, @d)
  OPTION (RECOMPILE)


План в приложенном файле.

К сообщению приложен файл (plan.7z - 15Kb) cкачать
13 сен 15, 19:07    [18144391]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
aleks2
Guest
Дык, не запрашивать - самое оптимальное.

ЗЫ. Чего тебя, страдалец, не устраивает то?
14 сен 15, 12:27    [18146120]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35381
Блог
aleks2
Дык, не запрашивать - самое оптимальное.

ЗЫ. Чего тебя, страдалец, не устраивает то?


хочу быстрее
14 сен 15, 12:43    [18146227]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Критик,

У меня на тестовой машине не получается из предложенного репро такой же план как у вас. Может быть дело в версии, у меня на тестовой машине 12.0.2495.0, у вас 12.0.4422.0, может еще в чем-то.
У меня получается план с Hash Match, который сливает данные на диск, у вас там Merge Join (many-to-many).

Есть ли возможность избавиться от many-to-many создав уникальный кластерный индекс (CREATE UNIQUE CLUSTERED INDEX [IX] ON [buf].[DIC_TEST] ([SOURCE_KEY] ASC, [SOURCE_SYSTEM] ASC))?

У меня время без уникального индекса 84 секунды, с уникальным индексом 15 секунд, т.е. выигрыш в 6 раз примерно...

Также можно попробовать разделить запрос на insert/update, но времени на такие эксперименты у меня сейчас, к сожалению, нет.
14 сен 15, 13:47    [18146644]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35381
Блог
На hash у меня поменялся после построения кластерного колоночного индекса. Но все равно с предупреждением "Operator used tempdb to spill data during execution with spill level 1". Также меняется на hash, если нет индексов на источнике. Время выполнения не изменилось.

Видимо действительно нужно попробовать разделить на операции.
14 сен 15, 22:46    [18148981]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
aleks2
Guest
Критик
aleks2
Дык, не запрашивать - самое оптимальное.

ЗЫ. Чего тебя, страдалец, не устраивает то?


хочу быстрее

Ну дык, надож постараться. А что ты сделал?

1. Шо за хрень?
CREATE CLUSTERED INDEX [IX] ON [buf].[DIC_TEST] ([SOURCE_KEY] ASC, [SOURCE_SYSTEM] ASC)


все равно двух одинаковых быть не должно
CREATE UNIQUE CLUSTERED INDEX [IX] ON [buf].[DIC_TEST] ([SOURCE_KEY] ASC, [SOURCE_SYSTEM] ASC)


2. Ну и как уже сказали
CREATE CLUSTERED INDEX [IX] ON [dbo].[DIC_TEST_LINK_HISTORY]
([SOURCE_KEY] ASC,[SOURCE_SYSTEM] ASC) ON [ACTIVE]([ACTIVE])


нужно тоже UNIQUE
CREATE UNIQUE INDEX [IX1] ON [dbo].[DIC_TEST_LINK_HISTORY]
([SOURCE_KEY] ASC,[SOURCE_SYSTEM] ASC) where [ACTIVE] = 1
15 сен 15, 06:08    [18149288]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить