Скорость выполнения хранимой проседуры

← →
Ольга (2005-05-14 16:06) [0]

Проблема такая: работаю в Query Analyzer, выполняю процедуру:
declare @id int, @data datetime, @sender int
set @data="20050503"
set @id_gg=531
set @sender=0
exec CheckRGE @id,@data,@sender
Работает очень долго (>1мин)
Вытаскиваю тело процедуры в Analyzer, параметры объявляю переменными, выполняю - быстро (<1сек)
В чем дело и как с этим бороться?

← →
Nikolay M. © (2005-05-14 16:35) [1]

Думаю, планы выполнения ХП в обоих случаях очень разные.
В первом случае план хранится при компиляции процедуры и при его построении сервер не знает значения параметров и, следовательно, не может оптимально выбрать нужные индексы.
Во втором случае значения параметров известны и оптимизатор выберет оптимальный план, основываясь на статистике индексов.
Указание в хинтах таблиц в селектах нужных индексов, имхо, спасет ситуацию.

← →
Ольга (2005-05-14 17:03) [2]

> Указание в хинтах таблиц в селектах нужных индексов

В Help-е про хинты как-то невнятно написано, не могу понять синтаксис - куда влепить нужную мне опцию (допустим индекс ХХХ на самую большую таблицу Generators_h). Может вы мне поможете, вот один из запросов:

SELECT a.id, b.id_pok, SUM(h01) h01, SUM(h02) h02, SUM(h03) h03, SUM(h04) h04, SUM(h05) h05, SUM(h06) h06, SUM(h07) h07, SUM(h08) h08,
SUM(h09) h09, SUM(h10) h10, SUM(h11) h11, SUM(h12) h12, SUM(h13) h13, SUM(h14) h14, SUM(h15) h15, SUM(h16) h16,
SUM(h17) h17, SUM(h18) h18, SUM(h19) h19, SUM(h20) h20, SUM(h21) h21, SUM(h22) h22, SUM(h23) h23, SUM(h24) h24
FROM Blocks a, Generators c, Generators_h b
WHERE a.id_gg=@id_gg and b.data=@data and b.sender=@sender and a.id=c.id_bl and c.id=b.id
GROUP BY a.id, b.id_pok
UNION
SELECT a.id, b.id_pok, SUM(h01) h01, SUM(h02) h02, SUM(h03) h03, SUM(h04) h04, SUM(h05) h05, SUM(h06) h06, SUM(h07) h07, SUM(h08) h08,
SUM(h09) h09, SUM(h10) h10, SUM(h11) h11, SUM(h12) h12, SUM(h13) h13, SUM(h14) h14, SUM(h15) h15, SUM(h16) h16,
SUM(h17) h17, SUM(h18) h18, SUM(h19) h19, SUM(h20) h20, SUM(h21) h21, SUM(h22) h22, SUM(h23) h23, SUM(h24) h24
FROM Blocks a, Boilers c, Boilers_h b
WHERE a.id_gg=@id_gg and b.data=@data and b.sender=@sender and a.id=c.id_bl and c.id=b.id
GROUP BY a.id, b.id_pok

← →
evvcom © (2005-05-14 17:31) [3]

О! Привет энергетикам.
Пример из help SQL Server, ключ.слово "hints, join hints":
SELECT ...
FROM authors a
INNER MERGE JOIN titleauthor ta
ON a.au_id = ta.au_id
INNER HASH JOIN titles t
ON t.title_id = ta.title_id

Здесь MERGE и HASH - хинты

← →
Ольга (2005-05-14 18:04) [4]

Привет энергетикам и неэнергетикам тоже!
Спасибо за подсказки, указала индексы - помогло, но не кардинально, стало в 2 раза быстрее работать. Нужно разбираться дальше... или грохнуть эту процедуру, видно сильно криво написано.
Ладно, утро вечера мудренее.

← →
Nikolay M. © (2005-05-14 23:05) [5]

> В Help-е про хинты как-то невнятно написано, не могу понять
> синтаксис

Имхо, все вполне понятно описано.
SELECT * FROM Northwind..Categories WITH (INDEX (CategoryName))

> указала индексы - помогло, но не кардинально, стало в 2
> раза быстрее работать

В 2 раза - это не в 60 раз. Если таблицы очень большие, никогда не помешает обновить статистику. Еще, как один из вариантов, попробовать создать процедуру с опцией RECOMPILE.

← →
Ольга (2005-05-17 09:44) [6]

RECOMPILE не помогает, INDEX в принципе тоже. Ситуация никак не разруливается. Помогите еще кто-нибудь советом!
Процедура состоит из десятка громоздких запросов, внутри создаются 4 переменные-таблицы для хранения промежуточных результатов, клиенту процедура возвращает одну из них. Очень не хочется тащить этот расчет в программу, т.к. алгоритм часто меняется.
Если бы все это не выполнялось быстро в Query Analyzer, я бы уже сдалась, но ведь выполняется! Почему же, когда все команды упаковываются в процедуру, начинаются дикие тормоза (сегодня > 3 мин)?

← →
Nikolay M. © (2005-05-17 10:25) [7]

> RECOMPILE не помогает, INDEX в принципе тоже. Ситуация никак
> не разруливается. Помогите еще кто-нибудь советом!

У меня советы еще не закончились. После расстановки индексов получился такой же план, что и при выполнении тела ХП в QA и работает он также быстро?
А если перед выполнением ХП сделать sp_recompile и DBCC FREEPROCCACHE?

← →
Nikolay M. © (2005-05-17 10:47) [8]

Да, еще вопросы: от какого пользователя создается ХП и какое у нее название?

← →
Ольга (2005-05-17 12:42) [9]

1. Индексы. У меня на самых больших таблицах вторичных индексов нет, т.к. вполне устраивает первичный кластерный (Primary Key по 3 полям, по которым и строятся однотипные запросы). Может я не права? Вчера после установки WITH (INDEX(0)) - в общем-то достаточно бессмысленной в данном случае - стало работать быстрее, но это, видимо, сервер к вечеру подразгрузился. Сегодня - без результата.
2. sp_recompile делала. Результат тот же.
3. DBCC FREEPROCCACHE, а так же sp_updatestats запустить не могу, т.к. не вхожу в группу администраторов сервера. В свойствах БД на Auto update statistics галки стоят. Этого мало?
4. ХП: dbo.CheckRGE, создается от моего имени - хозяина БД.
???

← →
Nikolay M. © (2005-05-17 13:07) [10]

> 1. Индексы. У меня на самых больших таблицах вторичных индексов
> нет, т.к. вполне устраивает первичный кластерный (Primary
> Key по 3 полям, по которым и строятся однотипные запросы).
> Может я не права?

Тут очень много факторов, влияющих на (не)правильность такого решения. Какие выполняются селекты (точнее, какие накладываются условия и агрегаты), насколько часто делаются insert/delete, размер полей, по которым построен кластерный индекс (имхо, кластерный индекс по полям, суммарным размером больше 4 байт может быть чреват потерей производительности), fillfactor индекса, среднее кол-во записей в таблице и тд, и тп.
Btw, надеюсь, про влияние очередности полей, перечисленных в индексе, в курсе?

> 3. DBCC FREEPROCCACHE, а так же sp_updatestats запустить
> не могу, т.к. не вхожу в группу администраторов сервера.

С FREEPROCCACHE, имхо, все-таки желательно поэксперементировать. Неужели тестового сервера нет? А права на запуск sp_updatestats по умолчанию у dbo есть.

> В свойствах БД на Auto update statistics галки стоят. Этого
> мало?

Мало. В этом случае сервер будет обновлять статистику "по мере необходимости" автоматом. А "мера необходимости" остается целиком на усмотрение сервера.

Еще было бы любопытно увидеть, насколько быстро будет работать ХП, принимающая на вход аналогичыне параметры, но формирующая и выполняющая dynamic sql.

← →
Ольга (2005-05-17 15:52) [11]

1. Кластерный индекс по 4 полям (одно забыла): datatime,int,int,int - получается значительно больше 4 байт, fillfactor=90, insert/delete делается довольно часто (~9000 зап/сут), кол-во записей ~2 млн. (поддерживается на постоянном уровне). Запросы формируются по всем или N-первым полям ключа в соответствующем порядке, часто делается SUM по неключевым 24 полям.
Создала вторичный индекс на 2 первых поля - особой разницы в скорости не заметила. К сожалению, закончилось раб. время и все стало работать быстрее.
Что посоветуете? Суррогатный ключ? По-моему, не надо. Увеличится скорость записи, которая меня не сильно волнует, так как делается автоматически в фоновом режиме. А для выборок все равно нужен естественный ключ (сделать уникальный индекс?).
2. С DBCC FREEPROCCACHE проэкспериментировали - чуда не произошло.
3. sp_updatestats вставила в ночной JOB.
4. С dynamic sql попробую завтра (возни много)-напишу.

> Кластерный индекс по 4 полям (одно забыла): datatime,int,int,int
> - получается значительно больше 4 байт

Угу. 20 байт, имхо, многовато. Честно говоря, на практике с ключами такой длины не сталкивался, но чисто умозрительно могу предположить, что операций физического чтения будет больше, чем даже в случае отсутствия кластерного индекса и, как следствие, обращения к страницам данных по RowID. Если есть возможность, рекомендую сделать несколько максимально разных запросов к этой таблице и посмотреть, что покажет QA при включенных опциях
SET STATISTICS TIME и
SET STATISTICS IO.
Потом сделать индекс НЕкластерным и выполнить те же запросы. И, наконец, завести новый ПК в виде Identity-поля. Планы запросов в каждом случае тоже пригодятся. Результат будет сразу виден.

> Создала вторичный индекс на 2 первых поля - особой разницы
> в скорости не заметила

Если эти два поля входят в кластерный индекс первыми по порядку, то это логично.

> Что посоветуете? Суррогатный ключ? По-моему, не надо. Увеличится
> скорость записи

Как раз скорость записи уменьшится, т.к. в случае операций вставки-удаления производится обновление кластерного индекса, а он у вас все-таки 20 байт * 2 млн. Но если скорость вставки не важна, может вообще fillfactor поднять до 95-100? Обновление будет происходить долго, зато физически индекс будет занимать меньшее кол-во страниц.

> А для выборок все равно нужен естественный ключ (сделать
> уникальный индекс?).

Не могу на это ответить заочно, нужно смотреть, какие делаются запросы к таблицам, какие поля участвуют в WHERE, в конце концов, просто ставить эксперименты, делать разные индексы. Что будет оптимальнее: длинный кластерный индекс (увеличивается число обращений к страницам индекса, но обращение к страницам с данными происходит быстрее) или короткий кластерный индекс - имхо, лучший ответ даст эксперимент.
Но, собственно, поскольку запрос в QA выполняется мгновенно, задачи оптимизации индексов, похоже, не стоит.

> С DBCC FREEPROCCACHE проэкспериментировали - чуда не произошло.

Жаль. Собственно, вот похожая проблема
http://www.sql.ru/forum/actualthread.aspx?bid=1&tid=42841&pg=-1
но если с перекомпиляцией и очисткой кэша вы эксперементировали, то дело, видимо, не в этом.

← →
Ольга (2005-05-18 13:45) [13]

Наконец-то у меня получилось! Плохо то, что я так и не поняла после какой из многочисленных моих манипуляций я добилась приемлемого результата (3-4 сек.)
Nikolay M. Большое вам спасибо, без вас бы я не справилась.

> Плохо то, что я так и не поняла после какой из многочисленных
> моих манипуляций я добилась приемлемого результата (3-4
> сек.)

Жаль, мне тоже было бы интересно :)
На здоровье, если что, обращайтесь :)

Скорость выполнения хранимой проседуры Найти похожие ветки