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

Откуда: г. Альметьевск (Татарстан)
Сообщений: 235
Приветствую!

Столкнулся с проблемой производительности расчетов на сервере (MSSQL Server 2000), т.к. сделано довольно-таки топорно. Менять структуру основной БД не могу (т.е. пользоваться в ней DDL), только из соседней БД делать к ней запросы (соответственно это view, udf, sp). Чтобы рассчитать определенную величину, нужно обратиться примерно к пяти-десяти таблицам (справочники итп), а так же активно пользоваться case, т.к. методика расчет сильно зависит от состояния данных (вложенность case-ов доходит до трех). Эти расчетные величины потом суммируются и группируются по разным признакам для отчетов, полей в отчете может быть 50, записей до 100-5000.

Как сделано у меня, почему именно так и что мне в этом не нравится.
Делаю набор вьюх, которые вычисляют нужные величины. Это необходимо для того, чтобы во всех последующих расчетах использовать единожды описанный алгоритм, т.к. встречаться предварительно-рассчитаные данные будут повсеместно. К тому же алгоритм все время меняется и такой подход позволяет менять расчет в одном месте. На основе вьюх делаю запросы, которые оборачиваю в UDF для удобства указания параметров. Можно в принципе использовать процедуры.

Что плохого - медленно работает :). Как одну из причин вижу в том, что когда пишешь запрос в UDF с использованием готовых вьюх, то одна и та же таблица может присутствовать в разных вьюхах (некоторые таблицы присутствуют почти во всех:)), и серверу приходится делать "лишние соединения".
Если писать запрос пользуясь исключительно таблицами, то такой запрос во-первых займет очень много места (просто суть расчетов тяжело уловить будет, соответственно и отлаживать труднее). Во-вторых менять логику расчетов придется сразу в разных UDF и синхронизировать конечные цифры нужно очень тщательно (Это важно когда итоги в разных отчетах получаются одинаковыми:)). Отчет нужно получать по запросу немедленно, т.к. пользователи откорректировав пару значений в БД сразу хотят видеть измененный результат, т.е. вариант создавать отдельную БД и раз в сутки/час скидывать туда данные из основной БД не годится.

Вопрос: какие способы оптимизации расчетов можно применить?
Предпочтительнее ли SP вместо UDF? (мне почему-то последние ближе для получения отчетных данных :), но это больше как блажь)
Может грамотно будет использовать временные таблицы или таблицы-переменные? Если да, то каким образом?
Может вместо VIEW использовать SP, а результат последних сохранять во временных таблицах?
Конечно можно поставить тесты и посмотреть, но боюсь это займет много времени на создание реальных примеров из жизни, поэтому и спрашиваю у людей с опытом побольше моего :).
Или есть еще какие хитрости или идеи, которые я не знаю?

Заранее благодарю.
4 июл 05, 15:34    [1672619]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро выполнять расчеты на T-SQL?  [new]
Breakneck
Member

Откуда: Kiev
Сообщений: 2454
Не хотите ли посмотреть планы выполнения, выявить узкие места Ваших запросов, и подумать над их оптимизацией?
4 июл 05, 15:36    [1672633]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро выполнять расчеты на T-SQL?  [new]
GreenSunrise
Member

Откуда:
Сообщений: 12310
dymka
Предпочтительнее ли SP вместо UDF? (мне почему-то последние ближе для получения отчетных данных :), но это больше как блажь)

UDF'ы могут быль причиной тормозов. Попробуйте взять тормознутые запросы, в которых используется UDF и попробуйте заменить ее на view, или в процедуру обернуть. Сравните время выполнения.

dymka
Может грамотно будет использовать временные таблицы или таблицы-переменные? Если да, то каким образом?

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

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

dymka
Конечно можно поставить тесты и посмотреть, но боюсь это займет много времени на создание реальных примеров из жизни, поэтому и спрашиваю у людей с опытом побольше моего :)

Без тестов все равно не получится, потому что все рекомендации должны опираться именно на ваши таблицы, с именно вашими индексами, с вашими данными.

Вам обязательно надо потратить некоторое время на анализ наиболее тормознутых запросов и потихоньку начинать их тюнить. Не все сразу. По одному. Стоит также подумать и проверить, все ли у вас хорошо с индексами, проверить планы выполнения, убедиться, что нужные индексы используются. Если что-то не так, довести до ума. Также можно воспользоваться Index Tuning Wizard'ом.
4 июл 05, 15:46    [1672684]     Ответить | Цитировать Сообщить модератору
 Re: Как быстро выполнять расчеты на T-SQL?  [new]
dymka
Member

Откуда: г. Альметьевск (Татарстан)
Сообщений: 235
GreenSunrise
UDF'ы могут быль причиной тормозов. Попробуйте взять тормознутые запросы, в которых используется UDF и попробуйте заменить ее на view, или в процедуру обернуть. Сравните время выполнения.

Попробовал - в принципе все варианты выполняются примерно одинаково - явного преимущества одного перед другим я не увидел...

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

Это как раз мой случай... будем думать и пробовать...

GreenSunrise
Без тестов все равно не получится, потому что все рекомендации должны опираться именно на ваши таблицы, с именно вашими индексами, с вашими данными.
Вам обязательно надо потратить некоторое время на анализ наиболее тормознутых запросов и потихоньку начинать их тюнить. Не все сразу. По одному. Стоит также подумать и проверить, все ли у вас хорошо с индексами, проверить планы выполнения, убедиться, что нужные индексы используются. Если что-то не так, довести до ума. Также можно воспользоваться Index Tuning Wizard'ом.

Это понятно, что без тестов никуда, просто сразу хочется отбросить нежизнеспособные приемы. К сожалению, на индексы я повлиять никак не могу, т.к. БД не моя, и выполняю запросы из другой БД. Разве что могу попробовать изменять порядок соединямых таблиц или еще как пошаманить.

Выше писал, что разные представления могут использовать одни и те же таблицы, и в результирующем запросе, основанном на этих представлениях, получаются лишние объединения. План показывает hash match (right outer join, HASH:([table].[id]=[table].[id]) таблицы с собой же по первичному ключу с довольно-таки высокой стоимостью :). Я проблему вижу не в конкретной настройке каждого SQL-запроса, а в неправильном подходе изначально. Т.е. делать вьюхи на вьюхах плохо :) - один раз умудрился перебрать лимит в 256 соединяемых таблиц. Поэтому интересует сами подходы к таким вычислениям.
5 июл 05, 09:17    [1674477]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить