View vs. Stored Procedure in SQL Server

Lightik · Post by **Lightik** » 29 Mar 2004 14:29

Спецы в SQL Server, помогите оценить выбор между хранимой процедурой, возвращающей набор записей, и вью, делающем то же самое. Что работает быстрее, и есть ли разница, и при каких условия она проявится, если запрос на выборку записей один и тот же, непараметризованный.
Соответсвенно тот же вопрос возникает для параметризованного запроса, но тогда уже выбор идет между хранимой процедурой и функцией.
БД используется для веб-апликейшена под ИИС, если это имеет значение. (То есть нагрузку и скорость приходится учитывать)

Strannik223 · Post by **Strannik223** » 29 Mar 2004 15:33

добавлю 3-й вариант: хранимка возвращает вьюшку :)

create sp_bla-bla
as
select * from my_view

Dmitry67 · Post by **Dmitry67** » 29 Mar 2004 15:39

Для view не хранится план
Потому что план зависит от where которые присоединят к view вовне
Поэтому
select * from V where A=0
select * from V where A>10
могут дать совершенно разные планы

Напротив, как правило в sp план сгенерен заранее, поэтому работает быстрее
В этом и плюс и минус
В случае нерегулярное селективности лучше view, с другой стороны в sp можно нерегулярные значения обрабатывать вручную

В принципе есть случаи когда быстрее одно а когда другое

UMLец · Post by **UMLец** » 29 Mar 2004 15:57

Dmitry67 wrote:В этом и плюс и минус
В случае нерегулярное селективности лучше view, с другой стороны в sp можно нерегулярные значения обрабатывать вручную

В принципе есть случаи когда быстрее одно а когда другое

Точнее будет сказать, что sp быстрее, когда статистица is up to date. Но и с устаревшей статистикой тоже можно бороться - recompile sp, update statistics regulary если underlying tables очень динамично меняются.

Lightik · Post by **Lightik** » 29 Mar 2004 16:17

Dmitry67 wrote:Для view не хранится план

О! Вот это собственно мне тоже хотелось узнать. Мерси. :wink:

Dmitry67 wrote:В случае нерегулярное селективности лучше view, с другой стороны в sp можно нерегулярные значения обрабатывать вручную

А что вы назвали регулярной и нерегулярной селективностью, что то я не могу догадаться....

Dmitry67 · Post by **Dmitry67** » 29 Mar 2004 16:23

Пример таблицы с нерегулярной селективностью

1000 000 записей
somecol=0, 500 000 записей
Остальные 500 000 записей имеют значения somecol От 1 до 500000
Если по somecol есть индекс то для
somecol=1 он будет использоваться
А для somecol=0 нет

Это частая в реальности ситуация для NULL, пробела, 'N/A' итд

Lightik · Post by **Lightik** » 29 Mar 2004 16:37

UMLец wrote:Точнее будет сказать, что sp быстрее, когда статистица is up to date. Но и с устаревшей статистикой тоже можно бороться - recompile sp, update statistics regulary если underlying tables очень динамично меняются.

Гм... То есть если данные меняются редко относительно случаев их запроса, то наверное тогда быстрее извлекать их sp-ой, а не при помощи view, так как план будет up to date для достаточно большого кличества вызовов. Верно?

Вообще-то приложение жутко тормозит на https, админ уверяет меня, что это зависит от количества обращений страницы к БД, (в общем, да, 10 простеньких, к справочникам, запросов на странице - и страница выполняется минуту :roll:

, при этом 2-3 запроса на странице - нормальные несколько секунд.), аутентикация там заново происходит при каждом запросе.

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

Может засунуть все запросы в одну хранимую процедуру, кажется я видела в ADO, что можно перебирать рекордсеты по порядку, возвращенные одной хранимой процедурой. Некрасиво, конечно, негибко, но зато вернет все одним-единственным запросом к БД, пока в голову ничего не приходит больше. :pain1:

Сорри, что топик сьехал в несколько другую область, надеюсь, мне и тут подскажут.

UMLец · Post by **UMLец** » 29 Mar 2004 16:43

Dmitry67
И что?
План запроса строится одинаково для sp и view - основываясь на существующей статистике. Если "селектиеность нерегулярная" - то вывод из этого будет сделан одинаковый - для sp и view. Только для view - в момент запроса. То есть важна не сама регулярность, а ее постоянство или изменчивость.

UMLец · Post by **UMLец** » 29 Mar 2004 16:55

Lightik wrote:Может засунуть все запросы в одну хранимую процедуру, кажется я видела в ADO, что можно перебирать рекордсеты по порядку, возвращенные одной хранимой процедурой. Некрасиво, конечно...

Красиви или не красиво - вам скажет "лицо по ту сторону экрана"

Niky · Post by **Niky** » 29 Mar 2004 17:15

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

Code: Select all

    cmd = "exec MySP1 'parValue1' 'parValue2'; exec MySP2 ..."

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

Если же таблицы содержат справочную информацию, которая не слишком часто меняется, то эти данные я бы вобще засунул в кэш (если у Вас classic ASP - в Application variable).

katit · Post by **katit** » 29 Mar 2004 17:27

Lightik wrote:Вообще-то приложение жутко тормозит на https, админ уверяет меня, что это зависит от количества обращений страницы к БД, (в общем, да, 10 простеньких, к справочникам, запросов на странице - и страница выполняется минуту , при этом 2-3 запроса на странице - нормальные несколько секунд.), аутентикация там заново происходит при каждом запросе.

А как https связан с SQL Server???

Может сам веб сервер тормозит и проблема не в запросах?

Dmitry67 · Post by **Dmitry67** » 29 Mar 2004 19:52

Lightik wrote:аутентикация там заново происходит при каждом запросе.

Неужели connection pooling выключено ? Срочно включите
Полегчает в разы
А если connection pooling нет и подтормазживает DNS, то вообще любое время реакции получить

Lightik · Post by **Lightik** » 29 Mar 2004 20:23

Niky wrote:Если некрасивость в том, что все селекты придется засунуть в одну процедуру, то это дело поправимое. Можно засунуть несколько вызовов в батч, примерно так:
Code: Select all
    cmd = "exec MySP1 'parValue1' 'parValue2'; exec MySP2 ..."
В этом случае Вы получите те же несколько рекордсетов, но каждая процедура будет заниматься своим делом.

Спасибо, сама не догадалась, отличная идея.

Niky wrote:Если же таблицы содержат справочную информацию, которая не слишком часто меняется, то эти данные я бы вобще засунул в кэш (если у Вас classic ASP - в Application variable).

Тогда все выстроятся в очередь к этим апликейшн переменным, которые хранят наиболее часто используемые объекты - и скорость отзыва будет еще ниже. В данном случае не подходит: сайт - и-нет магазин, и довольно посещаемый.

katit, Не знаю, как связан https с БД, но тестирование на обычном сервере показывало нормальный результата, все летало просто, а вот вживую та часть, что на https лежит, тормозит жутко. Если переложить страницу с https части на незащищенную часть живого сайта - опять все классно работает. А скорость вывода страницу на https явно зависит от количества запросов на странице - эту закономерность протестировала.

Dmitry67, я проверяла, от количества открытых коннекшенов скорость вывода страницы не зависит - так что connection pooling скорее всего включен, но у админа я спрошу на всякий случай. Про аутентикацию при каждом запросе - это я так поняла, вот как было в оригинале, когда я спрашивала у админа:
Мой вопрос - почему https работает так медленно.
Ответ: Because HTTPS invokes third party server authentication. Without HTTPS Accellerator it work slow. it's not the problem with ASP, it's problem with making a few round trips to authentication site and back before processing ASP script.
Мой вопрос: Why it depends on quantity of function calling?
Ответ: It could be that each function goes back to database. Every trip to database adds more time.... Trip to the database still needs to be made when you ask for more data. Then "select" statement you have the more trips will be.

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

Sergey___K · Post by **Sergey___K** » 29 Mar 2004 21:30

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

Очень даже красиво. Там где это надо. Пример: отчеты с разными секциями и общими исходными и промежуточными данными и параметрами. Вся логика в одном месте, временные таблицы один раз заполняются, и т.д.

katit · Post by **katit** » 29 Mar 2004 22:05

Lightik wrote:katit, Не знаю, как связан https с БД, но тестирование на обычном сервере показывало нормальный результата, все летало просто, а вот вживую та часть, что на https лежит, тормозит жутко. Если переложить страницу с https части на незащищенную часть живого сайта - опять все классно работает. А скорость вывода страницу на https явно зависит от количества запросов на странице - эту закономерность протестировала.

Никак и не связан. Просто данные передаваемые клиенту через SSL должны быть закодированы. А эта кодировка требует ресурсов. Есть много вариантов, но проблема не в запросе :-)

Что сервер отдает клиенту? Как я себе представляю то просто изображается HTML таблица с данными(которые из вашей таблицы). Так вот чем больше данных то тем больше надо кодировать и следовательно загрузка больше. Ну а если еще физически SQL Server и IIS на одном сервер то вы понимаете...

Я не думаю что данные от SQL Server в IIS идут через SSL :pain1:

Strannik223 · Post by **Strannik223** » 29 Mar 2004 22:53

Вы что то сразу ломитесь что то менять не имея арументированых представлений что именно тормозит (уверения админа в расчет не принимать!) А это плохая практика

Что бы отбросить от себя подозрения (или подтвердить) зайдите на сервер при помощи sql profiler и посмотрите что генерит типичный запрос

Те запросы которые занимают много чего нибудь (время IO, cpu) тяните в query analizer и смотрите план исполнения, после чего оптимизируйте их (если возможно)

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

Niky · Post by **Niky** » 29 Mar 2004 23:21

Lightik wrote:
Niky wrote:Если же таблицы содержат справочную информацию, которая не слишком часто меняется, то эти данные я бы вобще засунул в кэш (если у Вас classic ASP - в Application variable).

Тогда все выстроятся в очередь к этим апликейшн переменным, которые хранят наиболее часто используемые объекты - и скорость отзыва будет еще ниже. В данном случае не подходит: сайт - и-нет магазин, и довольно посещаемый.

Ну если не делать Lock при каждом чтении, так и не выстроятся. А так-то можно и в базе при каждом чтении таблицу блокировать, если очень постараться. Тянуть каждый раз список штатов из базы - намного медленнее, уж поверьте.

Lightik · Post by **Lightik** » 29 Mar 2004 23:52

katit wrote: Ну а если еще физически SQL Server и IIS на одном сервер то вы понимаете...

Я не думаю что данные от SQL Server в IIS идут через SSL

Вот выяснила таки наконец - физически они на разных серверах, и данные идут через SSL, поэтому и так долго, и каждый запрос столько времени выполняется. Черт. Так что самое гнусное предположение оказалось верным.

Знаете, какое решение в результате предложено? Сздать БД на том сервере, где SSL и веб-апликейшн, скинуть туда малоизменяемые данные, которые обычные пользователи сайта только читают (ну справочники всякие), sql таском каждое утро их обновлять, и на настоящую БД лазать только за динамическими данными. Тогда и запросов, требующих аутентикации будет немного.

Ответа про коннекшп пулинг я добиться не смогла - что-то вроде "неважно, есть он или нет, все равно никто менять это не будет."

Так шо выкручиваться придется вышеописанным образом. Ох, горе мне, горе...

katit · Post by **katit** » 30 Mar 2004 00:02

Lightik wrote:Вот выяснила таки наконец - физически они на разных серверах, и данные идут через SSL, поэтому и так долго, и каждый запрос столько времени выполняется. Черт. Так что самое гнусное предположение оказалось верным.

Понятно. Но зачем? Сервера в разных местах и досту по инету идет?
Или по сетке SSL гоняете?

vovap · Post by **vovap** » 30 Mar 2004 01:45

katit wrote:Понятно. Но зачем? Сервера в разных местах и досту по инету идет?
Или по сетке SSL гоняете?

Скорее всего там между тем сервером, где SSL и SQL server стоит файреволл. Вот оно то и делает погоду. При этом да- как раз от числа обращений и зависит.

vovap · Post by **vovap** » 30 Mar 2004 01:49

Вообще если бы кто из классиков по теории данных увидел бы этот топик - тотчас и помер бы. Что лучше - сторед процедуры или вью?
Сторед процедуры должны доставать данные из вью. Вот как должно быть.

IA72 · Post by **IA72** » 30 Mar 2004 03:26

Lightik wrote:
katit wrote: Ну а если еще физически SQL Server и IIS на одном сервер то вы понимаете...

Я не думаю что данные от SQL Server в IIS идут через SSL

Вот выяснила таки наконец - физически они на разных серверах, и данные идут через SSL, поэтому и так долго, и каждый запрос столько времени выполняется. Черт. Так что самое гнусное предположение оказалось верным.

Знаете, какое решение в результате предложено? Сздать БД на том сервере, где SSL и веб-апликейшн, скинуть туда малоизменяемые данные, которые обычные пользователи сайта только читают (ну справочники всякие), sql таском каждое утро их обновлять, и на настоящую БД лазать только за динамическими данными. Тогда и запросов, требующих аутентикации будет немного.

Ответа про коннекшп пулинг я добиться не смогла - что-то вроде "неважно, есть он или нет, все равно никто менять это не будет."

Так шо выкручиваться придется вышеописанным образом. Ох, горе мне, горе...

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