Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / оптимизация запроса / 11 сообщений из 11, страница 1 из 1
18.12.2019, 14:06
    #39904776
dab2
dab2
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
Из тестового задания.

Приведенный ниже запрос работает несколько минут.

Код: sql
1.
2.
3.
4.
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.

============================================================================================================
"О, сколько нам открытий чудных готовит просвещения дух, и опыт - сын ошибок трудных, и гений - парадоксов друг, и случай - бог изобретатель" (Пушкин, однако).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.12.2019, 14:10
    #39904779
iiyama
iiyama
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
dab2,

и где план запроса?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.12.2019, 14:11
    #39904780
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
Skidka - из какой таблицы?
Код: sql
1.
CREATE INDEX idx ON Skid (Skd, Data)

- должно помочь...
dab2
конструкцию ON ... AND надо заменить на On ... Where
"Можно", а не "надо". Только сие ни на что не влияет.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.12.2019, 14:17
    #39904783
dab2
dab2
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
iiyama,

план пришлю немного погодя, если удастся получить (база не у меня).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.12.2019, 14:59
    #39904825
dab2
dab2
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
Akina,

индекс Skid (Skd, Data) не помог, выполнение больше 3 минут.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.12.2019, 15:22
    #39904852
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
dab2
индекс Skid (Skd, Data) не помог, выполнение больше 3 минут.
И не должен был
Нужно (Data, Skd) include (Skidka) или (Data) include (Skd, Skidka), если Skidka в таблице Skid.

А вообще, пока не покажете хотя бы структуру таблиц, предлагать варианты бессмысленно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.12.2019, 03:59
    #39905890
dab2
dab2
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
Таблицы сконструированы так:
Skid
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
/****** 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
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
/****** 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 - индекс.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.12.2019, 04:47
    #39905895
dab2
dab2
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
Ещё была мысль создать внешний ключ
Код: sql
1.
2.
3.
4.
ALTER TABLE dbo.Skid
   ADD CONSTRAINT FK_Skid FOREIGN KEY (Skd)
      REFERENCES dbo.TovarySkid (Skd)
      ON UPDATE CASCADE;


Но прав пока нет.

Или как-то запрос переделать всё же надо?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.12.2019, 06:17
    #39905900
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
dab2

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


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
-- 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)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.12.2019, 02:06
    #39906428
dab2
dab2
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
Вот этот сработал за 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)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.12.2019, 02:06
    #39906429
dab2
dab2
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
Код: sql
1.
2.
3.
4.
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)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / оптимизация запроса / 11 сообщений из 11, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 525ms
Закрыть
start [/forum/topic.php?fid=46&mobile=1&tid=1686739]:
 0ms
get settings:
 10ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 91ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 45ms
get tp. blocked users:
 1ms
others: 341ms
total:  525ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]