оптимизация запроса / Microsoft SQL Server / Sql.ru

**Из тестового задания.**

SELECT SUM(Skidka)
FROM TovarySkid AS T WITH(NOLOCK)
INNER JOIN Skid AS S WITH(NOLOCK)
        ON (T.Skd=S.Skd) AND (S.Data BETWEEN '01/01/19' AND '01/31/19')

CREATE INDEX idx ON Skid (Skd, Data)

/****** Object:  Table [dbo].[Skid]    Script Date: 12/19/2019 11:10:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Skid](
	[Skd] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[NomerSklada] [int] NULL,
	[Data] [smalldatetime] NULL,
 CONSTRAINT [AK_Password] PRIMARY KEY CLUSTERED 
(
	[Skd] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE NONCLUSTERED INDEX [Data] ON [dbo].[Skid] 
(
	[Data] 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 [PRIMARY]


CREATE NONCLUSTERED INDEX [NomerSklada] ON [dbo].[Skid] 
(
	[NomerSklada] 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 [PRIMARY]


CREATE NONCLUSTERED INDEX [Skd_Data] ON [dbo].[Skid] 
(
	[Skd] ASC,
	[Data] 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 [PRIMARY]

/****** Object:  Table [dbo].[TovarySkid]    Script Date: 12/19/2019 11:15:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TovarySkid](
	[CntAA] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[Skd] [int] NULL,
	[VidTovara] [int] NULL,
	[Skidka] [money] NULL,
	[KolShtSkd] [money] NULL,
	[Karta] [varchar](2) NULL,
	[VidAkc] [int] NULL,
	[Tip] [int] NULL,
	[KolKl] [int] NULL,
	[Cena] [money] NULL,
 CONSTRAINT [PK_TovarySkid] PRIMARY KEY CLUSTERED 
(
	[CntAA] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


CREATE NONCLUSTERED INDEX [Skd] ON [dbo].[TovarySkid] 
(
	[Skd] 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 [PRIMARY]


CREATE NONCLUSTERED INDEX [Skd_iVidTovara] ON [dbo].[TovarySkid] 
(
	[Skd] ASC
)
INCLUDE ( [VidTovara]) 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 [PRIMARY]


CREATE NONCLUSTERED INDEX [Tip] ON [dbo].[TovarySkid] 
(
	[Tip] 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 [PRIMARY]


CREATE NONCLUSTERED INDEX [VidAkc] ON [dbo].[TovarySkid] 
(
	[VidAkc] 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 [PRIMARY]


CREATE NONCLUSTERED INDEX [VidTovara] ON [dbo].[TovarySkid] 
(
	[VidTovara] 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 [PRIMARY]

ALTER TABLE dbo.Skid
   ADD CONSTRAINT FK_Skid FOREIGN KEY (Skd)
      REFERENCES dbo.TovarySkid (Skd)
      ON UPDATE CASCADE;

-- 1. Индекс на dbo.TovarySkid 
CREATE NONCLUSTERED INDEX X_skd ON dbo.TovarySkid(Skd) INCLUDE (Skidka);
go
-- индекс на dbo.Skid
CREATE NONCLUSTERED INDEX X_data ON dbo.Skid(Data) INCLUDE(Skd);
go
-- решит проблему "чтобы он работал на этих данных за указанный период менее минуты"
with t as ( select Skd, Skidka = sum(Skidka) from dbo.TovarySkid where Skidka is not null group by Skd )
   , s as ( select * from dbo.Skid where Data BETWEEN '20190101' AND '20190131' )
 SELECT SUM(t.Skidka)
   FROM s INNER JOIN t ON (t.Skd=s.Skd) 

-- 2. Индексированное View
alter view dbo.TovarySkid_Skd_Skidka
with schemabinding
as
select Skd, Skidka = sum(Skidka), count_big(*) as cnt from dbo.TovarySkid where Skidka is not null group by Skd;
go
create unique clustered index CIX_TovarySkid_Skd_Skidka on dbo.TovarySkid_Skd_Skidka(Skd);
go

--решит проблему "чтобы он работал на этих данных менее минуты за любой период"
with t as ( select * from dbo.TovarySkid_Skd_Skidka with(noexpand))
   , s as ( select * from dbo.Skid where Data BETWEEN '20190101' AND '20190131' )
 SELECT SUM(t.Skidka)
   FROM s INNER JOIN t ON (t.Skd=s.Skd) 

With s as (SELECT * FROM dbo.Skid WHERE Skid.Data BETWEEN '20190901' AND '20190931')
SELECT SUM(Skidka) as Скидка
FROM TovarySkid
WHERE EXISTS (SELECT Skd FROM S WHERE TovarySkid.Skd=S.Skd)

оптимизация запроса [new]
dab2 Member Откуда: Воронеж Сообщений: 776	Из тестового задания. Приведенный ниже запрос работает несколько минут. SELECT SUM(Skidka) FROM TovarySkid AS T WITH(NOLOCK) INNER JOIN Skid AS S WITH(NOLOCK) ON (T.Skd=S.Skd) AND (S.Data BETWEEN '01/01/19' AND '01/31/19') Поля TovarySkid.Skd и Skid.Skd индексированы кластерным индексом, Skid.Data проиндексированы некластеризованным индексом. Задача минимум: Написать этот запрос так, чтобы он работал на этих данных за указанный период менее минуты. Предложите, как нужно проиндексировать эти данные дополнительно, чтобы выполнить поставленную задачу? Задача максимум: Написать этот запрос так, чтобы он работал на этих данных менее минуты за любой период. Насколько я понимаю, конструкцию ON ... AND надо заменить на On ... Where. ============================================================================================================ "О, сколько нам открытий чудных готовит просвещения дух, и опыт - сын ошибок трудных, и гений - парадоксов друг, и случай - бог изобретатель" (Пушкин, однако).
18 дек 19, 14:06 [22043156] Ответить \| Цитировать Сообщить модератору

Re: оптимизация запроса [new]
iiyama Member Откуда: Сообщений: 642	dab2, и где план запроса?
18 дек 19, 14:10 [22043163] Ответить \| Цитировать Сообщить модератору

Re: оптимизация запроса [new]
dab2 Member Откуда: Воронеж Сообщений: 776	iiyama, план пришлю немного погодя, если удастся получить (база не у меня).
18 дек 19, 14:17 [22043171] Ответить \| Цитировать Сообщить модератору

Re: оптимизация запроса [new]
dab2 Member Откуда: Воронеж Сообщений: 776	Akina, индекс Skid (Skd, Data) не помог, выполнение больше 3 минут.
18 дек 19, 14:59 [22043240] Ответить \| Цитировать Сообщить модератору

Re: оптимизация запроса [new]
dab2 Member Откуда: Воронеж Сообщений: 776	Ещё была мысль создать внешний ключ ALTER TABLE dbo.Skid ADD CONSTRAINT FK_Skid FOREIGN KEY (Skd) REFERENCES dbo.TovarySkid (Skd) ON UPDATE CASCADE; Но прав пока нет. Или как-то запрос переделать всё же надо?
20 дек 19, 04:47 [22044852] Ответить \| Цитировать Сообщить модератору

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

Re: оптимизация запроса [new]
dab2 Member Откуда: Воронеж Сообщений: 776	Вот этот сработал за 2 с! With s as (SELECT * FROM dbo.Skid WHERE Skid.Data BETWEEN '20190901' AND '20190931') SELECT SUM(Skidka) as Скидка FROM TovarySkid WHERE EXISTS (SELECT Skd FROM S WHERE TovarySkid.Skd=S.Skd)
21 дек 19, 02:06 [22045757] Ответить \| Цитировать Сообщить модератору

Re: оптимизация запроса [new]
dab2 Member Откуда: Воронеж Сообщений: 776	With s as (SELECT * FROM dbo.Skid WHERE Skid.Data BETWEEN '20190901' AND '20190931') SELECT SUM(Skidka) as Скидка FROM TovarySkid WHERE EXISTS (SELECT Skd FROM S WHERE TovarySkid.Skd=S.Skd)
21 дек 19, 02:06 [22045758] Ответить \| Цитировать Сообщить модератору