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

Откуда:
Сообщений: 539
Добрый день!
Имеется вот такая таблица:
CREATE TABLE [dbo].[GoodsSale]
( 
	[Id]			        INT IDENTITY(1,1) NOT NULL,		
	[IdGoods]			INT NOT NULL,
	[IdStore]			INT NOT NULL,
	[Date]			DATETIME NOT NULL,		
	[SaledQuantity]		INT NOT NULL, 
	[RowVersion]		TIMESTAMP NOT NULL,
	CONSTRAINT [PK_GoodsSale] PRIMARY KEY NONCLUSTERED ([Id]),	
	CONSTRAINT [UQ_GoodsSale$IdGoods$IdStore$Date] UNIQUE CLUSTERED ([IdGoods],[IdStore],[Date]),
	CONSTRAINT [FK_GoodsSale$IdGoods] FOREIGN KEY(IdGoods) REFERENCES Goods(Id),	
	CONSTRAINT [FK_GoodsSale$IdStore] FOREIGN KEY(IdStore) REFERENCES Store(Id)	
)

Сейчас в ней не очень много записей, порядка 2.5 млн.
Делаю простой запрос с группировкой по годам и месяцам:
SELECT 
	gs.IdGoods,
	s.IdSalesPoint,
	DATEPART(yyyy,gs.Date) Y,
	DATEPART(mm,gs.Date) M,	
	SUM(gs.SaledQuantity) SaledQuantity
FROM GoodsSale gs 
INNER JOIN Store s ON s.Id = gs.IdStore
WHERE 1 = 1
	AND gs.Date >= @dateFrom
	AND gs.Date <= @dateTo		
GROUP BY
	gs.IdGoods,
	s.IdSalesPoint,
	DATEPART(yyyy,gs.Date),
	DATEPART(mm,gs.Date)

Запрос выполняется порядка 30 секунд! И это для 2.5 млн. записей.
При этом сиквел подсказал, что нужно создать индекс:
CREATE NONCLUSTERED INDEX [IX_GoodsSale$Date] ON [dbo].[GoodsSale] ([Date])
INCLUDE ([IdGoods],[IdStore],[SaledQuantity])

После создания этого индекса, время выполнения запроса не сокращается :)

У меня появились вопросы:
Почему сиквел не хочет работать с кластерным индексом, а подсказывает создать не кластерный и перечислил все поля которые уже указаны в кластерном?
Как мне оптимизировать этот простой запрос?
15 авг 13, 13:53    [14713043]     Ответить | Цитировать Сообщить модератору
 Re: Медленный Select с группировкой  [new]
Glory
Member

Откуда:
Сообщений: 104760
RomanH
Запрос выполняется порядка 30 секунд! И это для 2.5 млн. записей.

2,5 млн записей всего или которые попали в запрос ?

RomanH
Почему сиквел не хочет работать с кластерным индексом,

Отчего вы решили, что не хочет ? Увидели в плане ?
15 авг 13, 13:56    [14713072]     Ответить | Цитировать Сообщить модератору
 Re: Медленный Select с группировкой  [new]
iap
Member

Откуда: Москва
Сообщений: 46999
RomanH,

по IDStore, похоже, индекса нет?
15 авг 13, 14:02    [14713124]     Ответить | Цитировать Сообщить модератору
 Re: Медленный Select с группировкой  [new]
RomanH
Member

Откуда:
Сообщений: 539
Спасибо что откликнулись!
Glory
RomanH
Запрос выполняется порядка 30 секунд! И это для 2.5 млн. записей.

2,5 млн записей всего или которые попали в запрос ?

RomanH
Почему сиквел не хочет работать с кластерным индексом,

Отчего вы решили, что не хочет ? Увидели в плане ?


всего записей в таблице - 2.5 млн
Кластерный индекс используются но как scan, а хочется seek.

iap
по IDStore, похоже, индекса нет?

Отдельно для IdStore индекса нет, но он же есть в кластерном уникальном.

Может при создании таблицы, я не правильно указал индексы? Да вроде правильно(на мой взгляд.)

К сообщению приложен файл. Размер - 57Kb
15 авг 13, 14:12    [14713196]     Ответить | Цитировать Сообщить модератору
 Re: Медленный Select с группировкой  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
RomanH

всего записей в таблице - 2.5 млн
Кластерный индекс используются но как scan, а хочется seek.


так попробуйте обьявлять его как CONSTRAINT [UQ_GoodsSale$IdGoods$IdStore$Date] UNIQUE CLUSTERED ([Date],[IdGoods],[IdStore])
15 авг 13, 14:17    [14713239]     Ответить | Цитировать Сообщить модератору
 Re: Медленный Select с группировкой  [new]
Glory
Member

Откуда:
Сообщений: 104760
RomanH
Кластерный индекс используются но как scan, а хочется seek.

А что seek-ать по нему ?
На IdGoods условия нет
На IdStore хоть и есть join, но это второе поле в кластерном индексе

Совершенно правильно предложили индекс
CREATE NONCLUSTERED INDEX [IX_GoodsSale$Date] ON [dbo].[GoodsSale] ([Date])
INCLUDE ([IdGoods],[IdStore],[SaledQuantity])
что бы хотя бы по [Date] фильтровать его
15 авг 13, 14:17    [14713245]     Ответить | Цитировать Сообщить модератору
 Re: Медленный Select с группировкой  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
а на Store можно индексы посмотреть?
15 авг 13, 14:22    [14713284]     Ответить | Цитировать Сообщить модератору
 Re: Медленный Select с группировкой  [new]
iap
Member

Откуда: Москва
Сообщений: 46999
RomanH
iap
по IDStore, похоже, индекса нет?

Отдельно для IdStore индекса нет, но он же есть в кластерном уникальном.
Как правильно заметил Glory, только на втором месте!
Значит, будет использоваться только в случае использования поля IdGoods для сравнения на точное совпадение.
15 авг 13, 14:27    [14713316]     Ответить | Цитировать Сообщить модератору
 Re: Медленный Select с группировкой  [new]
RomanH
Member

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

CREATE NONCLUSTERED INDEX [IX_GoodsSale$Date] ON [dbo].[GoodsSale] ([Date])
INCLUDE ([IdGoods],[IdStore],[SaledQuantity])

CREATE UNIQUE CLUSTERED INDEX [UQ_GoodsSale$Date$IdStore$IdGoods] ON [dbo].[GoodsSale] 
([Date],[IdStore],[IdGoods])

CREATE NONCLUSTERED INDEX [IX_GoodsSale$IdStore] ON [dbo].[GoodsSale] ([IdStore])


Время выполнения не изменилось... :(
2.5 млн. сгруппировать до 500 тыс - занимает 30 сек....
15 авг 13, 14:29    [14713329]     Ответить | Цитировать Сообщить модератору
 Re: Медленный Select с группировкой  [new]
SomewhereSomehow
Member

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

Я бы попробовал создать такой индекс:
create index ix on [dbo].[GoodsSale]([IdStore], [Date], IdGoods) include (SaledQuantity)
15 авг 13, 14:30    [14713332]     Ответить | Цитировать Сообщить модератору
 Re: Медленный Select с группировкой  [new]
iap
Member

Откуда: Москва
Сообщений: 46999
Бывает, напишешь лишнее условие для первого поля в индексе
только ради использования индекса для второго.
Глядишь, сервер его и подхватывает.
15 авг 13, 14:35    [14713362]     Ответить | Цитировать Сообщить модератору
 Re: Медленный Select с группировкой  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
RomanH
Сделал вот такие индексы для этой таблицы


Время выполнения не изменилось... :(
2.5 млн. сгруппировать до 500 тыс - занимает 30 сек....

так вы бы планы приводили, так то не интересно
15 авг 13, 14:39    [14713383]     Ответить | Цитировать Сообщить модератору
 Re: Медленный Select с группировкой  [new]
RomanH
Member

Откуда:
Сообщений: 539
Мистер Хенки
RomanH
Сделал вот такие индексы для этой таблицы


Время выполнения не изменилось... :(
2.5 млн. сгруппировать до 500 тыс - занимает 30 сек....

так вы бы планы приводили, так то не интересно


В каком виде хотите видеть планы?
15 авг 13, 14:43    [14713396]     Ответить | Цитировать Сообщить модератору
 Re: Медленный Select с группировкой  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3618
RomanH
Сделал вот такие индексы для этой таблицы

CREATE NONCLUSTERED INDEX [IX_GoodsSale$Date] ON [dbo].[GoodsSale] ([Date])
INCLUDE ([IdGoods],[IdStore],[SaledQuantity])

CREATE UNIQUE CLUSTERED INDEX [UQ_GoodsSale$Date$IdStore$IdGoods] ON [dbo].[GoodsSale] 
([Date],[IdStore],[IdGoods])

CREATE NONCLUSTERED INDEX [IX_GoodsSale$IdStore] ON [dbo].[GoodsSale] ([IdStore])


Время выполнения не изменилось... :(
2.5 млн. сгруппировать до 500 тыс - занимает 30 сек....

план в студию.
И какие периоды в таблице всего по дате, и какие выбираются ???
15 авг 13, 14:43    [14713399]     Ответить | Цитировать Сообщить модератору
 Re: Медленный Select с группировкой  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
RomanH
Мистер Хенки
пропущено...

так вы бы планы приводили, так то не интересно


В каком виде хотите видеть планы?

давайте как .sqlplan .
15 авг 13, 14:48    [14713427]     Ответить | Цитировать Сообщить модератору
 Re: Медленный Select с группировкой  [new]
RomanH
Member

Откуда:
Сообщений: 539
Итак есть таблица GoodsSale c вот такими индексами:
ALTER TABLE [dbo].[GoodsSale] ADD  CONSTRAINT [PK_GoodsSale] PRIMARY KEY NONCLUSTERED ([Id])...

CREATE UNIQUE CLUSTERED INDEX [UQ_GoodsSale$Date$IdStore$IdGoods] ON [dbo].[GoodsSale] 
([Date],[IdStore],[IdGoods])

Текст sql-запроса не изменился. Время тоже :(
План запроса во вложении.
Если смотерть на план в графическом виде, то видно что используется кластерный UQ_GoodsSale$Date$IdStore$IdGoods,
причем как seek. Это ведь хорошо?

К сообщению приложен файл (1.sqlplan - 20Kb) cкачать
15 авг 13, 15:02    [14713500]     Ответить | Цитировать Сообщить модератору
 Re: Медленный Select с группировкой  [new]
aleks2
Guest
-- вот нахера тут план?
-- мозг надо иметь
-- вангую я Store не имеет двух лимонов записей, а гораздо скромнее...
SELECT 
	gs.IdGoods,
	s.IdSalesPoint,
	gs.Y,
	gs.M,	
	SUM(gs.SaledQuantity) SaledQuantity
FROM 
(SELECT 
	IdStore,
	IdGoods,
	DATEPART(yyyy, Date) Y,
	DATEPART(mm, Date) M,	
	SUM(SaledQuantity) SaledQuantity
FROM GoodsSale
WHERE 1 = 1
	AND Date >= @dateFrom
	AND Date <= @dateTo		
GROUP BY
	IdStore,
	IdGoods,
	DATEPART(yyyy, Date),
	DATEPART(mm, Date)
) gs 
left outer JOIN Store s ON s.Id = gs.IdStore
GROUP BY
	gs.IdGoods,
	s.IdSalesPoint,
	gs.Y,
	gs.M

-- индекс потребен на GoodsSale
-- 	IdStore, IdGoods, Date, SaledQuantity


А ужо если захерачить Indexed View из вложенного запроса - будет ваще мигом...
15 авг 13, 15:12    [14713600]     Ответить | Цитировать Сообщить модератору
 Re: Медленный Select с группировкой  [new]
SomewhereSomehow
Member

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

А в приведенном плане есть большая ошибка с оценками, оценивается что будет выбрано 2307530*0.3*0.3 строк, т.е. 207677.70 ~ 207678, что видно в плане, тогда как реально - гораздо больше, от этого все съезжает и наверняка есть спил на диск в сортировке или хэше или и там и там. Какая версия сервера? Попробуйте добавит option(recompile) в конец запроса - посмотрите - что будет. Ситуация должна улучшиться, если так, то думайте, оставить опцию рекомпиляци или как-то по другому адресовать проблему неизвестных значений, например optimize for или запихиванием в процедуру с прослушиванием параметров...

И я бы все же еще попробовал создать предложенный индекс.
15 авг 13, 15:18    [14713650]     Ответить | Цитировать Сообщить модератору
 Re: Медленный Select с группировкой  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
RomanH
Итак есть таблица GoodsSale c вот такими индексами:
ALTER TABLE [dbo].[GoodsSale] ADD  CONSTRAINT [PK_GoodsSale] PRIMARY KEY NONCLUSTERED ([Id])...

CREATE UNIQUE CLUSTERED INDEX [UQ_GoodsSale$Date$IdStore$IdGoods] ON [dbo].[GoodsSale] 
([Date],[IdStore],[IdGoods])

Текст sql-запроса не изменился. Время тоже :(
План запроса во вложении.
Если смотерть на план в графическом виде, то видно что используется кластерный UQ_GoodsSale$Date$IdStore$IdGoods,
причем как seek. Это ведь хорошо?

ну плохого в это наверное ничего нет. Теперь сравните статистику чтений записей в запросе с новым индексом и со старым - потом сделаете вывод стало лучше или нет
15 авг 13, 15:24    [14713685]     Ответить | Цитировать Сообщить модератору
 Re: Медленный Select с группировкой  [new]
RomanH
Member

Откуда:
Сообщений: 539
aleks2,

Магия!!!!
Используя Ваш запрос и вот этот индекс:
CREATE UNIQUE CLUSTERED INDEX [UQ_GoodsSale$Date$IdStore$IdGoods] ON [dbo].[GoodsSale] 
([Date],[IdStore],[IdGoods])

Время выполнения запроса 4 секунды! Спасибо огромное!
Кол-во строк в таблице Stores, действительно не много, порядка 20 записей.

Но у меня возник вопрос:
Если в Вашем запросе изменить left outer JOIN Store s ON s.Id = gs.IdStore
на inner, то время выполнения 48 сек. C left 4 сек. Как так?
15 авг 13, 15:33    [14713736]     Ответить | Цитировать Сообщить модератору
 Re: Медленный Select с группировкой  [new]
RomanH
Member

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

А в приведенном плане есть большая ошибка с оценками, оценивается что будет выбрано 2307530*0.3*0.3 строк, т.е. 207677.70 ~ 207678, что видно в плане, тогда как реально - гораздо больше, от этого все съезжает и наверняка есть спил на диск в сортировке или хэше или и там и там. Какая версия сервера? Попробуйте добавит option(recompile) в конец запроса - посмотрите - что будет. Ситуация должна улучшиться, если так, то думайте, оставить опцию рекомпиляци или как-то по другому адресовать проблему неизвестных значений, например optimize for или запихиванием в процедуру с прослушиванием параметров...

И я бы все же еще попробовал создать предложенный индекс.


Версия сиквела 2008 R2. Пробовал с предложенным индексом, изменений нет в лучшую сторону.
15 авг 13, 15:37    [14713769]     Ответить | Цитировать Сообщить модератору
 Re: Медленный Select с группировкой  [new]
Glory
Member

Откуда:
Сообщений: 104760
А там случаем CONSTRAINT [FK_GoodsSale$IdStore] FOREIGN KEY(IdStore) REFERENCES Store(Id) не not trusted ?
15 авг 13, 15:42    [14713811]     Ответить | Цитировать Сообщить модератору
 Re: Медленный Select с группировкой  [new]
RomanH
Member

Откуда:
Сообщений: 539
Glory
А там случаем CONSTRAINT [FK_GoodsSale$IdStore] FOREIGN KEY(IdStore) REFERENCES Store(Id) не not trusted ?

Не знаю что такое trusted, никогда не сталкивался с этим. Но на всякий случай скрипт создания этого FK приведу:

ALTER TABLE [dbo].[GoodsSale]  WITH CHECK ADD  CONSTRAINT [FK_GoodsSale$IdStore] FOREIGN KEY([IdStore])
REFERENCES [dbo].[Store] ([Id])
GO

ALTER TABLE [dbo].[GoodsSale] CHECK CONSTRAINT [FK_GoodsSale$IdStore]
GO
15 авг 13, 15:48    [14713878]     Ответить | Цитировать Сообщить модератору
 Re: Медленный Select с группировкой  [new]
Glory
Member

Откуда:
Сообщений: 104760
select OBJECTPROPERTY(OBJECT_ID('FK_GoodsSale$IdStore'), 'CnstIsNotTrusted')
15 авг 13, 15:52    [14713907]     Ответить | Цитировать Сообщить модератору
 Re: Медленный Select с группировкой  [new]
RomanH
Member

Откуда:
Сообщений: 539
Glory
select OBJECTPROPERTY(OBJECT_ID('FK_GoodsSale$IdStore'), 'CnstIsNotTrusted')

Результат равен 0
15 авг 13, 15:58    [14713957]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить