Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите оптимизировать запрос: select top 1 ... where a = [константа] order by b  [new]
Katy
Member

Откуда: Екатеринбург-Москва
Сообщений: 876
есть таблица в 1с. в ней порядка 105 млн записей


CREATE TABLE [dbo].[_InfoRg1006](
	[_Period] [datetime] NOT NULL,
	[_RecorderTRef] [binary](4) NOT NULL,
	[_RecorderRRef] [binary](16) NOT NULL,
	[_LineNo] [numeric](9, 0) NOT NULL,
	[_Active] [binary](1) NOT NULL,
	[_Fld1007RRef] [binary](16) NOT NULL,
	[_Fld1008RRef] [binary](16) NOT NULL,
	[_Fld1009RRef] [binary](16) NOT NULL,
	[_Fld1010RRef] [binary](16) NOT NULL,
	[_Fld1011] [numeric](14, 2) NOT NULL,
	[_Fld1012] [binary](1) NOT NULL,
	[_Fld1013] [binary](1) NOT NULL
) ON [PRIMARY]

GO

CREATE UNIQUE NONCLUSTERED INDEX [_InfoRe1006_ByDims_RRRRTRN] ON [dbo].[_InfoRg1006] 
(
	[_Fld1007RRef] ASC,
	[_Fld1008RRef] ASC,
	[_Fld1009RRef] ASC,
	[_Fld1010RRef] ASC,
	[_Period] ASC,
	[_RecorderTRef] ASC,
	[_RecorderRRef] ASC,
	[_LineNo] 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]
GO

CREATE UNIQUE CLUSTERED INDEX [_InfoRe1006_ByPeriod_TRN] ON [dbo].[_InfoRg1006] 
(
	[_Period] ASC,
	[_RecorderTRef] ASC,
	[_RecorderRRef] ASC,
	[_LineNo] 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]
GO

-- медленный запрос использует индекс [_InfoRe1006_ByPeriod_TRN] (clustered index scan)
-- выполняется порядка 1-2 мин 
SELECT TOP 1  T1._Period
FROM _InfoRg1006 T1 with ( nolock )
WHERE (T1._Fld1007RRef = 0x85500002A54C31E611DCF00551A25B8D ) 
ORDER BY (T1._Period) DESC

-- быстрый запрос. в подсказке заставляем использовать индекс [_InfoRe1006_ByDims_RRRRTRN]
-- выполняется меньше 1 сек

SELECT TOP 1  T1._Period
FROM _InfoRg1006 T1 with ( nolock , index ( _InfoRe1006_ByDims_RRRRTRN ))
WHERE (T1._Fld1007RRef = 0x85500002A54C31E611DCF00551A25B8D ) 
ORDER BY (T1._Period) DESC

статистика по обоим индексам обновлялась недавно (пару дней назад)
при этом именно с _Fld1007RRef = 0x85500002A54C31E611DCF00551A25B8D запись скорее всего добавленна давно. по крайней мере запрос возвращает 2009 год. Для некоторых значений _Fld1007RRef запрос выполняется относительно быстро. Но при этом в плане тоже используется индекс [_InfoRe1006_ByPeriod_TRN]

1. можно ли как то переписать запрос
SELECT TOP 1  T1._Period
FROM _InfoRg1006 T1 with ( nolock )
WHERE (T1._Fld1007RRef = 0x85500002A54C31E611DCF00551A25B8D ) 
ORDER BY (T1._Period) DESC
так, чтобы он использовал индекс [_InfoRe1006_ByDims_RRRRTRN]. при этом не использовать with ( index (...) )?

2. можно ли утверждать что в с этим индексом запрос будет заведомо выполнятся быстрее

данных по каждому значению _Fld1007RRef порядка 4 000-5 000 записей


версия сервера
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)
4 май 11, 09:39    [10601770]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос: select top 1 ... where a = [константа] order by b  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

а вариант добавить индекс:
CREATE NONCLUSTERED INDEX [_InfoRe1006_ByDims_RRRRTRN_2] ON [dbo].[_InfoRg1006]
(
	[_Fld1007RRef] ASC,
	[_Period] ASC
)
GO
не рассматривается?

Posted via ActualForum NNTP Server 1.4

4 май 11, 10:07    [10601919]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос: select top 1 ... where a = [константа] order by b  [new]
Katy
Member

Откуда: Екатеринбург-Москва
Сообщений: 876
daw, рассматривался. сейчас в процессе обсуждения. пока склоняемся к варианту
SELECT TOP 250  T1._Period
FROM _InfoRg1006 T1 with ( nolock ) 
WHERE (T1._Fld1007RRef = 0x85500002A54C31E611DCF00551A25B8D ) 
ORDER BY (T1._Period) DESC
и top 1 уже на клиенте выбирать.
4 май 11, 10:13    [10601961]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос: select top 1 ... where a = [константа] order by b  [new]
iljy
Member

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

значит написать with(nolock) мы могем, а with(index) нам религия не позволяет? И из каких соображений вы решили, что top 250 будет быстрее, чем top 1?
Статистика кстати как обновлялась? С FULLSCAN или без? И еще - обновитесь до SP2, может это тупо глюк оптимизатора.
4 май 11, 11:50    [10602671]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос: select top 1 ... where a = [константа] order by b  [new]
Katy
Member

Откуда: Екатеринбург-Москва
Сообщений: 876
iljy
Katy,

значит написать with(nolock) мы могем, а with(index) нам религия не позволяет?
Вы не поверите. именно религия. Это требование архитектора.
iljy
И из каких соображений вы решили, что top 250 будет быстрее, чем top 1?
Опытным путем: в плане запроса используется индекс[_InfoRe1006_ByDims_RRRRTRN]
iljy
Статистика кстати как обновлялась? С FULLSCAN или без? И еще - обновитесь до SP2, может это тупо глюк оптимизатора.

Статистика свежая. значение 0x85500002A54C31E611DCF00551A25B8D пападает в диапазоны статистики.
Вообщето и при свежей статистике с FULLSCAN мог не попасть

Вот на глюr похоже. сейчас на тестовм сервере проверяю.
4 май 11, 11:55    [10602716]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос: select top 1 ... where a = [константа] order by b  [new]
iljy
Member

Откуда:
Сообщений: 8711
Katy
iljy
Katy,

значит написать with(nolock) мы могем, а with(index) нам религия не позволяет?
Вы не поверите. именно религия. Это требование архитектора.

А nolock - тоже требование архитектора? Гоните их, доктор, им просто негде!
Katy
iljy
И из каких соображений вы решили, что top 250 будет быстрее, чем top 1?
Опытным путем: в плане запроса используется индекс[_InfoRe1006_ByDims_RRRRTRN]

Всегда? Запросто можно нарваться на ту же проблему, что в некоторых случаях используется, в некоторых нет. Ну и тащить записи на клиент точно никакой необходимости, можно обернуть запрос и сделать во внешнем top 1.
Katy
iljy
Статистика кстати как обновлялась? С FULLSCAN или без? И еще - обновитесь до SP2, может это тупо глюк оптимизатора.

Статистика свежая. значение 0x85500002A54C31E611DCF00551A25B8D пападает в диапазоны статистики.
Вообщето и при свежей статистике с FULLSCAN мог не попасть

Ээээ.... так я не понял - был FULLSCAN или нет?? Попадать-то оно может и попадает, вот только нет гарантий, что количество записей правильно определяется. Кстати это легко проверить - на действительный план запроса посмотрите.

Katy
Вот на глюr похоже. сейчас на тестовм сервере проверяю.

Проверяйте, все-таки это действительно странно - индекс-то заведомо покрывающий...
4 май 11, 12:25    [10602938]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос: select top 1 ... where a = [константа] order by b  [new]
Katy
Member

Откуда: Екатеринбург-Москва
Сообщений: 876
iljy
Ээээ.... так я не понял - был FULLSCAN или нет?? Попадать-то оно может и попадает, вот только нет гарантий, что количество записей правильно определяется. Кстати это легко проверить - на действительный план запроса посмотрите.
Я на действительный план запроса и смотрю. FULLSCAN сейчас в процессе - но это надолго. Но и без FULLSCAN, AVG_RANGE_ROWS в диапазоне был правильный
4 май 11, 12:33    [10603003]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос: select top 1 ... where a = [константа] order by b  [new]
iljy
Member

Откуда:
Сообщений: 8711
Katy
iljy
Ээээ.... так я не понял - был FULLSCAN или нет?? Попадать-то оно может и попадает, вот только нет гарантий, что количество записей правильно определяется. Кстати это легко проверить - на действительный план запроса посмотрите.
Я на действительный план запроса и смотрю.

И что вы в нем видете? Предполагаемое количество строк совпадает с реальным?
4 май 11, 12:45    [10603111]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос: select top 1 ... where a = [константа] order by b  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

> Проверяйте, все-таки это действительно странно - индекс-то заведомо покрывающий...

покрывающий-то он покрывающий. но это же после поиска еще сортировку (нескольких
тысяч найденных строк) делать надо. а это дорого.
а с кластерным получается ordered scan, пока первую строку, подпадающую под
условие, не найдем. вполне может иметь смысл именно так делать. собственно, и
в самом деле:

> Для некоторых значений _Fld1007RRef запрос выполняется относительно быстро.
> Но при этом в плане тоже используется индекс [_InfoRe1006_ByPeriod_TRN]

и логично, что, если найти надо уже не top 1, а, скажем, top 250, то такая тактика
уже не выгодна.

может, правда, оптимизатор и переоценивает дороговизну сортировки. но сомневаюсь, что
это именно глюк.

ну, а вместо хинта можно еще и так, например, сервер обмануть:

ORDER BY dateadd(dd, 1, T1._Period) DESC

Posted via ActualForum NNTP Server 1.4

4 май 11, 13:00    [10603225]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос: select top 1 ... where a = [константа] order by b  [new]
Katy
Member

Откуда: Екатеринбург-Москва
Сообщений: 876
iljy
Katy
пропущено...
Я на действительный план запроса и смотрю.

И что вы в нем видете? Предполагаемое количество строк совпадает с реальным?

estimated number of rows = 1. но разве это не потоум что top 1?
AVG_RANGE_ROWS - 3500 (по индексу _InfoRe1006_ByDims_RRRRTRN), в реальности ~ 4 000

К сообщению приложен файл. Размер - 73Kb
4 май 11, 13:07    [10603276]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос: select top 1 ... where a = [константа] order by b  [new]
Katy
Member

Откуда: Екатеринбург-Москва
Сообщений: 876
daw, спасибо!
4 май 11, 13:09    [10603286]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос: select top 1 ... where a = [константа] order by b  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

> ну, а вместо хинта можно еще и так, например, сервер обмануть:

только вот, лучше ли такой фокус, чем явный хинт...

Posted via ActualForum NNTP Server 1.4

4 май 11, 13:14    [10603326]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос: select top 1 ... where a = [константа] order by b  [new]
iljy
Member

Откуда:
Сообщений: 8711
daw
> Проверяйте, все-таки это действительно странно - индекс-то заведомо покрывающий...

покрывающий-то он покрывающий. но это же после поиска еще сортировку (нескольких
тысяч найденных строк) делать надо. а это дорого.
а с кластерным получается ordered scan, пока первую строку, подпадающую под
условие, не найдем. вполне может иметь смысл именно так делать.

Ну может быть... Хотя там ведь не просто Sort, а Top N Sort, а он дешевый.
4 май 11, 13:18    [10603384]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос: select top 1 ... where a = [константа] order by b  [new]
iljy
Member

Откуда:
Сообщений: 8711
Katy
iljy
пропущено...

И что вы в нем видете? Предполагаемое количество строк совпадает с реальным?

estimated number of rows = 1. но разве это не потоум что top 1?
AVG_RANGE_ROWS - 3500 (по индексу _InfoRe1006_ByDims_RRRRTRN), в реальности ~ 4 000

Не для этого запроса, а с использованием индекса! Тут-то естественно на выход будет 1 строка, но сколько он прочитает прежде, чем выполнится условие??
4 май 11, 13:21    [10603415]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос: select top 1 ... where a = [константа] order by b  [new]
Katy
Member

Откуда: Екатеринбург-Москва
Сообщений: 876
iljy
Katy
пропущено...

estimated number of rows = 1. но разве это не потоум что top 1?
AVG_RANGE_ROWS - 3500 (по индексу _InfoRe1006_ByDims_RRRRTRN), в реальности ~ 4 000

Не для этого запроса, а с использованием индекса! Тут-то естественно на выход будет 1 строка, но сколько он прочитает прежде, чем выполнится условие??
кстате непонятно почему там estimated number of rows 38 860, если AVG_RANGE_ROWS - 3 500

К сообщению приложен файл. Размер - 95Kb
4 май 11, 13:34    [10603534]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос: select top 1 ... where a = [константа] order by b  [new]
SomewhereSomehow
Member

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

может имеет смысл выложить два реальных плана , в текстовом виде, одного и того же запроса с хинтом и без?
И еще интересно, а если ордер бай убрать, будет ли медленный запрос использовать индекс? и насколько фрагментированы данные и индекс?
4 май 11, 14:10    [10603837]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос: select top 1 ... where a = [константа] order by b  [new]
Katy
Member

Откуда: Екатеринбург-Москва
Сообщений: 876
SomewhereSomehow, считаете имеет смысл? мне кажется daw всё логисно объяснил 10603225
4 май 11, 14:14    [10603874]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос: select top 1 ... where a = [константа] order by b  [new]
SomewhereSomehow
Member

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

Ну если этим проблема решена, то конечно.
4 май 11, 14:21    [10603928]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос: select top 1 ... where a = [константа] order by b  [new]
Katy
Member

Откуда: Екатеринбург-Москва
Сообщений: 876
чтобы совсем закрыть тему:

1. обновление статистики с FULLSCAN не помогает
2. такой индекс исправляет ситуацию.
CREATE NONCLUSTERED INDEX [_InfoRe1006_ByDims_RRRRTRN_2] ON [dbo].[_InfoRg1006]
(
	[_Fld1007RRef] ASC,
	[_Period] ASC
)
сервер его использует сам. без подсказки
10 май 11, 10:25    [10627540]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос: select top 1 ... where a = [константа] order by b  [new]
Нектотам
Guest
Katy,

1. Лезть в БД 1С в обход 1С - нарушение лиц. соглашения. При этом в данном случае - ненужное нарушение.
2. Проиндексировать измерение, соответствующее полю [_Fld1007RRef] религия не позволяет? Судя по всему это регистр сведений, подчиненный регистратору, а _Fld1007RRef - первое его измерение. При индексировании средствами 1С (в соответствии с документацией - см. статью "Индексы таблиц базы данных" на ИТС) будет создан некластерный индекс "_Fld1007RRef, _Period, _RecorderTRef, _RecorderRRef, _LineNo", что прекрасно решает вашу задачу.
3. Есть подозрение, что в теме не упомянуты все регистры (где, например "_RecorderTRef, _RecorderRRef, _LineNo"?)
10 май 11, 11:29    [10628073]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить