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

Откуда:
Сообщений: 7
запрос из таблицы в которой примерно 1,2 млн записей
занимает неприличные 8 секнд

чувствую что можно сильно ускорить

от dbo.List избавляться не хочется

CREATE TYPE [dbo].[List] AS TABLE(
	[ID] [int] NOT NULL,
	PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)

CREATE TABLE [dbo].[Lots](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[PlatformID] [int] NULL,
	[CityID] [int] NULL,
	[StatusID] [int] NULL,
	[LotTypeID] [int] NULL,
	[Number] [nvarchar](100) NULL,
	[Name] [nvarchar](max) NULL,
	[PriceStart] [money] NULL,
	[DateStart] [datetime] NULL,
	[InsertDate] [datetime] NULL,
	[CountryID] [int] NULL,
	[RegionID] [int] NULL,
 CONSTRAINT [PK_Lots] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_Lots] UNIQUE NONCLUSTERED 
(
	[PlatformID] ASC,
	[Number] ASC,
	[NumberLot] 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

ALTER TABLE [dbo].[Lots]  WITH CHECK ADD  CONSTRAINT [FK_Lots_Cities] FOREIGN KEY([CityID])
REFERENCES [dbo].[Cities] ([ID])
GO

ALTER TABLE [dbo].[Lots] CHECK CONSTRAINT [FK_Lots_Cities]
GO

ALTER TABLE [dbo].[Lots]  WITH CHECK ADD  CONSTRAINT [FK_Lots_LotTypes] FOREIGN KEY([LotTypeID])
REFERENCES [dbo].[LotTypes] ([ID])
GO

ALTER TABLE [dbo].[Lots] CHECK CONSTRAINT [FK_Lots_LotTypes]
GO

ALTER TABLE [dbo].[Lots]  WITH CHECK ADD  CONSTRAINT [FK_Lots_Platforms] FOREIGN KEY([PlatformID])
REFERENCES [dbo].[Platforms] ([ID])
GO

ALTER TABLE [dbo].[Lots] CHECK CONSTRAINT [FK_Lots_Platforms]
GO

ALTER TABLE [dbo].[Lots]  WITH CHECK ADD  CONSTRAINT [FK_Lots_States] FOREIGN KEY([StatusID])
REFERENCES [dbo].[States] ([ID])
GO

ALTER TABLE [dbo].[Lots] CHECK CONSTRAINT [FK_Lots_States]
GO



сам запрос

declare @platforms dbo.List
insert into @platforms values(1)
insert into @platforms values(2)
...
insert into @platforms values(26)

declare @countries dbo.List

declare @regions dbo.List
insert into @regions values(244)

declare @cities dbo.List
insert into @cities values(2087)

declare @query nvarchar(2000) = N''
declare @greaterdate datetime = NULL
declare @smallerdate datetime 
declare @ungeocoded bit = 0
	
-- pre check
declare @all_cities bit = 0
if not exists(SELECT ID FROM @countries) AND
 not exists(SELECT ID FROM @regions) AND 
 not exists(SELECT ID FROM @cities)  
	set @all_cities = 1

		SELECT COUNT(*)
		FROM Lots
		WHERE
			PlatformID in (SELECT ID FROM @platforms) AND
			-- city (+unfound)
			(
				@all_cities = 1
				OR 
				(@ungeocoded = 1 AND CityID is NULL)
				OR
				CountryID in (SELECT ID FROM @countries)
				OR
				RegionID in (SELECT ID FROM @regions)
				OR
				CityID in (SELECT ID FROM @cities)
				
			) AND				
			-- dates
			(
				(
					(@greaterdate is NULL OR DateStart >= @greaterdate) AND
					(@smallerdate is NULL OR DateStart <= @smallerdate)
				) OR DateStart is NULL
			)
		
23 июн 11, 15:35    [10862747]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
SomewhereSomehow
Member

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

первое что приходит в голову, это избавиться от универсальности и для разных типов запросов, сделать разные ветки в коде, т.е. отделить когда нужно получать все, а когда по фильтрам, ну еще можно ин на экзистс на всякий случай заменить, но думаю оптимизатор и сам не дурак, так же можно добавить индекс по дате - первое что пришло в голову.
23 июн 11, 15:47    [10862906]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
ponemetski
Member

Откуда:
Сообщений: 7
еще есть вот такой индекс на этой таблице

CREATE NONCLUSTERED INDEX [IX_Lots3] ON [dbo].[Lots] 
(
	[PlatformID] ASC,
	[DateStart] ASC,
	[CityID] ASC,
	[CountryID] ASC,
	[RegionID] ASC
)
INCLUDE ( [ID],
[Name],
[PriceStart]

) 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]
GO
23 июн 11, 15:59    [10863041]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
ponemetski
Member

Откуда:
Сообщений: 7
SomewhereSomehow
ponemetski,

первое что приходит в голову, это избавиться от универсальности и для разных типов запросов, сделать разные ветки в коде, т.е. отделить когда нужно получать все, а когда по фильтрам, ну еще можно ин на экзистс на всякий случай заменить, но думаю оптимизатор и сам не дурак, так же можно добавить индекс по дате - первое что пришло в голову.


да, замена

CountryID in (SELECT ID FROM @countries)
OR
RegionID in (SELECT ID FROM @regions)
OR
CityID in (SELECT ID FROM @cities)

на

exists (SELECT ID FROM @countries WHERE ID = CountryID)
OR
exists (SELECT ID FROM @regions WHERE ID = RegionID )
OR
exists (SELECT ID FROM @cities WHERE ID = CityID )

ничего не дала

разделить ветки можно но обычно нужно искать как раз когда все условия/фильтры активны
23 июн 11, 16:02    [10863073]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
ponemetski
да, замена
ничего не дала
Ну я почти не сомневался..
ponemetski
разделить ветки можно но обычно нужно искать как раз когда все условия/фильтры активны
Ну а что вам мешает написать
if @all_cities = 1 ..тогда запрос без фильтров по @countries и т.д., а если в ваших таблицах фильтрующих есть значения - другая ветка. Короче, постараться отделить для оптимизатора ситуации в которых выгодно использовать просмотр, от ситуаций в которых выгодно использовать поиск. Универсальных планов и под то и под то, к сожалению не бывает.
А тот ваш составной индекс, для поиска скорее всего не используется, т.к. у вас условия or.
Ну а вообще, планы надо смотреть конечно, и говорить про конкретную ситуацию, я пока так, пальцем в небо только тычу и воздух сотрясаю...
23 июн 11, 16:56    [10863724]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
ponemetski
Member

Откуда:
Сообщений: 7
2SomewhereSomehow
спаибо,
я пока решил отойти от stored procedure и попробовать составить текст запроса в приложении
он хотябы чистым получится, без многочисленных OR
23 июн 11, 17:03    [10863811]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить