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

Откуда:
Сообщений: 1978
Процедура в настоящий момент получает имя таблицы как nvarchar(60),
затем формируется динамический запрос, который выполняется при помощи execute.

Если процедура будет получать саму таблицу при помощи табличного параметра, что превратит запрос в статический, повысит ли это скорость выполнения запроса?
И правильно ли я понимаю, что для передачи таблицы придется использовать табличную переменную?
27 июн 12, 18:10    [12784705]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить выполнение процедур  [new]
Crimean
Member

Откуда:
Сообщений: 13147
нет / нет. и статики тут не будет. небось запрос не из простых и 90% времени тратится на компиляцию. увы. проблемы дизайна не всегда можно подпереть в эксплуатации. возможно, уход на предподготовленные запросы чуть поможет. но это может оказаться весьма сильным колдунством в освоении
27 июн 12, 18:14    [12784723]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить выполнение процедур  [new]
DaniilSeryi
Member

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

Предподготовленные запросы? Это как?
28 июн 12, 09:38    [12786702]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить выполнение процедур  [new]
aleks2
Guest
DaniilSeryi
Crimean,

Предподготовленные запросы? Это как?


Это аккурат так, как делать не надо. Даже если "выхода нет".
28 июн 12, 09:41    [12786718]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить выполнение процедур  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
А почему нельзя написать по одной хранимке на каждую таблицу? Или обьединить таблицы в одну, если они вообще ничем не отличаются кроме названия? Тогда и не надо использовать динамический sql.
28 июн 12, 10:01    [12786804]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить выполнение процедур  [new]
DaniilSeryi
Member

Откуда:
Сообщений: 1978
Тогда переформулирую задачу.
Итак, есть одна большая таблица в 10 миллионов строк, которая увеличивается каждый месяц ещё на 170-200 тысяч.
И есть несколько маленьких, но однотипных - от 5 до 80 тысяч строк.
Необходимо произвести сворачивание по нескольким столбцам как маленькой таблицы, так и отобранной по двум столбцам части большой. А затем необходимо произвести Full Join получившихся свернутых таблиц, записать в новую таблицу, и свернуть ещё раз.

Как этот процесс можно ускорить?

Первая мысль:
а) использовать для большой таблицы Partition Scheme,
б) создать индексы по тем полям, по которым производится отбор части данных,
в) создать один индекс для всех полей, по которым идет сворачивание данных. Или лучше единый запрос сделать для полей, по которым таблицы объединяются?
г) Объединить все маленькие таблицы в одну большую, что позволит отказаться от динамического запроса в процедуре, но потребует создания аналогичных индексов для свежесозданной таблицы.
28 июн 12, 10:15    [12786914]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить выполнение процедур  [new]
DaniilSeryi
Member

Откуда:
Сообщений: 1978
Мистер Хенки,

Эти малые таблицы - появляются часто и регулярно удаляются. Для каждой из них - свою процедуру?
28 июн 12, 10:16    [12786924]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить выполнение процедур  [new]
SomewhereSomehow
Member

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

Прежде всего нужно знать, с чем бороться. Включите statistics time, во время выполнения вашего динамического запроса и посмотрите, сколько ушло времени на его компиляцию. Возможно совсем не много, зато оптимизатор получает возможность строить новый план для каждых новых значений параметров (если они есть). Если выяснится, что основное время уходит на выполнение, а не на компиляцию, то посмотрите в действительный план запроса. Посмотрите нет ли сильных (на порядок), различий между estimated number of rows и actual number of rows. Если есть, попробуйте обновить статистику с полным сканированием, выполнить запрос повторно и посмотреть, изменилось ли что-то. Посмотрите на операторы которые на выходе имеют большое число строк, и посмотрите на "дорогие" операторы. Посмотрите, как происходит работа с большой таблицей, нет ли там сканирования, если есть, то может ли оно быть заменено на поиск по индексу. Есть ли для этого подходящий индекс. Если нет, то нужно создать и проследить, чтобы он использовался. Если без сканирования никак, то посмотрите, возможно ли делать его параллельно, нет ли каких-то сдерживающих факторов для этого (скалярных функций, спулов, и т.д.)
В общем, нужно выяснить, на что уходит время и заниматься оптимизацией чего-то конкретного.
28 июн 12, 10:42    [12787121]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить выполнение процедур  [new]
DaniilSeryi
Member

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

запустил с set showplan_text и с Include Actual Execution Plan - в итогах set showplan_text фигурировало множество Hash Match, что говорит о необходимости индекса, а в итогах Include Actual Execution Plan фигурировало требование создать покрывающий индекс, что "could improve the query cost by 89.5278%".

Индекс придётся делать. Вопрос в другом - как долго он будет создаваться на 10 миллионах строк на офисной машине с двумя гигами оперативки?
И нужно ли создавать отдельный индекс для поля, которое Included в первом индексе (чтобы избавиться от одного из Hash Match)
28 июн 12, 11:39    [12787592]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить выполнение процедур  [new]
Владимир Затуливетер
Member

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

У вас тестовая машина есть? или все на рабочей делаете?
Индекс не должен долго создаваться на 10 мил. несколько минут, правда все зависит от данных.
28 июн 12, 13:39    [12788459]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить выполнение процедур  [new]
dmitry stakanov
Member

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

запустил с set showplan_text и с Include Actual Execution Plan - в итогах set showplan_text фигурировало множество Hash Match, что говорит о необходимости индекса, а в итогах Include Actual Execution Plan фигурировало требование создать покрывающий индекс, что "could improve the query cost by 89.5278%".

Индекс придётся делать. Вопрос в другом - как долго он будет создаваться на 10 миллионах строк на офисной машине с двумя гигами оперативки?
И нужно ли создавать отдельный индекс для поля, которое Included в первом индексе (чтобы избавиться от одного из Hash Match)


сначала создайте индекс, который просят, потом смотрите как план поменяется.
база всегда поднагрузкой? если нет, то создавайте ночью.
28 июн 12, 13:45    [12788515]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить выполнение процедур  [new]
DaniilSeryi
Member

Откуда:
Сообщений: 1978
Владимир Затуливетер,

Это будущая тестовая, на которой сейчас приходится работать.
Индекс у меня создавался 9 минут.
Потом пришлось создать ещё один - на другой таблице.
В итоге скорость выполнения кода выросла с 74 секунд до 4.

Остался неотвеченным только один вопрос - есть ли смысл делать Partition Scheme, чтобы разбить данные по годам (полю DATA)?
28 июн 12, 13:52    [12788575]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить выполнение процедур  [new]
dmitry stakanov
Member

Откуда:
Сообщений: 241
DaniilSeryi
Владимир Затуливетер,

Это будущая тестовая, на которой сейчас приходится работать.
Индекс у меня создавался 9 минут.
Потом пришлось создать ещё один - на другой таблице.
В итоге скорость выполнения кода выросла с 74 секунд до 4.

Остался неотвеченным только один вопрос - есть ли смысл делать Partition Scheme, чтобы разбить данные по годам (полю DATA)?


думаю нет
28 июн 12, 14:12    [12788734]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить выполнение процедур  [new]
DaniilSeryi
Member

Откуда:
Сообщений: 1978
Ещё один вопрос появился - есть ли необходимость отключать составной некластеризованный индекс перед вставкой данных в таблицу?
(В таблице 10 миллионов записей, в индексе два ключевых и пять INCLUDED столбцов, вставляется каждый раз 180-200 тысяч записей).
28 июн 12, 15:05    [12789186]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить выполнение процедур  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
DaniilSeryi
Ещё один вопрос появился - есть ли необходимость отключать составной некластеризованный индекс перед вставкой данных в таблицу?
(В таблице 10 миллионов записей, в индексе два ключевых и пять INCLUDED столбцов, вставляется каждый раз 180-200 тысяч записей).


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

А вообще, как говорит мой Шеф: "Тестируйте, тестируйте, тестируйте!"
28 июн 12, 21:42    [12791176]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить