iT>>А если у сервера есть гистограммы распределения?
iT>>И там написано, что константа1 встречается в таблице 10 раз, а константа2 — 1000000 раз?
M>Значит у него в кеше для данного запроса с параметрами(!) будет прописано, что возможно два физических плана выполнения в зависимости от того что пришло в параметре.

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

А вот если будет два запроса с литералами, то с использованием гистограмм он получит и будет хранить два разных плана.

Здравствуйте, pkarklin, Вы писали:
P>Мои знания берутся тоже из личного опыта. И на, в принципе, классическом запросе вида Звезда, когда идет объединение порядка 10-15 таблиц и в предложение WHERE несколько критериев отбора как по полям целого типа, так и по datetime, оптимизатор не может провести автопараметризацию. О чем явно сказано в BOL. И именно для повторного использования плана выполнения в таких случаях, я использую хп. Хотя тем, кто любит зашивать инструкции в клиента, и sp_executesql подойдет.
А можно попросить пример (на базе Northwind или Pubs) такого запроса?

Здравствуйте, pkarklin, Вы писали:
P>И на, в принципе, классическом запросе вида Звезда, когда идет объединение порядка 10-15 таблиц и в предложение WHERE несколько критериев отбора как по полям целого типа, так и по datetime, оптимизатор не может провести автопараметризацию.
Естественно автопараметризация не всесильна, но тех кто на клиенте запрос без параметров пишет бъют железной линейкой по пальцам и вообщем за дело.

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

P> Хотя тем, кто любит зашивать инструкции в клиента, и sp_executesql подойдет.
sp_executesql подставляет драйвер, напрямую его никто не использует. На клиенте я пишу

using(DbManager DB = new DbManager())
{
     DataTable DT = DB.ExecteDataTable(@"
                seelect * from ... join ... on ... ...  where a=@a and b=@b",
                DB.Parameter("@a",1),
                DB.Parameter("@b",2));
}

А на сервер приезжает sp_executesql "...."

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

P>Вот и я о том же твердил в том топике на SQL.RU, тока Вы еще третий способ забыли — использование хп.
Ээээ, "Я Пастернака не читал, но как и весь совецкий народ..."
Вообщем выводы озвученные здесь на основе Вашего sql.ru-шного топика имеют мало общего с действительностью, вот я и возмутился.
На самом деле, Вы, если я правильно понял, ратуете за использование хп на основании того, что они лучше кешируются? Для запросов с параметрами это не верно, в обоих случаях он кешируется одинаково хорошо.

Здравствуйте, pkarklin, Вы писали:

M>>И сделать это можно двумя способами, либо указывая вопросики и передавая параметры в должном порядке, либо используя процедуру sp_executesql, что и делают большинство клиентских библиотек.
P>Вот и я о том же твердил в том топике на SQL.RU,

Простите, но в том топике на SQL.RU Вы ни слова не сказали про "указывая вопросики" — иначе тот топик был бы вдвое короче.

Здравствуйте, Merle, Вы писали:

M>Совершенно согласен, но ведь и с фиксированными планами точно такая же ситуация, соотношение изменилось и данная константа уже отличается от других в противоположную сторону.

Хм. Так фиксированных планов тоже никто не предлагает — по крайней мере, если не иметь в виду план, который год за годом остается в кэше, медленно теряя связь с реальностью

Ну а в целом ситуация понятна, спасибо.

Здравствуйте, Merle, Вы писали:

M>Естественно автопараметризация не всесильна, но тех кто на клиенте запрос без параметров пишет бъют железной линейкой по пальцам и вообщем за дело.

Хм...Понятно, но обыгрывалась то именно эта ситауция, а не параметризация на клиенте.

P>> И именно для повторного использования плана выполнения в таких случаях, я использую хп.
M>Если только для этого, то зря.

Нет, не только для этого.

M>sp_executesql подставляет драйвер, напрямую его никто не использует. На клиенте я пишу
...
M>А на сервер приезжает sp_executesql "...."

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

M>Так что грамотно написанный запрос, что в sp, что на клиенте кешируется с абсолютно одинаковой эффективностью.

Согласен, тысячу раз согласен, при условии параметризации НА КЛИЕНТЕ.

M>На самом деле, Вы, если я правильно понял, ратуете за использование хп на основании того, что они лучше кешируются? Для запросов с параметрами это не верно, в обоих случаях он кешируется одинаково хорошо.

Нет, это тока одно из приемуществ при использовании хп.

Здравствуйте, Softwarer, Вы писали:

S>Здравствуйте, pkarklin, Вы писали:

M>>>И сделать это можно двумя способами, либо указывая вопросики и передавая параметры в должном порядке, либо используя процедуру sp_executesql, что и делают большинство клиентских библиотек.
P>>Вот и я о том же твердил в том топике на SQL.RU,

S>Простите, но в том топике на SQL.RU Вы ни слова не сказали про "указывая вопросики" — иначе тот топик был бы вдвое короче.

Простите, но про "указывая вопросики" — что означает клиентскую параметризацию при работе через ODBC с использованием модели prepare\execute, я писал.

так может кто-нибудь повторить эксперемент на табличке где пара 7-ок, и почти милион 8-ок с запросом
select a from tbl1 where a<7
и
select a from tbl1 where a<8

так чтоб в в одном случае правильно было бы юзать индекс а в другом fullscan

Здравствуйте, pkarklin, Вы писали:

P>Простите, но про "указывая вопросики" — что означает клиентскую параметризацию при работе через ODBC с использованием модели prepare\execute, я писал.

Простите, в первую очередь я не понял, при чем тут ODBC. Сервер либо поддерживает binding в запросах, либо нет. Если поддерживает — Ваше утверждение о кэшировании планов исключительно в случае SP становится очень странным.

Во вторую очередь, если поиск не врет — слово odbc в той теме не употребляется ни разу вообще.

В-третьих, цитата:

Я:
----------------------------------------------------------------------------
А кто сказал, что оптимизатор должен строить план при каждом выполнении
запроса из приложения
----------------------------------------------------------------------------

Вы:
----------------------------------------------------------------------------
Блин, никто мне об этом не гоорил, я сам с этим сталкиваюсь.
----------------------------------------------------------------------------

В-третьих, когда я спросил Вас про "select .. where object_id = :id" Вы совершенно однозначно сказали, что для MS SQL это будут разные запросы — после чего долго удивлялись, что для оракла будет иначе.

P>Согласен, тысячу раз согласен, при условии параметризации НА КЛИЕНТЕ.

Хм. А какой вариант Вы рассматривали там? В форуме дельфы? Параметризацию не на клиенте?

Здравствуйте, Softwarer, Вы писали:

S>Простите, в первую очередь я не понял, при чем тут ODBC. Сервер либо поддерживает binding в запросах, либо нет. Если поддерживает — Ваше утверждение о кэшировании планов исключительно в случае SP становится очень странным.

Так, начинай с начала. Сам по себе оптимизатор ничего не поддерживает. За binding отвечают ДОПОЛНИТЕЛЬНЫЕ хп, sp_executesql, sp_prepare.


S>Во вторую очередь, если поиск не врет — слово odbc в той теме не употребляется ни разу вообще.

Видимо поиск врет, ибо вот цитата:

На счет вашей трассы. Если это действительно так и серверу Oracle передаются запросы с маркерами параметров и он сам их подставляет, то это плюс ему. Для сиквела параметризация маркеров происходит на уровне механизма доступа к данным (ODBC, OLEDB).

А вот пост: Оптимальней: куча QUERY или SQL.add(' ');


S>Хм. А какой вариант Вы рассматривали там? В форуме дельфы? Параметризацию не на клиенте?

Вернемся к тому топику еще раз? Разберемся снова в чем отличие параметризации клиентским механизмом доступа к данным и автопараметризацией оптимизатором сервера?

Здравствуйте, pkarklin, Вы писали:

P>Вернемся к тому топику еще раз? Разберемся снова в чем отличие параметризации клиентским механизмом доступа к данным и автопараметризацией оптимизатором сервера?
Стоп-стоп, не надо путать теплое с мягким. Есть автопараметризация — когда сервер сам догадывается о том, что константы можно заменить параметрами.
Есть просто параметризация, причем пофигу где она выполняется, на сервере или на клиенте, в данном случае where a=?, a=@a, и аa=:a полностью эквивалентны. Плюс есть еще некоторые системны хранимки, которые умеют немного больше.

Здравствуйте, Merle, Вы писали:

