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

Откуда:
Сообщений: 10
Имеется таблица с тремя полями:
[N] [int] IDENTITY(1,1) NOT NULL,
[Cell] [int] NOT NULL,
[Feature] [varbinary](512) NOT NULL

Первичный ключ по полям Cell, N.

Поле Cell принимает значения 0..6^8. Распределение значений не равномерное, но и не вырожденное (2/3 возможных значений в таблице присутствуют).

Из таблицы делается выборка запросом
DECLARE @filter AS TABLE (Cell INT)
INSERT @filter SELECT Cell FROM dbo.GetAllCells(...)
SELECT Feature FROM Features
WHERE (Cell IN
(SELECT Cell FROM @filter))

Временная таблица содержит от 100 до 500 записей, сгруппированных в 3-4 интервала (т.е. разброс значений небольшой).

В основной таблице 40 млн записей и быстро увеличивается. Предполагается рост до 100-150 млн. Время выполнения выборки постоянно падает.

Хотел секционировать таблицу по полю Cell для ускорения выборки. Ввел 36 равных интервалов. Вместо ускорения получил замедление на 20%.
Вопрос №1: этот инструмент в моих условиях не должен давать ускорения, или я что-то не так сделал?
Вопрос №2: что еще можно придумать для ускорения?

Заранее всем спасибо!
24 окт 11, 11:53    [11487729]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация выборки из большой таблицы  [new]
iljy
Member

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

а выбираются близкие значения Cell? Или произвольно разбросанные? Если разбросанные, то секционирование может дать выигрышь только при разнесении секций в разные файлгруппы (желательно еще их на разные луны положить). А вы вообще помотрели, в чем именно затык по производительности? Или так, поиграть в угадайку решили?
24 окт 11, 11:58    [11487773]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация выборки из большой таблицы  [new]
kuzz2000
Member

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

Во временной таблице сотни записей, представляющих собой 3-4 группы подряд идущих значений. Сами группы разбросаны по интервалу хаотично. Я полагал, что проверка 4 секций будет проходить быстрее, чем полной таблицы.

В разные файловые группы не раскидывал. А есть ли смысл, если на сервере один диск?
24 окт 11, 12:06    [11487852]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация выборки из большой таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Какая часть запроса выполняется больше времени?
Заполнение табличной переменной вызовом какой-то функции (она, кстати, inline?)?
Выборка из Features?

Индекс в табличной переменной ничего не меняет?
24 окт 11, 12:10    [11487884]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация выборки из большой таблицы  [new]
iljy
Member

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

Во временной таблице сотни записей, представляющих собой 3-4 группы подряд идущих значений. Сами группы разбросаны по интервалу хаотично. Я полагал, что проверка 4 секций будет проходить быстрее, чем полной таблицы.

С какого перепугу? Экономия может достигаться за счет работы с одной секцией, а у вас все равно лезет во все, так что запросто станет медленнее.

kuzz2000
В разные файловые группы не раскидывал. А есть ли смысл, если на сервере один диск?

Параллелиться может лучше. Но это все гадания. Повторяю вопрос - вы смотрели, в чем конкретно затык?
24 окт 11, 12:10    [11487887]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация выборки из большой таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Кстати говоря, а зачем вообще табличная переменная @filter?
24 окт 11, 12:11    [11487891]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация выборки из большой таблицы  [new]
kuzz2000
Member

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

Время заполнения временной таблицы пренебрежительно мало. Заполняет CLR-функция.
24 окт 11, 12:17    [11487951]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация выборки из большой таблицы  [new]
kuzz2000
Member

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

индекс ничего не меняет. от временной таблицы уходил - никакой разницы.
24 окт 11, 12:20    [11487982]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация выборки из большой таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
kuzz2000
iap,

Время заполнения временной таблицы пренебрежительно мало. Заполняет CLR-функция.
Табличной переменной, Вы хотели сказать?
Ну так что, индекс по ней не помогает?
24 окт 11, 12:20    [11487991]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация выборки из большой таблицы  [new]
kuzz2000
Member

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

Я вот и не могу понять, почему запрос лезет во все секции? Я ожидал, что в 3-4...
24 окт 11, 12:21    [11487995]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация выборки из большой таблицы  [new]
iljy
Member

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

Я вот и не могу понять, почему запрос лезет во все секции? Я ожидал, что в 3-4...

В план смотрели? Там видно, в какие секции он лезет. И даже пробег по 3-4 может дать проигрышь.

И повторяю вопрос, сосредоточтесь. Вы смотрели, за счет чего именно растет время выполнения запроса?
24 окт 11, 12:23    [11488004]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация выборки из большой таблицы  [new]
Users
Member

Откуда:
Сообщений: 341
А вот так?

SELECT Feature FROM Features
WHERE exists (SELECT Cell FROM @filter where Cell = Features.Cell))
24 окт 11, 12:26    [11488017]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация выборки из большой таблицы  [new]
kuzz2000
Member

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

По плану видно, что обходит все секции. Видимо, это и дает основное время.
24 окт 11, 12:28    [11488032]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация выборки из большой таблицы  [new]
iljy
Member

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

По плану видно, что обходит все секции. Видимо, это и дает основное время.

Ну что значит обходит? Как иемнно - сканирует чтоли? План выложите тогда уж.
Там наверняка стоит Pre-startup filter, так что сильно врядли он реально "обходит" все.
24 окт 11, 12:30    [11488041]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация выборки из большой таблицы  [new]
aleks2
Guest
kuzz2000
Хотел секционировать таблицу по полю Cell для ускорения выборки. Ввел 36 равных интервалов. Вместо ускорения получил замедление на 20%.

Ишо один, верующий в легенду: секционирование ускоряет.


kuzz2000
Вопрос №2: что еще можно придумать для ускорения?

1. Кластерный индекс по (Cell, N). Хотя на вставку это могет сказаться плохо...
2. DECLARE @filter AS TABLE (Cell INT primary key clustered)
3. Выборка поинтервально в этих самых "3-4 интервала" и UNION ALL.
4. Обновить статистику.
5. Состряпать индекс (Cell, include Feature), ну если Кластерный никак.
6. В качестве бубна:
SELECT T.Feature FROM  @filter F left outer join Features T on F.Cell=T.Cell WHERE  T.Feature is not null
24 окт 11, 13:39    [11488669]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация выборки из большой таблицы  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
kuzz2000, попробуйте вместо табличной переменной использовать временную таблицу; возможно, наличие статистики в ней позволит улучшить план.


попутно вопрос ко всем по задаче с аналогичными условиями - почему добавление в запросе OPTION (RECOMPILE) влияет на параллелизм выборки? при экспериментах выполняю DBCC FREEPROCCACHE и после добавляю/убираю из запроса эту подсказку, что приводит, соответственно, к появлению/исчезновению параллелизма в плане при первом же исполнении.
2008 sp2 ent
24 окт 11, 14:06    [11488945]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация выборки из большой таблицы  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5503
Блог
Shakill,
Во время компиляции запроса, сервер знает о количестве записей в табличной переменной только если к тому моменту она уже заполнена. Без принудительной рекомпиляции запроса, сервер считает что там одна запись.

Соответственно и планы выполнения могут различаться, а чем больше данных, тем больше вероятность использования параллелизма.
24 окт 11, 15:22    [11489697]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация выборки из большой таблицы  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
DeColo®es, мысль понятна, но пока не сходится с примером

к моменту выборки табличная переменная заполнена, а исполнение FREEPROCCACHE означает, что в обоих вариантах сервер будет компилировать этот запрос - что есть подсказка, что её нет. просто с подсказкой полученный план будет удалён сразу после исполнения, а в данный момент условия получаются одинаковые, верно?
24 окт 11, 15:34    [11489797]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация выборки из большой таблицы  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5503
Блог
Shakill,
нет, не верно.

Опция RECOMPILE заставляет запрос рекомпилироваться перед выполнением.
Без этой опции, к моменту заполнения табличной переменной, запрос уже скомпилирован без учета ее заполнения.
24 окт 11, 15:40    [11489847]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация выборки из большой таблицы  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
DeColo®es, опа
я неправильно перевожу или недочитал где-то?
http://msdn.microsoft.com/en-us/library/ms181714.aspx
RECOMPILE
Instructs the SQL Server Database Engine to discard the plan generated for the query after it executes, forcing the query optimizer to recompile a query plan the next time the same query is executed.


ну даже без учёта этого нюанса, вопрос был в другом. до меня не дошло, поэтому по шагам
1. dbcc FREEPROCCACHE 
2. INSERT INTO @mytable
3. SELECT ... (в подзапросе есть @mytable)
ведь на шаге 3 компиляция получается всегда независимо от подсказки и к этому моменту в @mytable уже есть данные
но план зависит от подсказки. вы хотите сказать, что без неё он получается скомпилирован уже до фактического выполнения шага 2? не понимаю
24 окт 11, 15:48    [11489922]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация выборки из большой таблицы  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5503
Блог
Shakill,

Ну, далее там написано:
автор
When compiling query plans, the RECOMPILE query hint[ b]uses the current values[/b] of any local variables in the query and, if the query is inside a stored procedure, the current values passed to any parameters.
Использовать значения локальных переменных в запросе можно только если к тому моменту пакет уже начал выполняться. и эти значения присвоены.
Так что правы все.

По использованию FREEPROCCACHE - посмотрите профайлером, нет ли события рекмпиляции.
Возможно, что сам себя запрос из кэша не выкидывает.
24 окт 11, 16:09    [11490126]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация выборки из большой таблицы  [new]
iljy
Member

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

недавно совсем была тема, ссылку лень искать. Суть в том, что при выполнении пакета планы, по которым идет собственно выполнение, находятся не в кеше, а в каких-то внутренних структурах. Соответственно получается так:
1. Пакет компилируется, при этом планы могут браться из кеша.
2. Выполняется FREEPROCCACHE, кеш очищается, но на выполнении пакета это уже никак не сказывается.
Это очень легко проверить:
dbcc freeproccache
go
select * from sys.dm_exec_cached_plans

dbcc freeproccache

select * from sys.tables

select * from sys.dm_exec_cached_plans
24 окт 11, 16:19    [11490199]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация выборки из большой таблицы  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
DeColo®es,
то есть пакет компилируется целиком, и если внутри него самого идёт изменение содержимого переменных, то планы частей пакета без хинта уже не будут это учитывать. ок

illy,
DBCC было в отдельном батче, забыл сказать

проверил профайлером и через sys.dm_exec_cached_plans
по факту получилось, что OPTION (RECOMPILE) даёт сразу два эффекта: и рекомпиляцию запроса до выполнения, и непопадание его плана в кэш
24 окт 11, 16:58    [11490544]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация выборки из большой таблицы  [new]
iljy
Member

Откуда:
Сообщений: 8711
Shakill
illy,
DBCC было в отдельном батче, забыл сказать

Тогда оно тем более никакого влияния на другие пакеты не могло оказать. Они компилируются последовательно. Просто при компиляции пакета целиком значения переменных не известны, а для отдельного оператора - очень даже запросто.
24 окт 11, 17:05    [11490616]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация выборки из большой таблицы  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5503
Блог
Shakill
по факту получилось, что OPTION (RECOMPILE) даёт сразу два эффекта: и рекомпиляцию запроса до выполнения, и непопадание его плана в кэш
Ну а собственно что ему там делать, если его все равно потом рекомпилить нужно. ;)
24 окт 11, 17:38    [11490890]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить