Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 exec sp_executesql  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7608
Не понимаю, почему ado.net и другие клиенты используют exec sp_executesql в связке с SQL Server, а не посылают прямой запрос? В других серверах, oracle, например никаких "exec sp_executesql" нет и проблем таких нет. Да и еще план запроса при этом слетает по сравнению с прямым запросом.
4 дек 14, 01:59    [16947128]     Ответить | Цитировать Сообщить модератору
 Re: exec sp_executesql  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Relic Hunter
Не понимаю, почему ado.net и другие клиенты используют exec sp_executesql в связке с SQL Server, а не посылают прямой запрос? В других серверах, oracle, например никаких "exec sp_executesql" нет и проблем таких нет. Да и еще план запроса при этом слетает по сравнению с прямым запросом.
Крик души?
Вопрос то в чем? Каких таких проблем? И нам неведомо что у вас там слетает и почему.
И как вы собираетесь передавать параметры в прямой запрос без sp_executesql? Константами что-ли?
4 дек 14, 02:15    [16947139]     Ответить | Цитировать Сообщить модератору
 Re: exec sp_executesql  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7608
Mind,

Такой у меня тест-кейс. Один и тот-же запрос черес exec sp_executesql исполняется 40 сек, прямой селект 1 сек. И как тут студия передает параметры? Передает-же как-то.
+
use FieldReportBak 
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500)
DECLARE @WBS1 varchar(5) = '10265'
DECLARE @WBS2 varchar(7) = ' '
DECLARE @WBS3 varchar(7) = ' '
DECLARE @startDate date = '14-nov-2014'
DECLARE @stopDate date = '15-nov-2014'

set @ParmDefinition = '@WBS1 varchar(5),@WBS2 varchar(7),@WBS3 varchar(7),@startDate date,@stopDate date';
set @SQLString = 'SELECT * FROM V_FR_INVOICE_ALL WHERE (WBS1 = @WBS1) AND (WBS2 = @WBS2) AND (WBS3 = @WBS3) AND (BillExt > 0) AND (TransDate BETWEEN @startDate AND @stopDate) ORDER BY LEMType, UnitName'

exec sp_executesql  @SQLString, @ParmDefinition, @WBS1, @WBS2, @WBS3, @startDate, @stopDate;

SELECT * FROM V_FR_INVOICE_ALL 
WHERE
   (WBS1 = @WBS1) 
   AND (WBS2 = @WBS2) 
   AND (WBS3 = @WBS3) 
   AND (BillExt > 0)
   AND (TransDate BETWEEN @startDate AND @stopDate) 
ORDER BY LEMType


4 дек 14, 02:42    [16947158]     Ответить | Цитировать Сообщить модератору
 Re: exec sp_executesql  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Relic Hunter,
Сколько ж можно уже. Называется это - parameter sniffing, гугл в помощь. Или вот есть хорошая статья Медленно в приложении, быстро в SSMS. В конечном счете, лечиться с помощью OPTIMIZE FOR UNKNOWN / OPTION(RECOMPILE). В вашем случае скорее всего первое.

А еще могли бы планы посмотреть, чтобы понять откуда ноги растут.

Relic Hunter
И как тут студия передает параметры? Передает-же как-то.
Это не параметры, а переменные, разницу улавливаете?
4 дек 14, 02:54    [16947163]     Ответить | Цитировать Сообщить модератору
 Re: exec sp_executesql  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7608
Mind,

Да вкурсе я про снифинг. Запрос лечится, если эго переписать так (но это-же не прилично!)
set @SQLString = N'SELECT * FROM V_FR_INVOICE_ALL WHERE (WBS1 = @WBS1) AND (WBS2 = @WBS2) AND (WBS3 = @WBS3) AND (BillExt > 0) AND (TransDate BETWEEN @startDate AND @stopDate ) ORDER BY LEMType, UnitName 
OPTION (OPTIMIZE FOR (@WBS1 UNKNOWN, @WBS2 UNKNOWN, @WBS3 UNKNOWN, @startDate UNKNOWN, @stopDate UNKNOWN))'
Вопрос был о другом, почему так происходит?
4 дек 14, 03:01    [16947166]     Ответить | Цитировать Сообщить модератору
 Re: exec sp_executesql  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Relic Hunter
Mind,

Да вкурсе я про снифинг. Вопрос был о другом, почему так происходит?
Потому что снифинг?!?

Еще есть такой вариант ответа: Потому что гладиолус
4 дек 14, 03:10    [16947169]     Ответить | Цитировать Сообщить модератору
 Re: exec sp_executesql  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7608
Mind
Потому что снифинг?!?
В общем, как оказалось, никакой не сниффинг. Смена типов параметров с VARCHAR на NVARCHAR поправила ситуацию. Хотя сами колонки WBS1, WBS2, WBS3 - VARCHAR, а не NVARCHAR. Просто у полей WBS1, WBS2, WBS3 локализация была Cyrillic_General_CI_AS и индекс не использовался при передаче параметров в формате VARCHAR.
4 дек 14, 03:32    [16947178]     Ответить | Цитировать Сообщить модератору
 Re: exec sp_executesql  [new]
aleks2
Guest
Relic Hunter
Mind
Потому что снифинг?!?
В общем, как оказалось, никакой не сниффинг. Смена типов параметров с VARCHAR на NVARCHAR поправила ситуацию. Хотя сами колонки WBS1, WBS2, WBS3 - VARCHAR, а не NVARCHAR. Просто у полей WBS1, WBS2, WBS3 локализация была Cyrillic_General_CI_AS и индекс не использовался при передаче параметров в формате VARCHAR.


Дык, это... оптимизатору поддых врезали и еще радуются.

1. NVARCHAR имеет преимущество над VARCHAR.

При where X = @X, где X VARCHAR, а @X - NVARCHAR, будет выполнено неявное преобразование

where cast( X as NVARCHAR) = @X и похерена сама возможность использования индексов.

2. Data Type Precedence (Transact-SQL)

When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operation has that data type.

SQL Server uses the following precedence order for data types:
1.user-defined data types (highest)
2. sql_varian t
3. xml
4. datetimeoffset
5. datetime2
6. datetime
7. smalldatetime
8. date
9. time
10. float
11. real
12. decimal
13. money
14. smallmoney
15. bigint
16. int
17. smallint
18. tinyint
19. bit
20. ntext
21. text
22. image
23. timestamp
24. uniqueidentifier
25. nvarchar (including nvarchar(max) )
26. nchar
27. varchar (including varchar(max) )
28. char
29. varbinary (including varbinary(max) )
30. binary (lowest)
4 дек 14, 07:45    [16947294]     Ответить | Цитировать Сообщить модератору
 Re: exec sp_executesql  [new]
Glory
Member

Откуда:
Сообщений: 104751
Relic Hunter
почему ado.net и другие клиенты используют exec sp_executesql в связке с SQL Server, а не посылают прямой запрос?

Потому что вы для облегчения своего труда используете ADO API, т.е. "обертки", которые создали другие люди.
4 дек 14, 09:19    [16947460]     Ответить | Цитировать Сообщить модератору
 Re: exec sp_executesql  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31991
Mind
Relic Hunter
Не понимаю, почему ado.net и другие клиенты используют exec sp_executesql в связке с SQL Server, а не посылают прямой запрос? В других серверах, oracle, например никаких "exec sp_executesql" нет и проблем таких нет. Да и еще план запроса при этом слетает по сравнению с прямым запросом.
Крик души?
Вопрос то в чем? Каких таких проблем? И нам неведомо что у вас там слетает и почему.
И как вы собираетесь передавать параметры в прямой запрос без sp_executesql? Константами что-ли?
Для меня это тоже загадка.

Как клиент передаёт параметры, вызывая тот же sp_executesql? Он же не делает выполнение строки, я надеюсь? Как то привязывает параметры, делает вызов...

Может, в API предусмотрено привязывание и передачу параметров только для процедур, а для батча это невозможно?
Или это именно в ADO так сделано? Тогда непонятно, зачем. Почему бы не делать прямой вызов, а не через обёртку?
4 дек 14, 10:02    [16947621]     Ответить | Цитировать Сообщить модератору
 Re: exec sp_executesql  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31991
Relic Hunter
И как тут студия передает параметры? Передает-же как-то.
Это как раз понятно, если посмотреть в трейс.

Студия не передаёт параметры для параметризованного запроса, потому что параметризованного запроса тут нет - есть просто текст. Вот студия берёт этот текст, и отсылает серверу, пусть там хоть поэма Пушкина.

Интереснее посмотреть, если мы всё таки заставим студию выполнить параметризованный запрос, например, нажав правой кнопкой на хранимую процедуру в дереве объектов и выбрав "выполнить".

Она тогда по метаданным определяет параметы, и формирует опять же текст, наподобие:
DECLARE	@return_value int

EXEC	@return_value = [dbo].[test_param]
		@id = 2

SELECT	'Return Value' = @return_value

GO
То есть и тут студия ушла от привязывания параметров средствами API доступа, а тупо передаёт некий текст!

Не знаю, с чем это связано, может, как то нестабильно, или слишком по разному от версии к версии, ведёт себя компоненты доступа...
4 дек 14, 10:09    [16947655]     Ответить | Цитировать Сообщить модератору
 Re: exec sp_executesql  [new]
step_ks
Member

Откуда:
Сообщений: 936
alexeyvg
Для меня это тоже загадка.

Как клиент передаёт параметры, вызывая тот же sp_executesql? Он же не делает выполнение строки, я надеюсь? Как то привязывает параметры, делает вызов...

Может, в API предусмотрено привязывание и передачу параметров только для процедур, а для батча это невозможно?
Или это именно в ADO так сделано? Тогда непонятно, зачем. Почему бы не делать прямой вызов, а не через обёртку?

Может, это трэйс просто так показывает события RPC? В RPC:Completed отображает же он в TextData выходные значения output'ных параметров, так что, возможно, и к остальному тексту прикладывает руку.
4 дек 14, 20:43    [16952346]     Ответить | Цитировать Сообщить модератору
 Re: exec sp_executesql  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7608
Не понятно одно. Почему МС выбрала кас средство доступа к серверу процедурный стэк T-SQL? Вроде-ж ado/ado.net универсальная технология для доступа к любой СУБД. И как оно работает при отсутствии T-SQL? Когда был выпущен на свободу ODBC, было все понятно: вот тебе ODBC API, вот ODBC драйвер от вендора, который транслирует вызовы ODBC API в native CLI соостветствующей базы. А теперь, что все вендоры пишут трансляторы T-SQL в натив?
4 дек 14, 21:16    [16952443]     Ответить | Цитировать Сообщить модератору
 Re: exec sp_executesql  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Relic Hunter
Mind
Потому что снифинг?!?
В общем, как оказалось, никакой не сниффинг. Смена типов параметров с VARCHAR на NVARCHAR поправила ситуацию. Хотя сами колонки WBS1, WBS2, WBS3 - VARCHAR, а не NVARCHAR. Просто у полей WBS1, WBS2, WBS3 локализация была Cyrillic_General_CI_AS и индекс не использовался при передаче параметров в формате VARCHAR.
Где тут смайлик, который бьется головой о стену...
Простите, но логики в вашем высказывании никакой.

Посмотрите еще раз на планы (можете даже выложить их сюда) и подумайте как преобразование типов влияет на cardinality estimates и как следствие на выбор плана. Если дополнительное, неявное преобразование типов улучшает план, то явно тут что-то совсем плохо и лечить запросы путем подсовывание не тех типов, это простите маразм.
4 дек 14, 21:59    [16952578]     Ответить | Цитировать Сообщить модератору
 Re: exec sp_executesql  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
alexeyvg
То есть и тут студия ушла от привязывания параметров средствами API доступа, а тупо передаёт некий текст!
Я думаю что цели и задачи у студии совсем другие, поэтому это тупо не было реализовано.

А еще, кстати, помимо sp_executesql есть и другие интерфейсы, например sp_prepare/sp_prepexec или скажем sp_cursorprepare. Их все нужно было реализовывать в SSMS?


И я до сих пор не понимаю, что за проблема с sp_executesql? Можно передать через ADO текст с переменными и конкатенированными значениями вместо параметров и будет оно передано как прямой запрос.
4 дек 14, 22:37    [16952703]     Ответить | Цитировать Сообщить модератору
 Re: exec sp_executesql  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Relic Hunter
Вроде-ж ado/ado.net универсальная технология для доступа к любой СУБД. И как оно работает при отсутствии T-SQL? Когда был выпущен на свободу ODBC, было все понятно: вот тебе ODBC API, вот ODBC драйвер от вендора, который транслирует вызовы ODBC API в native CLI соостветствующей базы. А теперь, что все вендоры пишут трансляторы T-SQL в натив?
А разве sp_executesql генерируется в ADO? Я всегда считал что ADO не привязано к провайдеру данных, это просто программная обертка над OLEDB или ODBC. ADO вызывает что-то типа SQLBindParameter в ODBC, а та уже в свою очередь генерит sp_executesql и прочее, в зависимости от реализации под конкретную СУБД. Я не прав?
4 дек 14, 22:41    [16952714]     Ответить | Цитировать Сообщить модератору
 Re: exec sp_executesql  [new]
правильный проходящий.
Guest
Mind
А разве sp_executesql генерируется в ADO? Я всегда считал что ADO не привязано к провайдеру данных, это просто программная обертка над OLEDB или ODBC. ADO вызывает что-то типа SQLBindParameter в ODBC, а та уже в свою очередь генерит sp_executesql и прочее, в зависимости от реализации под конкретную СУБД. Я не прав?
Насколько я помню работу с ODBC, там тоже при использовании параметризованных запросов на сервер приходила sp_executesql При неиспользовании параметров на сервер приходил запрос в неизменном виде.
4 дек 14, 23:10    [16952797]     Ответить | Цитировать Сообщить модератору
 Re: exec sp_executesql  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31991
step_ks
Может, это трэйс просто так показывает события RPC? В RPC:Completed отображает же он в TextData выходные значения output'ных параметров, так что, возможно, и к остальному тексту прикладывает руку.
Не, события RPC трейс показывает как RPC:Completed

Relic Hunter
Не понятно одно. Почему МС выбрала кас средство доступа к серверу процедурный стэк T-SQL? Вроде-ж ado/ado.net универсальная технология для доступа к любой СУБД. И как оно работает при отсутствии T-SQL? Когда был выпущен на свободу ODBC, было все понятно: вот тебе ODBC API, вот ODBC драйвер от вендора, который транслирует вызовы ODBC API в native CLI соостветствующей базы. А теперь, что все вендоры пишут трансляторы T-SQL в натив?
Чего-чего?

МС не "выбрала процедурный стэк T-SQL", она позволила его использовать.

Например, если вы используете класс из ado/ado.net, который предназначен для работы исключительно с MSSQL, то странно говорить, почему этот класс использует T-SQL.

Если вы используете прямую передачу текста команд в непреобразованном виде, то тоже странно недоумевать, прочему сиквел получает T-SQL.

А если вы используете класс, предназначенный для любых СУБД, то ado/ado.net, весь стек драйверов в итоге преобразуют вызовы в native CLI MSSQL, то есть в T-SQL (так он виден в трейсе, хотя это не T-SQL, то есть некоторые команды из трейса вы выполнить не можете как T-SQL команды)

В общем, так же работает обращения и к другим СУБД. В трейсе оракла вы тоже увидите не T-SQL команды, а оракловские.
5 дек 14, 00:09    [16952949]     Ответить | Цитировать Сообщить модератору
 Re: exec sp_executesql  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31991
Mind
alexeyvg
То есть и тут студия ушла от привязывания параметров средствами API доступа, а тупо передаёт некий текст!
Я думаю что цели и задачи у студии совсем другие, поэтому это тупо не было реализовано.
Однако ведь могли сделать нормальный параметризованный вызов?

Но там да, непонятно, зачем делать параметризованный вызов, вроде никаких преимуществ он не даёт.

Mind
И я до сих пор не понимаю, что за проблема с sp_executesql? Можно передать через ADO текст с переменными и конкатенированными значениями вместо параметров и будет оно передано как прямой запрос.
Эээ, тут вроде считается акиомой, что параметризованный вызов лучше.
Быстрее, безопаснее хотя бы. SQL инжекшен. Меньше проблем с передачей больших значений - при сохранении 10Мб файла в базу нужно всего лишь передать 10Мб данных, а не парсить, синтаксически анализировать 10 Мб запрос.
Mind
А разве sp_executesql генерируется в ADO? Я всегда считал что ADO не привязано к провайдеру данных, это просто программная обертка над OLEDB или ODBC. ADO вызывает что-то типа SQLBindParameter в ODBC, а та уже в свою очередь генерит sp_executesql и прочее, в зависимости от реализации под конкретную СУБД. Я не прав?
ИМХО это именно ADO.
Причём ИМХО только с текстами, с процедурами по другому.

Всё ИМХО, лет 15 я не писал клиентов, но когда то этим хорошо и много занимался.
5 дек 14, 00:19    [16952967]     Ответить | Цитировать Сообщить модератору
 Re: exec sp_executesql  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7608
alexeyvg
В общем, так же работает обращения и к другим СУБД. В трейсе оракла вы тоже увидите не T-SQL команды, а оракловские.
В оракловом трейсе - одинаково, есть параметры, нет. Стек обработки оператора будет такой-же, и для DML, и для хранимок. В сиквеле-же каждый случай - особый. Может - три, может и больше.

К сообщению приложен файл. Размер - 14Kb
5 дек 14, 00:30    [16952988]     Ответить | Цитировать Сообщить модератору
 Re: exec sp_executesql  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31991
Relic Hunter
alexeyvg
В общем, так же работает обращения и к другим СУБД. В трейсе оракла вы тоже увидите не T-SQL команды, а оракловские.
В оракловом трейсе - одинаково, есть параметры, нет. Стек обработки оператора будет такой-же, и для DML, и для хранимок. В сиквеле-же каждый случай - особый. Может - три, может и больше.
Вопрос был "Почему МС выбрала кас средство доступа к серверу процедурный стэк T-SQL? "
Вот я ответил, что в трейсе оракла вы не увидите команд T-SQL, а увидите исключительно вызовы API оракла.

Остальное я про оракл не писал, остальное было исключительно про сиквел.

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

Ну и вообще, ИМХО вы смешиваете API доступа и передаваемые команды.

API доступа для сиквела, то есть как бы аналог OCI - это OLEDB, он совершенно определённый.

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

Вы уверены, что посылка текста с параметризованной командой и непараметризованной в оракл из ADO, ADO.NET, BDE и ODBC (то есть итого 8 вариантов) приведёт к совершенно одинаковым строкам в трейсе оракла?

Я вот не уверен. Но я не специалист по ораклу, могу только предполагать, из общих соображений.
5 дек 14, 01:14    [16953049]     Ответить | Цитировать Сообщить модератору
 Re: exec sp_executesql  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31991
alexeyvg
Вы уверены, что посылка текста с параметризованной командой и непараметризованной в оракл из ADO, ADO.NET, BDE и ODBC (то есть итого 8 вариантов) приведёт к совершенно одинаковым строкам в трейсе оракла?
Да, и ещё: в ADO.NET к ораклу можно обратиться несколькими путями - используя классы из разных неймспейсов, например System.Data.Odbc, System.Data.OleDb, System.Data.OracleClient
Тут я тоже не уверен в одинаковости получившиххся в итоге команд

В общем, как давно написали:
Glory
Потому что вы для облегчения своего труда используете ADO API, т.е. "обертки", которые создали другие люди.
Как создатель обёртки написал, так и работает, а много обёрток, так ещё и по разному для каждой.
5 дек 14, 01:20    [16953057]     Ответить | Цитировать Сообщить модератору
 Re: exec sp_executesql  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7608
alexeyvg,

В sql оператор параметры передать не представляется возможным, а в процедуру можно. Поэтому, чтобы передать параметры в оператор мы используем процедуры. Но, в конечном итоге, процедура как-то передает параметры в оператор? Как? :)
5 дек 14, 01:39    [16953081]     Ответить | Цитировать Сообщить модератору
 Re: exec sp_executesql  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
alexeyvg
Mind
И я до сих пор не понимаю, что за проблема с sp_executesql? Можно передать через ADO текст с переменными и конкатенированными значениями вместо параметров и будет оно передано как прямой запрос.
Эээ, тут вроде считается акиомой, что параметризованный вызов лучше.
Быстрее, безопаснее хотя бы. SQL инжекшен. Меньше проблем с передачей больших значений - при сохранении 10Мб файла в базу нужно всего лишь передать 10Мб данных, а не парсить, синтаксически анализировать 10 Мб запрос.
Я это понимаю, просто кто-то очень хочет передать "прямой" запрос, что почему то еще должно решить какие то проблемы..
5 дек 14, 01:56    [16953093]     Ответить | Цитировать Сообщить модератору
 Re: exec sp_executesql  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31991
Mind
Я это понимаю, просто кто-то очень хочет передать "прямой" запрос, что почему то еще должно решить какие то проблемы..
Так убирается лишняя прослойка, это же хорошо?
Мне странно читать тексты в трейсе с sp_executesql, зная, что можно обойтись без неё...
Relic Hunter
alexeyvg,

В sql оператор параметры передать не представляется возможным, а в процедуру можно. Поэтому, чтобы передать параметры в оператор мы используем процедуры. Но, в конечном итоге, процедура как-то передает параметры в оператор? Как? :)
Странный вопрос. Процедура не передаёт параметры в оператор. Всё равно что сказать, что функция на С передаёт параметры в выражение, которое встречается в теле этой функции.

А в батч (не в оператор) вроде можно(?) передавать паратемры, просто это почему то не реализовано в ADO.
5 дек 14, 14:03    [16955538]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить