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

Откуда:
Сообщений: 104
Столкнулся со странным поведением механизма параметризации у 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 окт 13, 10:43    [14965085]     Ответить | Цитировать Сообщить модератору
 Re: параметризация запросов insert  [new]
kalimba
Member

Откуда:
Сообщений: 297
artii,

Приводить все числа явно к типу определенному, либо включить Forced параметризацию, либо попробовать ручную через sp_executesql.
14 окт 13, 10:46    [14965111]     Ответить | Цитировать Сообщить модератору
 Re: параметризация запросов insert  [new]
artii
Member

Откуда:
Сообщений: 104
Это помогает, к сожалению, только для запросов select
Я проверял это все для insert такая же картина с двумя планами.
14 окт 13, 10:51    [14965142]     Ответить | Цитировать Сообщить модератору
 Re: параметризация запросов insert  [new]
Glory
Member

Откуда:
Сообщений: 104760
artii
Я проверял это все для insert такая же картина с двумя планами.

Что "это" вы проверили ?
Предоставьте ваш код с явным приведением типов
14 окт 13, 11:00    [14965217]     Ответить | Цитировать Сообщить модератору
 Re: параметризация запросов insert  [new]
kalimba
Member

Откуда:
Сообщений: 297
artii,

Тогда по ходу печалька : (
14 окт 13, 11:00    [14965218]     Ответить | Цитировать Сообщить модератору
 Re: параметризация запросов insert  [new]
kalimba
Member

Откуда:
Сообщений: 297
Glory,

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)

Реально не работает : (
14 окт 13, 11:02    [14965233]     Ответить | Цитировать Сообщить модератору
 Re: параметризация запросов insert  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
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


а так?
14 окт 13, 11:08    [14965285]     Ответить | Цитировать Сообщить модератору
 Re: параметризация запросов insert  [new]
Glory
Member

Откуда:
Сообщений: 104760
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 окт 13, 11:10    [14965297]     Ответить | Цитировать Сообщить модератору
 Re: параметризация запросов insert  [new]
artii
Member

Откуда:
Сообщений: 104
Knyazev Alexey
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 )
14 окт 13, 11:11    [14965310]     Ответить | Цитировать Сообщить модератору
 Re: параметризация запросов insert  [new]
artii
Member

Откуда:
Сообщений: 104
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)


Такой вариант, вообще не приводит к параметризации
14 окт 13, 11:13    [14965314]     Ответить | Цитировать Сообщить модератору
 Re: параметризация запросов insert  [new]
Glory
Member

Откуда:
Сообщений: 104760
artii
Такой вариант, вообще не приводит к параметризации

А к чему он приводит ? )
14 окт 13, 11:16    [14965334]     Ответить | Цитировать Сообщить модератору
 Re: параметризация запросов insert  [new]
artii
Member

Откуда:
Сообщений: 104
к формированию не параметризованного плана для конкретного батча.

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 окт 13, 11:28    [14965454]     Ответить | Цитировать Сообщить модератору
 Re: параметризация запросов insert  [new]
artii
Member

Откуда:
Сообщений: 104
Knyazev Alexey
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 с параметрами?
14 окт 13, 13:54    [14966809]     Ответить | Цитировать Сообщить модератору
 Re: параметризация запросов insert  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
artii
Knyazev Alexey
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



а так?


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


в чём это проявляется?
14 окт 13, 14:26    [14967083]     Ответить | Цитировать Сообщить модератору
 Re: параметризация запросов insert  [new]
Crimean
Member

Откуда:
Сообщений: 13148
а сделать хранимку (вплоть до временной) - не предлагать?
14 окт 13, 14:54    [14967326]     Ответить | Цитировать Сообщить модератору
 Re: параметризация запросов insert  [new]
artii
Member

Откуда:
Сообщений: 104
У меня работа с БД при загрузке данных построена таким образом.
Запросы 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 окт 13, 15:09    [14967490]     Ответить | Цитировать Сообщить модератору
 Re: параметризация запросов insert  [new]
Гость333
Member

Откуда:
Сообщений: 3683
artii
запросы объединяются в один пакет. т.е.
into (..) values ()
into (..) values ()
into (..) values ()
into (..) values ()
into (..) values ()
GO

Каким образом вы определили, что ваше узкое место — это компиляция запросов?
14 окт 13, 15:23    [14967606]     Ответить | Цитировать Сообщить модератору
 Re: параметризация запросов insert  [new]
Crimean
Member

Откуда:
Сообщений: 13148
p.s.

а вы боретесь со скоростью вставки? ну не планы ради планов же? тогда (1) хранимки и (2) оборачивать по ~ 100..1000..10000 вставок в транзакцию. но с транзакцией будет попадалово с ошибками, да.. и будет вам быстро. ну или примеряйте способы передавать не по 1 записи, а пачками сразу на сиквел - тоже будет быстрее
14 окт 13, 15:24    [14967614]     Ответить | Цитировать Сообщить модератору
 Re: параметризация запросов insert  [new]
Shakill
Member

Откуда: мск
Сообщений: 1880
artii
При низкой нагрузке в БД могут ехать отдельные запросы при высокой запросы объединяются в один пакет. т.е.
into (..) values ()
into (..) values ()
into (..) values ()
into (..) values ()
into (..) values ()
GO
а зачем так много букв, пишите сразу одной командой insert into (..) values (), (), (), ... и будет один план на пакет
14 окт 13, 15:29    [14967646]     Ответить | Цитировать Сообщить модератору
 Re: параметризация запросов insert  [new]
artii
Member

Откуда:
Сообщений: 104
Crimean
p.s.

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


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

Но в процессе понимания, что происходило при тормозах на SQ Server единственное, что было не понятно это зачем SQL Server постоянно генерит новые планы для казалось бы одинаковых запросов, к тому же эти планы далеко не такие безобидные и потребляют довольно много памяти в моем случае это было около 1,3 Гб при этом часть из этих планов удаляется в процессе вставки 500 тыс строк.
15 окт 13, 07:26    [14970578]     Ответить | Цитировать Сообщить модератору
 Re: параметризация запросов insert  [new]
artii
Member

Откуда:
Сообщений: 104
Shakill
artii
При низкой нагрузке в БД могут ехать отдельные запросы при высокой запросы объединяются в один пакет. т.е.
into (..) values ()
into (..) values ()
into (..) values ()
into (..) values ()
into (..) values ()
GO
а зачем так много букв, пишите сразу одной командой insert into (..) values (), (), (), ... и будет один план на пакет


Не знал про такой синтаксис, спасибо, но мне не подходит вариант, запрос откатывает все вставленные строки, если хотя бы одна вызывает ошибку например PK_violation
15 окт 13, 07:35    [14970584]     Ответить | Цитировать Сообщить модератору
 Re: параметризация запросов insert  [new]
artii
Member

Откуда:
Сообщений: 104
Гость333
artii
запросы объединяются в один пакет. т.е.
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 окт 13, 08:09    [14970625]     Ответить | Цитировать Сообщить модератору
 Re: параметризация запросов insert  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
artii
единственное, что было не понятно это зачем SQL Server постоянно генерит новые планы для казалось бы одинаковых запросов
Запросы же не одинаковые. Если хоть байт в тексте отличается, то это другой запрос.
Чтобы уменьшить расходы на компиляцию, нужно либо включить специальную опцию, либо не передавать запросы в виде текста.

Ну и вообще есть способы и в сотни раз увеличить скорость, просто вы говорите, что скорость вас не интересует, что вы выясняете конкретный вопрос поведения SQL Server...
15 окт 13, 08:12    [14970636]     Ответить | Цитировать Сообщить модератору
 Re: параметризация запросов insert  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
artii
Ну и в конце поста я хотел бы акцентировать внимание на вашем вопросе, скорее всего компиляция планов не является узким местом т.к. после очистки кэша все летит и запросы компилируются со свистом, но проблема все-равно где-то в процедурном кэше возможно в поиске готовых планов.
Это взаимосвязанные вещи. Сама по себе компиляция такого простого запроса не создаёт нагрузки, но мллионы запросов - это миллионы хранящихся в кеше планов, и естественно серверу становится плохо. Поэтому и нужно избавляться от лишних компиляций и лишних планов.
15 окт 13, 08:23    [14970655]     Ответить | Цитировать Сообщить модератору
 Re: параметризация запросов insert  [new]
artii
Member

Откуда:
Сообщений: 104
alexeyvg
Запросы же не одинаковые. Если хоть байт в тексте отличается, то это другой запрос.
Чтобы уменьшить расходы на компиляцию, нужно либо включить специальную опцию, либо не передавать запросы в виде текста.

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


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

Может кто-то уже использовал sp_create_plan_guide в таком же контексте как я и знает как создать правильную схему для планов?
15 окт 13, 08:34    [14970683]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить