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

Откуда: Зеленоград, Москва, Россия
Сообщений: 20484
Skidka - из какой таблицы?
CREATE INDEX idx ON Skid (Skd, Data)
- должно помочь...
dab2
конструкцию ON ... AND надо заменить на On ... Where
"Можно", а не "надо". Только сие ни на что не влияет.
18 дек 19, 14:11    [22043164]     Ответить | Цитировать Сообщить модератору
 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]
invm
Member

Откуда: Москва
Сообщений: 9270
dab2
индекс Skid (Skd, Data) не помог, выполнение больше 3 минут.
И не должен был
Нужно (Data, Skd) include (Skidka) или (Data) include (Skd, Skidka), если Skidka в таблице Skid.

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

Сообщение было отредактировано: 18 дек 19, 15:24
18 дек 19, 15:22    [22043278]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
dab2
Member

Откуда: Воронеж
Сообщений: 776
Таблицы сконструированы так:
Skid
+
/****** 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]

TovarySkid
+
/****** 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]

По сути, SUM(TovarySkid.Skidka) - проиндексированное поле, а Skid.Data тоже индексировано.
Поля связи: Skid.Skd - ключевое поле, TovarySkid.Skd - индекс.
20 дек 19, 03:59    [22044843]     Ответить | Цитировать Сообщить модератору
 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]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
aleks222
Member

Откуда:
Сообщений: 919
dab2

Или как-то запрос переделать всё же надо?


-- 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) 


Сообщение было отредактировано: 20 дек 19, 06:25
20 дек 19, 06:17    [22044859]     Ответить | Цитировать Сообщить модератору
 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]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить