powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / параметризация запросов insert
25 сообщений из 71, страница 1 из 3
параметризация запросов insert
    #38426167
artii
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Столкнулся со странным поведением механизма параметризации у 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)
...
Рейтинг: 0 / 0
параметризация запросов insert
    #38426175
kalimba
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
artii,

Приводить все числа явно к типу определенному, либо включить Forced параметризацию, либо попробовать ручную через sp_executesql.
...
Рейтинг: 0 / 0
параметризация запросов insert
    #38426181
artii
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Это помогает, к сожалению, только для запросов select
Я проверял это все для insert такая же картина с двумя планами.
...
Рейтинг: 0 / 0
параметризация запросов insert
    #38426197
Glory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
artiiЯ проверял это все для insert такая же картина с двумя планами.
Что "это" вы проверили ?
Предоставьте ваш код с явным приведением типов
...
Рейтинг: 0 / 0
параметризация запросов insert
    #38426198
kalimba
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
artii,

Тогда по ходу печалька : (
...
Рейтинг: 0 / 0
параметризация запросов insert
    #38426200
kalimba
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Glory,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
DROP TABLE A
CREATE TABLE A (a int, b decimal(16, 7)) --создаем тестовую табличку
GO

DBCC FREEPROCCACHE -- чистим операционный кэш, чтобы проще искать результат
GO

-- вставляем строки
INSERT INTO A (a, b) values (1, CAST(10000.01 AS decimal(16, 7))) 
INSERT INTO A (a, b) values (1, CAST(990000.019999 AS decimal(16, 7)))
GO

--Смотрим, что у нас лежит в кэше 
SELECT text, *
FROM
	sys.dm_exec_cached_plans cp
	CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle)
OPTION (RECOMPILE)


Реально не работает : (
...
Рейтинг: 0 / 0
параметризация запросов insert
    #38426216
Фотография 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 numeric(12,6)', @p1 = 1, @p2 = 10000.01
exec sp_executesql @cmd, N'@p1 int, @p2 numeric(12,6)', @p1 = 1, @p2 = 990000.019999



а так?
...
Рейтинг: 0 / 0
параметризация запросов insert
    #38426220
Glory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
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)
...
Рейтинг: 0 / 0
параметризация запросов insert
    #38426225
artii
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
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 numeric(12,6)', @p1 = 1, @p2 = 10000.01
exec sp_executesql @cmd, N'@p1 int, @p2 numeric(12,6)', @p1 = 1, @p2 = 990000.019999



а так?

так лучше

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 )
...
Рейтинг: 0 / 0
параметризация запросов insert
    #38426226
artii
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
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)

Такой вариант, вообще не приводит к параметризации
...
Рейтинг: 0 / 0
параметризация запросов insert
    #38426231
Glory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
artiiТакой вариант, вообще не приводит к параметризации
А к чему он приводит ? )
...
Рейтинг: 0 / 0
параметризация запросов insert
    #38426253
artii
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
к формированию не параметризованного плана для конкретного батча.

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)
...
Рейтинг: 0 / 0
параметризация запросов insert
    #38426574
artii
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
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 numeric(12,6)', @p1 = 1, @p2 = 10000.01
exec sp_executesql @cmd, N'@p1 int, @p2 numeric(12,6)', @p1 = 1, @p2 = 990000.019999



а так?

Отдельное спасибо за такой вариант, но он мне не совсем подходит, из-за низкой производительности такого подхода.

Я накопал такой вариант, как создание структуры плана для связывания запросов через 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 с параметрами?
...
Рейтинг: 0 / 0
параметризация запросов insert
    #38426656
Фотография Knyazev Alexey
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
artiiKnyazev 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 numeric(12,6)', @p1 = 1, @p2 = 10000.01
exec sp_executesql @cmd, N'@p1 int, @p2 numeric(12,6)', @p1 = 1, @p2 = 990000.019999




а так?

он мне не совсем подходит, из-за низкой производительности такого подхода.


в чём это проявляется?
...
Рейтинг: 0 / 0
параметризация запросов insert
    #38426706
Crimean
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
а сделать хранимку (вплоть до временной) - не предлагать?
...
Рейтинг: 0 / 0
параметризация запросов insert
    #38426746
artii
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
У меня работа с БД при загрузке данных построена таким образом.
Запросы 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, чтобы он таки нашел нужный план плану у себя к кэше и не создавал новый.
...
Рейтинг: 0 / 0
параметризация запросов insert
    #38426768
Гость333
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
artiiзапросы объединяются в один пакет. т.е.
into (..) values ()
into (..) values ()
into (..) values ()
into (..) values ()
into (..) values ()
GO
Каким образом вы определили, что ваше узкое место — это компиляция запросов?
...
Рейтинг: 0 / 0
параметризация запросов insert
    #38426769
Crimean
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
p.s.

а вы боретесь со скоростью вставки? ну не планы ради планов же? тогда (1) хранимки и (2) оборачивать по ~ 100..1000..10000 вставок в транзакцию. но с транзакцией будет попадалово с ошибками, да.. и будет вам быстро. ну или примеряйте способы передавать не по 1 записи, а пачками сразу на сиквел - тоже будет быстрее
...
Рейтинг: 0 / 0
параметризация запросов insert
    #38426782
Фотография Shakill
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
artiiПри низкой нагрузке в БД могут ехать отдельные запросы при высокой запросы объединяются в один пакет. т.е.
into (..) values ()
into (..) values ()
into (..) values ()
into (..) values ()
into (..) values ()
GO а зачем так много букв, пишите сразу одной командой insert into (..) values (), (), (), ... и будет один план на пакет
...
Рейтинг: 0 / 0
параметризация запросов insert
    #38427513
artii
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Crimeanp.s.

а вы боретесь со скоростью вставки? ну не планы ради планов же? тогда (1) хранимки и (2) оборачивать по ~ 100..1000..10000 вставок в транзакцию. но с транзакцией будет попадалово с ошибками, да.. и будет вам быстро. ну или примеряйте способы передавать не по 1 записи, а пачками сразу на сиквел - тоже будет быстрее

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

Но в процессе понимания, что происходило при тормозах на SQ Server единственное, что было не понятно это зачем SQL Server постоянно генерит новые планы для казалось бы одинаковых запросов, к тому же эти планы далеко не такие безобидные и потребляют довольно много памяти в моем случае это было около 1,3 Гб при этом часть из этих планов удаляется в процессе вставки 500 тыс строк.
...
Рейтинг: 0 / 0
параметризация запросов insert
    #38427516
artii
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
ShakillartiiПри низкой нагрузке в БД могут ехать отдельные запросы при высокой запросы объединяются в один пакет. т.е.
into (..) values ()
into (..) values ()
into (..) values ()
into (..) values ()
into (..) values ()
GO а зачем так много букв, пишите сразу одной командой insert into (..) values (), (), (), ... и будет один план на пакет

Не знал про такой синтаксис, спасибо, но мне не подходит вариант, запрос откатывает все вставленные строки, если хотя бы одна вызывает ошибку например PK_violation
...
Рейтинг: 0 / 0
параметризация запросов insert
    #38427528
artii
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Гость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

Ну и в конце поста я хотел бы акцентировать внимание на вашем вопросе, скорее всего компиляция планов не является узким местом т.к. после очистки кэша все летит и запросы компилируются со свистом, но проблема все-равно где-то в процедурном кэше возможно в поиске готовых планов.
...
Рейтинг: 0 / 0
параметризация запросов insert
    #38427531
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
artiiединственное, что было не понятно это зачем SQL Server постоянно генерит новые планы для казалось бы одинаковых запросовЗапросы же не одинаковые. Если хоть байт в тексте отличается, то это другой запрос.
Чтобы уменьшить расходы на компиляцию, нужно либо включить специальную опцию, либо не передавать запросы в виде текста.

Ну и вообще есть способы и в сотни раз увеличить скорость, просто вы говорите, что скорость вас не интересует, что вы выясняете конкретный вопрос поведения SQL Server...
...
Рейтинг: 0 / 0
параметризация запросов insert
    #38427536
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
artiiНу и в конце поста я хотел бы акцентировать внимание на вашем вопросе, скорее всего компиляция планов не является узким местом т.к. после очистки кэша все летит и запросы компилируются со свистом, но проблема все-равно где-то в процедурном кэше возможно в поиске готовых планов.Это взаимосвязанные вещи. Сама по себе компиляция такого простого запроса не создаёт нагрузки, но мллионы запросов - это миллионы хранящихся в кеше планов, и естественно серверу становится плохо. Поэтому и нужно избавляться от лишних компиляций и лишних планов.
...
Рейтинг: 0 / 0
параметризация запросов insert
    #38427548
artii
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
alexeyvgЗапросы же не одинаковые. Если хоть байт в тексте отличается, то это другой запрос.
Чтобы уменьшить расходы на компиляцию, нужно либо включить специальную опцию, либо не передавать запросы в виде текста.

Ну и вообще есть способы и в сотни раз увеличить скорость, просто вы говорите, что скорость вас не интересует, что вы выясняете конкретный вопрос поведения SQL Server...

Значения в запросах не одинаковые, но это как бы не должно играть ни какой роли, если запрос имеет параметризованный план.
Для этого собственно и нужна параметризация. Например для запросов Select в простом текстовом виде, если план параметризуется, добиться повторного использования параметризованного плана довольно просто, достаточно привести нужную константу к нужному типу и соотв параметр в плане примет указанный тип. С инсертами сложнее нужно использовать sp_exeutesql, возможно sp_create_plan_guide делает так же как и sp_exeutesql, но мне пока не удается его распинать применительно к инсертам.

Может кто-то уже использовал sp_create_plan_guide в таком же контексте как я и знает как создать правильную схему для планов?
...
Рейтинг: 0 / 0
25 сообщений из 71, страница 1 из 3
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / параметризация запросов insert
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]