M>Стоп-стоп, не надо путать теплое с мягким. Есть автопараметризация — когда сервер сам догадывается о том, что константы можно заменить параметрами.
M>Есть просто параметризация, причем пофигу где она выполняется, на сервере или на клиенте, в данном случае where a=?, a=@a, и аa=:a полностью эквивалентны. Плюс есть еще некоторые системны хранимки, которые умеют немного больше.

Ну, наконец, то. Хоть Вы меня поняли правильно. Т.е. резюмируя оба этих топика можно сказать, что использование хп — одно из возможных решений вопроса параметризации на сервере, а точнее решение проблем с автопарметризацией оптимизатором.

Здравствуйте, pkarklin, Вы писали:

Я позволю себе переупорядочить фразы — думаю, в этом нет ничего страшного.

P>Вернемся к тому топику еще раз? Разберемся снова в чем отличие параметризации клиентским механизмом доступа к данным и автопараметризацией оптимизатором сервера?

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

В рамках изначального вопроса — тема практически неинтересная.

P>Видимо поиск врет, ибо вот цитата:

Джуджу уже отписал. Интересный глюк.

P>Так, начинай с начала. Сам по себе оптимизатор ничего не поддерживает. За binding отвечают ДОПОЛНИТЕЛЬНЫЕ хп, sp_executesql, sp_prepare.

Я окончательно перестал Вас понимать. Давайте я попробую сформулировать вопрос, ответ на который меня интересует.

Исходная ситуация: я в дельфе делаю объект Query (или аналогичный в другой технологии доступа), пишу в нем SQL.Text = 'select * from table where field = :a', указываю значение для параметра, делаю Open.

Вопрос: какие варианты реально возможны в случае MS SQL?

1) На сервер приедет текст запроса вида (примерно) 'select * from table where field = ?' и значение параметра.

2) На сервер приедет текст запроса вида 'select * from table where field = 123' (с подставленным параметром)

3) На сервер приедет (в каком-то виде) просьба вызвать хранимку (sp_XXXX) с указанными параметрами, одним из которых является текст sql.

4) Еще что-то

Здравствуйте, Softwarer, Вы писали:

S>Я окончательно перестал Вас понимать. Давайте я попробую сформулировать вопрос, ответ на который меня интересует.

S>Исходная ситуация: я в дельфе делаю объект Query (или аналогичный в другой технологии доступа), пишу в нем SQL.Text = 'select * from table where field = :a', указываю значение для параметра, делаю Open.

S>Вопрос: какие варианты реально возможны в случае MS SQL?

S>1) На сервер приедет текст запроса вида (примерно) 'select * from table where field = ?' и значение параметра.

S>2) На сервер приедет текст запроса вида 'select * from table where field = 123' (с подставленным параметром)

S>3) На сервер приедет (в каком-то виде) просьба вызвать хранимку (sp_XXXX) с указанными параметрами, одним из которых является текст sql.

S>4) Еще что-то

Очень многое будет зависеть от того, какие дрова вы будете использовать. При работе с BDE и Prepared = False будет 2.

1 не будет никогда, ибо '?' — это маркер параметра для ODBC функции SQLPrepare. При этом на сервер при первом вызове будет отправлен вызов sp_prepare, где будет текст запроса и параметр вида @param. А уже затем sp_execute c параметром.

При работе через ADO (OLEDB) и использовании наследников TCustomADODataSet будет произведен вызов sp_executesql примерно в таком виде:

execute sp_executesql 
          N'select * from pubs.dbo.employee where job_lvl = @level',
          N'@level tinyint',
          @level = 35

Здравствуйте, Softwarer, Вы писали:

S>Исходная ситуация: я в дельфе делаю объект Query (или аналогичный в другой технологии доступа), пишу в нем SQL.Text = 'select * from table where field = :a', указываю значение для параметра, делаю Open.
S>Вопрос: какие варианты реально возможны в случае MS SQL?

Ну так посмотри в SQL Profiler. Ну вот например что происходит если делать аналогичное в VB6 + ADO.

Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command, prm As ADODB.Parameter, rst As ADODB.Recordset

cnn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=mydb;Data Source=myserv"

Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText
cmd.CommandText = "SELECT * FROM mytable WHERE mytableid = ?"
cmd.Prepared = True

Set prm = cmd.CreateParameter("prm1", adInteger, adParamInput)
cmd.Parameters.Append prm

cmd.Parameters(0).Value = 5
Set rst = cmd.Execute

В этом месте видим в профайлере (одним батчем):

declare @P1 int
set @P1=1
exec sp_prepexec @P1 output, N'@P1 int', N'SELECT * FROM mytable WHERE mytableid = @P1', 5
select @P1

Идем дальше:

rst.Close
cmd.Parameters(0).Value = 10005
Set rst = cmd.Execute

В профайлере:

exec sp_execute 1, 10005

Ты что-то такое хотел увидеть?

Здравствуйте, pkarklin, Вы писали:

Спасибо. То есть, картина в моем понимании остается примерно неизменной: либо SQL обертывается в хранимки (явно или неявно — механизмом доступа), либо подстановка параметров на клиенте и соответствующая некоторая потеря производительности.

Здравствуйте, lazymf, Вы писали:

L>Ну так посмотри в SQL Profiler. Ну вот например что происходит если делать аналогичное в VB6 + ADO.

Боюсь, даже если я поставлю себе MS SQL — результаты, полученные мной "при первом знакомстве" вряд ли можно будет считать достоверными характеристиками продукта. Поэтому приходится спрашивать.

О том, что описанный Вами вариант возможен, pkarklin уже говорил. Меня интересует спектр возможностей — в первую очередь, упоминание "вопросиков" заставило предположить, что в MS SQL таки возможен, как в Оракле, первый из названных мной вариантов.

Здравствуйте, Softwarer, Вы писали:

S> Меня интересует спектр возможностей — в первую очередь, упоминание "вопросиков" заставило предположить, что в MS SQL таки возможен, как в Оракле, первый из названных мной вариантов.
Возможен, все зависит от драйвера. Просто использование системных хранимок типа sp_executesql несколько эффективнее и все последние версии драйверов пользуются ими.

Здравствуйте, Merle, Вы писали:

M>Возможен, все зависит от драйвера. Просто использование системных хранимок типа sp_executesql несколько эффективнее и все последние версии драйверов пользуются ими.

А за счет чего эффективнее? Или просто известно по факту?

Что я не совсем понимаю — если есть эффективная реализация, что мешает серверу вызывать ее внутри себя? Дополнительные телодвижения клиента, имхо, заведомо не более эффективны — он снаружи, то есть не может использовать то, что сервер может сделать внутри себя.

Здравствуйте, Softwarer, Вы писали:

S>А за счет чего эффективнее? Или просто известно по факту?
Скорее по факту, но есть некоторые догадки...

S>Что я не совсем понимаю — если есть эффективная реализация, что мешает серверу вызывать ее внутри себя?
Видимо сервер не всегда в состоянии привести оддну реализацию к другой.

S>Дополнительные телодвижения клиента, имхо, заведомо не более эффективны — он снаружи, то есть не может использовать то, что сервер может сделать внутри себя.
Это не дополнительные телодвижения, клиент может вызвать либо одну реализацию, либо другую. Накладные расходы на клиенте на приведение к обоим формам вызова примерно одинаковы.
Видимо, в силу каких-то причин, серверу проще, когда параметры явно проименованы и заранее известен их тип, клиенту все равно, а серверу проще...

Здравствуйте, Merle, Вы писали:
S>>Дополнительные телодвижения клиента, имхо, заведомо не более эффективны — он снаружи, то есть не может использовать то, что сервер может сделать внутри себя.
M>Это не дополнительные телодвижения, клиент может вызвать либо одну реализацию, либо другую. Накладные расходы на клиенте на приведение к обоим формам вызова примерно одинаковы.
M>Видимо, в силу каких-то причин, серверу проще, когда параметры явно проименованы и заранее известен их тип, клиенту все равно, а серверу проще...
Ну, на самом деле проблема упирается ровно в парсер.
Смысл в том, что явное указание параметров точно говорит движку, какие части запроса имеют тенденцию измениться при следующем запуске. Если их нет, то для парсера весь запрос — это просто строка. Вот простейший пример:

select * from orders where orderdate < DateAdd(day, -5, getdate())

Сколько здесь параметров? один? два? Какой план кэшировать?

select * from orders where orderdate < @p1

или

select * from orders where orderdate < DateAdd(day, @p1, getdate())

или может

select * from orders where orderdate < DateAdd(day, @p1, @p2)

?