Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Bulk insert с помощью table value parameters в хранимой процедуре на C#.  [new]
padevong
Member

Откуда:
Сообщений: 3
Есть консольное приложение на С# которое читает входную таблицу, парсит данные и заливает их в выходную таблицу.
Заливаются данные с помощью table value parameters, пробовал заливать так же с помощью SqlBulkCopy,
результаты примерно одинаковые, около 1 млн записей читается, парсится и заливается ~ 15 сек.

Но надо оформить все это в виде хранимой процедуры на C#. Создал в Visual Studio хранимку и перенес в нее тот же самый код.
В ней чтение и парсинг входных данных выполняются столько же, т. е. несколько сек.
А вот заливка данных (т.е. в тех местах где вызывается insertCommand.ExecuteNonQuery()) тормозит (до 30-50 мин.) и жрет память.

Так создается табличный тип:
CREATE TYPE [tmp_table] AS TABLE (
	[out_param1] [varchar](400) NULL,
	[out_param2] [varchar](400) NULL
)


Собственно код:
using (some_connection) {
	DataTable inputTable = new DataTable();
	DataTable outputTable = new DataTable();
	outputTable.Columns.Add("out_param1", typeof(string));
	outputTable.Columns.Add("out_param2", typeof(string));

	connection.Open();
	
	SqlCommand selectCommand = connection.CreateCommand();
	selectCommand.CommandText = "SELECT in_param1, in_param2 FROM in_table";

	SqlCommand insertCommand = connection.CreateCommand();
	insertCommand.CommandText = "INSERT INTO out_table (\"out_param1\", \"out_param2\") SELECT \"out_param1\", \"out_param2\" FROM @tmp_table";
	
	SqlParameter parameter = new SqlParameter();
	parameter.SqlDbType = SqlDbType.Structured;
	parameter.ParameterName = "@tmp_table";
	parameter.TypeName = "tmp_table";
	parameter.Value = outputTable;
	insertCommand.Parameters.Add(parameter);
	
	try	{
		SqlDataReader dr = selectCommand.ExecuteReader();
		using (inputTable) {
			inputTable.Load(dr);

			foreach (DataRow row in inputTable.Rows) {
				// parse inputTable row and fill outputTable row
				parse(row);
				// batch size = 5000 rows
				if (outputTable.Rows.Count > 5000) {
					insertCommand.ExecuteNonQuery();
					outputTable.Clear();
				}
			}
			insertCommand.ExecuteNonQuery();
		}
	}
	catch (Exception e)	{
		...
	}
	finally	{
		connection.Close();
	}
}

Вопрос - почему один и тот же код в консольном приложении работает как надо, а в хранимке тормозит?
6 мар 12, 14:35    [12203538]     Ответить | Цитировать Сообщить модератору
 Re: Bulk insert с помощью table value parameters в хранимой процедуре на C#.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Кажись вам нуна и легче и эффективнее написать функцию (на C#) которой подаёшь параметры и получаешь результивные данные.
Нинада никаких коннектов, табличных типов и всякой другой ненцжной шелухи.
А запрос уже в самом скуле написать:
INSERT	dbo.out_table	( [out_param1] ,  [out_param2])
SELECT			 F.[out_param1],F.[out_param2]
FROM	dbo.in_table T
	CROSS APPLY dbo.[fnMyParseCSharpFunction](T.[in_param1], T.[in_param2]) F



PS:
insertCommand.CommandText = "INSERT INTO out_table ([out_param1], [out_param2]) SELECT [out_param1], [out_param2] FROM @tmp_table";
Зачем использовать кавычки (") для колонок? Если надо терминировать так почему не стандартно, через квадратные скобки ([])? А так вы ещё извращетесь в написании запроса, через терминирование кавычек (\").
6 мар 12, 15:00    [12203815]     Ответить | Цитировать Сообщить модератору
 Re: Bulk insert с помощью table value parameters в хранимой процедуре на C#.  [new]
dvim
Member

Откуда: Санкт Петербург
Сообщений: 709
padevong,

Использовал код отсюда
С табличным типом все работает очень быстро и беспроблемно
6 мар 12, 16:18    [12204664]     Ответить | Цитировать Сообщить модератору
 Re: Bulk insert с помощью table value parameters в хранимой процедуре на C#.  [new]
igr_ok
Member

Откуда:
Сообщений: 170
Вы же хотели использовать BULK INSERT, так почему у вас в запросе INSERT? Естественно, INSERT работает медленнее
Если у вас нужно перебрасывать данные между двумя таблицами БД, то создайте DTS-пакет и запускайте его из хранимки
Если источник данных-файл, тогда воспользуйтесь BULK INSERT
6 мар 12, 17:02    [12205198]     Ответить | Цитировать Сообщить модератору
 Re: Bulk insert с помощью table value parameters в хранимой процедуре на C#.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
igr_ok
BULK INSERT
Тоже тупо скопировал код у ТС и профтыкал сам запрос. :)
6 мар 12, 20:52    [12206550]     Ответить | Цитировать Сообщить модератору
 Re: Bulk insert с помощью table value parameters в хранимой процедуре на C#.  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
padevong, делал балк из C# используя, выглядело вида:

            sda = new SqlDataAdapter("SELECT * FROM ... WHERE ID=-1 ORDER BY ID", sc_Conn); // схитрил с заполнением схемы :)))
            sCmd = new SqlCommand("INSERT INTO ... (...) values (@value, @attribute, @data)", sc_Conn);
            sCmd.CommandTimeout = CommandTimeout;
            pc = sCmd.Parameters;
            pc.Add("@attribute", SqlDbType.BigInt, 0, "attribute");
            pc.Add("@data", SqlDbType.BigInt, 0, "data");
            pc.Add("@value", SqlDbType.Float, 0, "value");
            sda.InsertCommand = sCmd;

            sda.InsertCommand = sCmd;
            sda.InsertCommand.UpdatedRowSource = UpdateRowSource.None; // вроде это надобно, проверить MSDN
            sda.UpdateBatchSize = BatchSize; // Это надобно 100%

            sda.Fill(ds, "таблица"); // Вроде жеж читать не нужно, посему такой странный фильтр в создании адапрета, хотя можно просто схему было вычитать)))...

//..... тут всякое разное происходит и заполняется таблица ....

            sda.Update(ds, "таблица"); // полетел типа BULK порциями по BatchSize
// у себя еще прогресс вешал, который типа (вам оно не надобно)
// sda.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdatedEventProgress);
6 мар 12, 21:27    [12206658]     Ответить | Цитировать Сообщить модератору
 Re: Bulk insert с помощью table value parameters в хранимой процедуре на C#.  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
* Написанное выше ессно не традиционный балк))), но лучше нежели есть по умолчанию с отсылкой каждого инсерта отдельно. Про батчсайз можно поглядеть тут: http://msdn.microsoft.com/ru-ru/library/system.data.common.dbdataadapter.updatebatchsize.aspx
6 мар 12, 21:33    [12206673]     Ответить | Цитировать Сообщить модератору
 Re: Bulk insert с помощью table value parameters в хранимой процедуре на C#.  [new]
padevong
Member

Откуда:
Сообщений: 3
dvim,
С табличным типом действительно проблем нет - я написал что с его помощью заливается так же быстро, как и с помощью SqlBulkCopy, время заливки - секунды. Но только если делать это в консольном приложении или запускать, скажем, из ssis пакета. Проблемы начинаются если сделать хранимую процедуру на с# и в ней использовать табличный тип.

igr_ok,
Это не простой INSERT. По большому счету, вот это
"INSERT INTO out_table ([out_param1], [out_param2]) SELECT [out_param1], [out_param2] FROM @tmp_table"

есть текст хранимой процедуры, в которую передаются данные с помощью табличного типа tmp_table. По скорости работы сравнимо с SqlBulkCopy.
И проблема в том, что когда я вызываю эту хранимую процедуру из обычного приложения - все работает как часы, а когда из другой с# хранимой процедуры - тормозит.

kDnZP,
Делал такой вариант, заливалось медленно, возможно что-то делал не так, например не выставил размер BatchSize.

Mnior,
Функция - очень интересный вариант, обязательно попробую. Спасибо тебе, добрый человек!
6 мар 12, 22:12    [12206781]     Ответить | Цитировать Сообщить модератору
 Re: Bulk insert с помощью table value parameters в хранимой процедуре на C#.  [new]
dvim
Member

Откуда: Санкт Петербург
Сообщений: 709
padevong
С табличным типом действительно проблем нет - я написал что с его помощью заливается так же быстро, как и с помощью SqlBulkCopy, время заливки - секунды. Но только если делать это в консольном приложении или запускать, скажем, из ssis пакета. Проблемы начинаются если сделать хранимую процедуру на с# и в ней использовать табличный тип.

Я просто понизил безопасность у ХП - и все заработало.

ХП на c# по возможностям априори не меньше, чем у консольной программы.
(Из минусов конкретно моего решения - коннекция использовалась в контексте другого пользоветеля. Но мне это было не принципиально, не стал тратить время )
7 мар 12, 14:38    [12210342]     Ответить | Цитировать Сообщить модератору
 Re: Bulk insert с помощью table value parameters в хранимой процедуре на C#.  [new]
padevong
Member

Откуда:
Сообщений: 3
В итоге сделал функцией.
Работает быстро, в c# коде нет ничего лишнего.
Mnior, Спасибо!
19 мар 12, 16:11    [12274899]     Ответить | Цитировать Сообщить модератору
 Re: Bulk insert с помощью table value parameters в хранимой процедуре на C#.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
padevong
В итоге сделал функцией.
Работает быстро, в c# коде нет ничего лишнего!
Видно что чел хочет делать правильно.
Бальзам на душу.
Больше бы таких как padevong.
19 мар 12, 21:18    [12277147]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить