|
параметризация запросов insert
|
|||
---|---|---|---|
#18+
Столкнулся со странным поведением механизма параметризации у SQL Server. Проблема в том, что SQL Server создает разные планы для казалось бы одинаковых запросов на insert. Пример такой CREATE TABLE A (a int, b float) --создаем тестовую табличку GO DBCC FREEPROCCACHE -- чистим операционный кэш, чтобы проще искать результат GO -- вставляем строки insert into A (a, b) values (1, 10000.01) insert into A (a, b) values (1, 990000.019999) GO Смотрим, что у нас лежит в кэше SELECT text, * FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) а там два разных плана (@1 int,@2 numeric(12,6))INSERT INTO [A]([a],[b]) values(@1,@2) (@1 int,@2 numeric(7,2))INSERT INTO [A]([a],[b]) values(@1,@2) Как заставить SQL Server, чтобы он создавал один план в такой ситуации и использовал его повторно? Версия сервера Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) ... |
|||
:
Нравится:
Не нравится:
|
|||
14.10.2013, 10:43 |
|
параметризация запросов insert
|
|||
---|---|---|---|
#18+
artii, Приводить все числа явно к типу определенному, либо включить Forced параметризацию, либо попробовать ручную через sp_executesql. ... |
|||
:
Нравится:
Не нравится:
|
|||
14.10.2013, 10:46 |
|
параметризация запросов insert
|
|||
---|---|---|---|
#18+
Это помогает, к сожалению, только для запросов select Я проверял это все для insert такая же картина с двумя планами. ... |
|||
:
Нравится:
Не нравится:
|
|||
14.10.2013, 10:51 |
|
параметризация запросов insert
|
|||
---|---|---|---|
#18+
artiiЯ проверял это все для insert такая же картина с двумя планами. Что "это" вы проверили ? Предоставьте ваш код с явным приведением типов ... |
|||
:
Нравится:
Не нравится:
|
|||
14.10.2013, 11:00 |
|
параметризация запросов insert
|
|||
---|---|---|---|
#18+
artii, Тогда по ходу печалька : ( ... |
|||
:
Нравится:
Не нравится:
|
|||
14.10.2013, 11:00 |
|
параметризация запросов insert
|
|||
---|---|---|---|
#18+
Glory, Код: sql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18.
Реально не работает : ( ... |
|||
:
Нравится:
Не нравится:
|
|||
14.10.2013, 11:02 |
|
параметризация запросов insert
|
|||
---|---|---|---|
#18+
Код: sql 1. 2. 3.
а так? ... |
|||
:
Нравится:
Не нравится:
|
|||
14.10.2013, 11:08 |
|
параметризация запросов insert
|
|||
---|---|---|---|
#18+
kalimbaРеально не работает : ( declare @x decimal (16,7) set @x = 10000.01 INSERT INTO A (a, b) values (1, @x) set @x = 990000.019999 INSERT INTO A (a, b) values (1, @x) ... |
|||
:
Нравится:
Не нравится:
|
|||
14.10.2013, 11:10 |
|
параметризация запросов insert
|
|||
---|---|---|---|
#18+
Knyazev Alexey Код: sql 1. 2. 3.
а так? так лучше declare @cmd nvarchar(1024) = 'insert into A (a, b) values ( @p1, @p2 )' exec sp_executesql @cmd, N'@p1 int, @p2 float', @p1 = 1, @p2 = 10000.01 exec sp_executesql @cmd, N'@p1 int, @p2 float', @p1 = 1, @p2 = 990000.019999 (@p1 int, @p2 float)insert into A (a, b) values ( @p1, @p2 ) ... |
|||
:
Нравится:
Не нравится:
|
|||
14.10.2013, 11:11 |
|
параметризация запросов insert
|
|||
---|---|---|---|
#18+
GlorykalimbaРеально не работает : ( declare @x decimal (16,7) set @x = 10000.01 INSERT INTO A (a, b) values (1, @x) set @x = 990000.019999 INSERT INTO A (a, b) values (1, @x) Такой вариант, вообще не приводит к параметризации ... |
|||
:
Нравится:
Не нравится:
|
|||
14.10.2013, 11:13 |
|
параметризация запросов insert
|
|||
---|---|---|---|
#18+
artiiТакой вариант, вообще не приводит к параметризации А к чему он приводит ? ) ... |
|||
:
Нравится:
Не нравится:
|
|||
14.10.2013, 11:16 |
|
параметризация запросов insert
|
|||
---|---|---|---|
#18+
к формированию не параметризованного плана для конкретного батча. declare @x decimal (16,7) set @x = 10000.01 INSERT INTO A (a, b) values (1, @x) set @x = 990000.019999 INSERT INTO A (a, b) values (1, @x) ... |
|||
:
Нравится:
Не нравится:
|
|||
14.10.2013, 11:28 |
|
параметризация запросов insert
|
|||
---|---|---|---|
#18+
Knyazev Alexey Код: sql 1. 2. 3.
а так? Отдельное спасибо за такой вариант, но он мне не совсем подходит, из-за низкой производительности такого подхода. Я накопал такой вариант, как создание структуры плана для связывания запросов через sp_create_plan_guide. Попробовал создать структуру плана, но в итоге опять создается два плана. Пример: EXEC sp_create_plan_guide @name = N'update_A', @stmt = N'insert into A (a, b) values ( @p1, @p2 )', @type = N'SQL', @module_or_batch = NULL, @params = N'@p1 int, @p2 float', @hints = BULL GO DBCC FREEPROCCACHE GO insert into A (a, b) values (1, 10000.01) insert into A (a, b) values (1, 990000.019999) GO на выходе опять (@1 int,@2 numeric(12,6))INSERT INTO [A]([a],[b]) values(@1,@2) (@1 int,@2 numeric(7,2))INSERT INTO [A]([a],[b]) values(@1,@2) посмотреть и удалить схемы можно так SELECT * FROM sys.plan_guides exec sp_control_plan_guide Вопрос будет создание схемы аналогом вызова sp_executesql с параметрами? ... |
|||
:
Нравится:
Не нравится:
|
|||
14.10.2013, 13:54 |
|
параметризация запросов insert
|
|||
---|---|---|---|
#18+
artiiKnyazev Alexey Код: sql 1. 2. 3.
а так? он мне не совсем подходит, из-за низкой производительности такого подхода. в чём это проявляется? ... |
|||
:
Нравится:
Не нравится:
|
|||
14.10.2013, 14:26 |
|
параметризация запросов insert
|
|||
---|---|---|---|
#18+
а сделать хранимку (вплоть до временной) - не предлагать? ... |
|||
:
Нравится:
Не нравится:
|
|||
14.10.2013, 14:54 |
|
параметризация запросов insert
|
|||
---|---|---|---|
#18+
У меня работа с БД при загрузке данных построена таким образом. Запросы insert(update) склеиваются в один батч от одного до N шт. При низкой нагрузке в БД могут ехать отдельные запросы при высокой запросы объединяются в один пакет. т.е. into (..) values () into (..) values () into (..) values () into (..) values () into (..) values () GO Такой подход позволяет не ждать ответа от сервера после каждого запроса, если запросы выполняются по одному и SQL Server и приложения стоят на разных машинах. А ожидать ответ только после отправки пачки запросов. Пока это написал я понял, что я не совсем прав, говоря, что этот подход с sp_executesql будет медленнее, чем описанный выше. Я предполагал, что sp_executesql будет делаться для каждого insert или update по-отдельности, что конечно же не так и можно переделать приложение, подставляя в вызов sp_executesql 20 строк на вставку, но это как-то не красиво и не спортивно. Хотелось бы выкрутить руки SQL Server, чтобы он таки нашел нужный план плану у себя к кэше и не создавал новый. ... |
|||
:
Нравится:
Не нравится:
|
|||
14.10.2013, 15:09 |
|
параметризация запросов insert
|
|||
---|---|---|---|
#18+
artiiзапросы объединяются в один пакет. т.е. into (..) values () into (..) values () into (..) values () into (..) values () into (..) values () GO Каким образом вы определили, что ваше узкое место — это компиляция запросов? ... |
|||
:
Нравится:
Не нравится:
|
|||
14.10.2013, 15:23 |
|
параметризация запросов insert
|
|||
---|---|---|---|
#18+
p.s. а вы боретесь со скоростью вставки? ну не планы ради планов же? тогда (1) хранимки и (2) оборачивать по ~ 100..1000..10000 вставок в транзакцию. но с транзакцией будет попадалово с ошибками, да.. и будет вам быстро. ну или примеряйте способы передавать не по 1 записи, а пачками сразу на сиквел - тоже будет быстрее ... |
|||
:
Нравится:
Не нравится:
|
|||
14.10.2013, 15:24 |
|
параметризация запросов insert
|
|||
---|---|---|---|
#18+
artiiПри низкой нагрузке в БД могут ехать отдельные запросы при высокой запросы объединяются в один пакет. т.е. into (..) values () into (..) values () into (..) values () into (..) values () into (..) values () GO а зачем так много букв, пишите сразу одной командой insert into (..) values (), (), (), ... и будет один план на пакет ... |
|||
:
Нравится:
Не нравится:
|
|||
14.10.2013, 15:29 |
|
параметризация запросов insert
|
|||
---|---|---|---|
#18+
Crimeanp.s. а вы боретесь со скоростью вставки? ну не планы ради планов же? тогда (1) хранимки и (2) оборачивать по ~ 100..1000..10000 вставок в транзакцию. но с транзакцией будет попадалово с ошибками, да.. и будет вам быстро. ну или примеряйте способы передавать не по 1 записи, а пачками сразу на сиквел - тоже будет быстрее Вопрос быстродействия уже решен до начала этой темы, путем вызова ХП, в которой все строки вставляются одним запросом. Но в процессе понимания, что происходило при тормозах на SQ Server единственное, что было не понятно это зачем SQL Server постоянно генерит новые планы для казалось бы одинаковых запросов, к тому же эти планы далеко не такие безобидные и потребляют довольно много памяти в моем случае это было около 1,3 Гб при этом часть из этих планов удаляется в процессе вставки 500 тыс строк. ... |
|||
:
Нравится:
Не нравится:
|
|||
15.10.2013, 07:26 |
|
параметризация запросов insert
|
|||
---|---|---|---|
#18+
ShakillartiiПри низкой нагрузке в БД могут ехать отдельные запросы при высокой запросы объединяются в один пакет. т.е. into (..) values () into (..) values () into (..) values () into (..) values () into (..) values () GO а зачем так много букв, пишите сразу одной командой insert into (..) values (), (), (), ... и будет один план на пакет Не знал про такой синтаксис, спасибо, но мне не подходит вариант, запрос откатывает все вставленные строки, если хотя бы одна вызывает ошибку например PK_violation ... |
|||
:
Нравится:
Не нравится:
|
|||
15.10.2013, 07:35 |
|
параметризация запросов insert
|
|||
---|---|---|---|
#18+
Гость333artiiзапросы объединяются в один пакет. т.е. into (..) values () into (..) values () into (..) values () into (..) values () into (..) values () GO Каким образом вы определили, что ваше узкое место — это компиляция запросов? Знаете, я не могу достоверно говорить, как я это определил я просто скажу что произошло совершенно случайно, я при проявлении этих тормозов, решил очистить процедурный кэш, ну так ради спортивного интереса, DBCC FREEPROCCACHE после этого все просто полетело, но через некоторое время все опять начало тормозить порядок тормозов следующий, запросы при тормозах просасывались по 20 шт за 1 сек, после очистки по несколько тыс за сек Далее начал смотреть, а что лежит в кэше и увидел , что практически весь процедурный кеш забит запросами к одной таблице, 80% от этих запросов имею UseCount 1 или 2. И этот факт вызвал большие вопросы т.к. запросы в целом одинаковые, т.е. insert содержит одинаковый набор полей в одинаковом порядке с одинаковыми отступами, пробелами и регистром буквенных символов отличаются только значениями полей полей типа float. Эти значения в планах приводятся к параметрам разных вариантов типа numeric(12,6), numeric(7,2) и т.д этих полей порядка 15 и получается, что с высокой вероятностью создается новый план. После этого я начал пытаться заставить SQL server использовать уже существующие планы, пока правда безуспешно, не считая вариант с параметизацией запросов через sp_executesql Ну и в конце поста я хотел бы акцентировать внимание на вашем вопросе, скорее всего компиляция планов не является узким местом т.к. после очистки кэша все летит и запросы компилируются со свистом, но проблема все-равно где-то в процедурном кэше возможно в поиске готовых планов. ... |
|||
:
Нравится:
Не нравится:
|
|||
15.10.2013, 08:09 |
|
параметризация запросов insert
|
|||
---|---|---|---|
#18+
artiiединственное, что было не понятно это зачем SQL Server постоянно генерит новые планы для казалось бы одинаковых запросовЗапросы же не одинаковые. Если хоть байт в тексте отличается, то это другой запрос. Чтобы уменьшить расходы на компиляцию, нужно либо включить специальную опцию, либо не передавать запросы в виде текста. Ну и вообще есть способы и в сотни раз увеличить скорость, просто вы говорите, что скорость вас не интересует, что вы выясняете конкретный вопрос поведения SQL Server... ... |
|||
:
Нравится:
Не нравится:
|
|||
15.10.2013, 08:12 |
|
параметризация запросов insert
|
|||
---|---|---|---|
#18+
artiiНу и в конце поста я хотел бы акцентировать внимание на вашем вопросе, скорее всего компиляция планов не является узким местом т.к. после очистки кэша все летит и запросы компилируются со свистом, но проблема все-равно где-то в процедурном кэше возможно в поиске готовых планов.Это взаимосвязанные вещи. Сама по себе компиляция такого простого запроса не создаёт нагрузки, но мллионы запросов - это миллионы хранящихся в кеше планов, и естественно серверу становится плохо. Поэтому и нужно избавляться от лишних компиляций и лишних планов. ... |
|||
:
Нравится:
Не нравится:
|
|||
15.10.2013, 08:23 |
|
параметризация запросов insert
|
|||
---|---|---|---|
#18+
alexeyvgЗапросы же не одинаковые. Если хоть байт в тексте отличается, то это другой запрос. Чтобы уменьшить расходы на компиляцию, нужно либо включить специальную опцию, либо не передавать запросы в виде текста. Ну и вообще есть способы и в сотни раз увеличить скорость, просто вы говорите, что скорость вас не интересует, что вы выясняете конкретный вопрос поведения SQL Server... Значения в запросах не одинаковые, но это как бы не должно играть ни какой роли, если запрос имеет параметризованный план. Для этого собственно и нужна параметризация. Например для запросов Select в простом текстовом виде, если план параметризуется, добиться повторного использования параметризованного плана довольно просто, достаточно привести нужную константу к нужному типу и соотв параметр в плане примет указанный тип. С инсертами сложнее нужно использовать sp_exeutesql, возможно sp_create_plan_guide делает так же как и sp_exeutesql, но мне пока не удается его распинать применительно к инсертам. Может кто-то уже использовал sp_create_plan_guide в таком же контексте как я и знает как создать правильную схему для планов? ... |
|||
:
Нравится:
Не нравится:
|
|||
15.10.2013, 08:34 |
|
|
start [/forum/topic.php?fid=46&msg=38426197&tid=1704757]: |
0ms |
get settings: |
9ms |
get forum list: |
14ms |
check forum access: |
4ms |
check topic access: |
4ms |
track hit: |
173ms |
get topic data: |
10ms |
get forum data: |
2ms |
get page messages: |
66ms |
get tp. blocked users: |
1ms |
others: | 246ms |
total: | 529ms |
0 / 0 |