ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / оптимизация запроса
11 сообщений из 11, страница 1 из 1
оптимизация запроса
    #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:06
| Ответить | Цитировать | Написать  
оптимизация запроса
    #39904779
iiyama
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iiyama
Участник
dab2,

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

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

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

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

А вообще, пока не покажете хотя бы структуру таблиц, предлагать варианты бессмысленно.
...
Рейтинг: 0 / 0
18.12.2019, 15:22
| Ответить | Цитировать | Написать  
оптимизация запроса
    #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, 03:59
| Ответить | Цитировать | Написать  
оптимизация запроса
    #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, 04:47
| Ответить | Цитировать | Написать  
оптимизация запроса
    #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
20.12.2019, 06:17
| Ответить | Цитировать | Написать  
оптимизация запроса
    #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
21.12.2019, 02:06
| Ответить | Цитировать | Написать  
11 сообщений из 11, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / оптимизация запроса
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=39905895&tid=1686739]:
 0ms
get settings:
 10ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 31ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 48ms
get tp. blocked users:
 1ms
others: 16ms
total:  142ms
созд. / загр.: 142ms
x
x
Закрыть


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