Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Параметр табличного типа. UDT и XML. Что выбрать?  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 467
Доброго времени суток.
Структура передаваемой в ХП таблицы примерно такая:
CREATE TYPE [dbo].[TListOfLinks] AS TABLE(
	[ID] [int] NULL,
	[IDEnt1] [uniqueidentifier] NULL,
	[IDEnt2] [uniqueidentifier] NULL,
	[Type] [int] NULL,
	[JoinType] [tinyint] NULL,
	[IDUser] [uniqueidentifier] NULL,
	[Date] [datetime] NULL,
	[Status] [bigint] NULL,
	[IDEnt3] [bigint] NULL,
	[IDEnt4] [int] NULL,
	[DedicatedDown] [bit] NULL
) 

Для передачи данных (примерно 13000 строк) использовал указанные в названии темы способы.
Для пустой ХП (+ блок парсинга во временную таблицу в случае XML-параметра) время выполнения варианта с UDT 12с, XML - 3c.
UDT -вариант:
SET @stTime = sysdatetime()
declare @p1 dbo.TListOfLinks
insert into @p1 ...
--13 000 insert'ов
exec MySP
print DATEDIFF(ms, @stTime,sysdatetime())

Вот что странно. Выводимое мной время выполнения 5,7с, а Managment studio показывает 12.
На что тратятся 6,3 секунды и откуда они вообще берутся?

XML -вариант:
SET @stTime = sysdatetime()
exec MySP @links = <длинный-длинный XML>
print DATEDIFF(ms, @stTime,sysdatetime())

Время выполнения, выводимое мной 1,2c; отображаемое Managment studio - 3c.
При повторном запуске обоих вариантов время выполнения снижается, даже если предварительно запускать
CHECKPOINT
DBCC DROPCLEANBUFFERS


Второй запуск.
UDT: отображаемое мной время выполнения 400мс, Managment Studio 7 с.
XML: Время выполнения, выводимое мной отображаемое Managment studio совпадают(1с)

Третий запуск.
UDT: отображаемое мной время выполнения 400мс, Managment Studio 2 с.
XML: Время выполнения, выводимое мной отображаемое Managment studio совпадают(1с)

Очевидно, формируется какой-то кэш, который я не сбрасываю. Что это за кэш?
Вероятно он как-то связан с текущим соединением - после пересоединения время выполнения идентично первому запуску.
Если это так, то как сбрасывать кэш текущего соединения (DROPCLEANBUFFERS не помогает).

P.S. Экспорт в txt и BULK INSERT пока не тестировал. Количество передаваемых строк в ХП чаще бывает небольшим(1-20). Имеет ли вообще смысл использовать здесь BULK INSERT? Максимальное количество передаваемых строк ~ 50к.
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) 
	Jun 28 2012 08:36:30 
	Copyright (c) Microsoft Corporation
	Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1
5 окт 13, 14:41    [14927711]     Ответить | Цитировать Сообщить модератору
 Re: Параметр табличного типа. UDT и XML. Что выбрать?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Шамиль Фаридович
На что тратятся 6,3 секунды?

На компиляцию запроса, например.

Шамиль Фаридович
Очевидно, формируется какой-то кэш, который я не сбрасываю.

DBCC FREEPROCCACHE
5 окт 13, 15:25    [14927808]     Ответить | Цитировать Сообщить модератору
 Re: Параметр табличного типа. UDT и XML. Что выбрать?  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 467
Гость333
Шамиль Фаридович
На что тратятся 6,3 секунды?

На компиляцию запроса, например.

Этот вопрос разрешен, спасибо!
Интересно, почему при повторном запуске UDT-варианта чистое время выполнения пакета падает с 5,7 до 0,4c (чистые буферы из буферного пула удаляются, кэши планов очищаются)? После реконнекта снова 5.7.
5 окт 13, 16:27    [14927952]     Ответить | Цитировать Сообщить модератору
 Re: Параметр табличного типа. UDT и XML. Что выбрать?  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 467
Пока решил остановиться на XML. На приведенном выше примере этот вариант показывает лучшую производительность.
Однако вопрос про "кэш соединения" остается открытым.
7 окт 13, 09:22    [14932074]     Ответить | Цитировать Сообщить модератору
 Re: Параметр табличного типа. UDT и XML. Что выбрать?  [new]
Glory
Member

Откуда:
Сообщений: 104760
Шамиль Фаридович
Однако вопрос про "кэш соединения" остается открытым.

Вы сраниваете 2 разных скрипта только по общему времени выполнения ???
Откауда вы тогда знаете, на что вообще тратится время ?
Например, у вас --13 000 insert'ов, каждый из которых при отсутствии set nocount on будет возвращать клиенте результат
7 окт 13, 09:29    [14932112]     Ответить | Цитировать Сообщить модератору
 Re: Параметр табличного типа. UDT и XML. Что выбрать?  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 467
В первую очередь меня интересует время выполнения. А на что бы вы посоветовали обратить внимание?
set nocount был включен.
К сожалению не могу вложить файл больше 300кб, поэтому прикладываю insert 3000 строк. Здесь время выполнения первого запуска 1,7c, последующих ~100мс. Откуда берется эта разница?

К сообщению приложен файл (Debug.zip - 104Kb) cкачать
7 окт 13, 15:32    [14934508]     Ответить | Цитировать Сообщить модератору
 Re: Параметр табличного типа. UDT и XML. Что выбрать?  [new]
invm
Member

Откуда: Москва
Сообщений: 9396
Шамиль Фаридович
Здесь время выполнения первого запуска 1,7c, последующих ~100мс. Откуда берется эта разница?
Вам же уже отвечали - компиляция пакета. Включите set statistics time и все сами увидите.
7 окт 13, 15:45    [14934605]     Ответить | Цитировать Сообщить модератору
 Re: Параметр табличного типа. UDT и XML. Что выбрать?  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 467
Дык я использую DBCC FREEPROCCACHE, а время все равно уменьшается.
7 окт 13, 15:57    [14934700]     Ответить | Цитировать Сообщить модератору
 Re: Параметр табличного типа. UDT и XML. Что выбрать?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Шамиль Фаридович
Дык я использую DBCC FREEPROCCACHE, а время все равно уменьшается.

Не смог воспроизвести.
Покажите statistics time, в части "SQL Server parse and compile time".
7 окт 13, 16:06    [14934790]     Ответить | Цитировать Сообщить модератору
 Re: Параметр табличного типа. UDT и XML. Что выбрать?  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 467
set statistics time on отобразит статистику по каждому из 13000 insert'ов.
ClientStatistics показывает, что падает Client processing time(6830 -> 379)

К сообщению приложен файл. Размер - 16Kb
7 окт 13, 16:27    [14934979]     Ответить | Цитировать Сообщить модератору
 Re: Параметр табличного типа. UDT и XML. Что выбрать?  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 467
Гость333, any идеи?
8 окт 13, 15:34    [14940291]     Ответить | Цитировать Сообщить модератору
 Re: Параметр табличного типа. UDT и XML. Что выбрать?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Шамиль Фаридович
Гость333, any идеи?
Какие идеи? Сами же написали:
Шамиль Фаридович
ClientStatistics показывает, что падает Client processing time(6830 -> 379)
Но самое плачевное что вы плохо понимаете что вы меряете.
В первом случае вы меряете создание временной таблицы, заполнение её по одной строке - самое глупое что можно было сделать, ибо это создание 13000 транзакций.
А потом меряете пересылку строки не сервер, парсирование её в строковую константу, перепарсирование в XML (не уверен что есть оптимизации).

В реале фиг его знает, когда ADO создаёт переменные на клиенте и передаёт их.
Поверьте, со строками он не работает (как в SSMS) - единственное что передаётся, так это команда - только имя процедуры и переменные чисто как данные.

Я бы использовал и использую UDT. Ибо нефиг загружать сервер тупым парсингом. И более того вы ещё этот XML создавать должны на клиенте, а это тоже время и ресурсы.
Если уже итак имеются таблички (или енумератор) на клиенте - не мучте *опу.

У UDF есть другие подводные камни. К примеру средний горе-проггер любит наплодить баз на ровном месте и считать всё как единая система. А меж-базового UDT нет.
Или того хуже, плодит 100500 команд когда в 90% случаев всё сводится к одной, чё поделать - гуманитариев большинство, функционально мыслить моск не тянет. (считая что то что он не понимает можно выкинуть из рассмотрения и никак на объективность не влияет)
10 окт 13, 00:29    [14948017]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить