Ответ 1
Пожалуйста, не создавайте DataTable для загрузки через BulkCopy. Это хорошее решение для небольших наборов данных, но нет абсолютно никакой причины загружать все 10 миллионов строк в память перед вызовом базы данных.
Ваш лучший выбор (за пределами BCP/BULK INSERT/OPENROWSET(BULK...)) - передать содержимое из файла в базу данных через Table-Valued Parameter (TVP). Используя TVP, вы можете открыть файл, прочитать строку и отправить строку до завершения, а затем закрыть файл. Этот метод имеет объем памяти всего одной строки. Я написал статью Поток данных в SQL Server 2008 из приложения, в котором есть пример этого сценария.
Простейший обзор структуры выглядит следующим образом. Я принимаю ту же таблицу импорта и имя поля, что и в предыдущем вопросе.
Необходимые объекты базы данных:
-- First: You need a User-Defined Table Type
CREATE TYPE ImportStructure AS TABLE (Field VARCHAR(MAX));
GO
-- Second: Use the UDTT as an input param to an import proc.
-- Hence "Tabled-Valued Parameter" (TVP)
CREATE PROCEDURE dbo.ImportData (
@ImportTable dbo.ImportStructure READONLY
)
AS
SET NOCOUNT ON;
-- maybe clear out the table first?
TRUNCATE TABLE dbo.DATAs;
INSERT INTO dbo.DATAs (DatasField)
SELECT Field
FROM @ImportTable;
GO
Код приложения С# для использования вышеуказанных объектов SQL приведен ниже. Обратите внимание, что вместо заполнения объекта (например, DataTable), а затем выполнения хранимой процедуры в этом методе выполняется выполнение хранимой процедуры, которая инициирует чтение содержимого файла. Входной параметр Stored Proc не является переменной; это возвращаемое значение метода, GetFileContents. Этот метод вызывается, когда SqlCommand вызывает ExecuteNonQuery, который открывает файл, читает строку и отправляет строку SQL Server с помощью конструкций IEnumerable<SqlDataRecord> и yield return, а затем закрывает файл. Хранимая процедура просто видит переменную таблицы @ImportTable, доступ к которой возможен, как только данные начнут поступать (обратите внимание: данные сохраняются в течение короткого времени, даже если не полное содержимое, в tempdb).
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Server;
private static IEnumerable<SqlDataRecord> GetFileContents()
{
SqlMetaData[] _TvpSchema = new SqlMetaData[] {
new SqlMetaData("Field", SqlDbType.VarChar, SqlMetaData.Max)
};
SqlDataRecord _DataRecord = new SqlDataRecord(_TvpSchema);
StreamReader _FileReader = null;
try
{
_FileReader = new StreamReader("{filePath}");
// read a row, send a row
while (!_FileReader.EndOfStream)
{
// You shouldn't need to call "_DataRecord = new SqlDataRecord" as
// SQL Server already received the row when "yield return" was called.
// Unlike BCP and BULK INSERT, you have the option here to create a string
// call ReadLine() into the string, do manipulation(s) / validation(s) on
// the string, then pass that string into SetString() or discard if invalid.
_DataRecord.SetString(0, _FileReader.ReadLine());
yield return _DataRecord;
}
}
finally
{
_FileReader.Close();
}
}
Используемый выше метод GetFileContents используется как значение входного параметра для хранимой процедуры, как показано ниже:
public static void test()
{
SqlConnection _Connection = new SqlConnection("{connection string}");
SqlCommand _Command = new SqlCommand("ImportData", _Connection);
_Command.CommandType = CommandType.StoredProcedure;
SqlParameter _TVParam = new SqlParameter();
_TVParam.ParameterName = "@ImportTable";
_TVParam.TypeName = "dbo.ImportStructure";
_TVParam.SqlDbType = SqlDbType.Structured;
_TVParam.Value = GetFileContents(); // return value of the method is streamed data
_Command.Parameters.Add(_TVParam);
try
{
_Connection.Open();
_Command.ExecuteNonQuery();
}
finally
{
_Connection.Close();
}
return;
}
Дополнительные примечания:
- С некоторой модификацией приведенный выше код С# может быть адаптирован для пакетной обработки данных.
- При незначительной модификации вышеуказанный код С# может быть адаптирован для отправки в нескольких полях (пример, указанный в статье "Пропаривание данных...", приведенной выше, проходит в 2 полях).
- Вы также можете манипулировать значением каждой записи в инструкции
SELECTв proc. - Вы также можете отфильтровать строки, используя условие WHERE в proc.
- Вы можете получить доступ к переменной TVP Table Variable несколько раз; это READONLY, но не "только вперед".
- Преимущества над
SqlBulkCopy:-
SqlBulkCopyявляется INSERT-only, тогда как использование TVP позволяет использовать данные любым способом: вы можете вызватьMERGE; вы можетеDELETEна основании некоторого условия; вы можете разделить данные на несколько таблиц; и т.д. - Из-за того, что TVP не является INSERT-only, вам не нужна отдельная промежуточная таблица для сброса данных.
- Вы можете вернуть данные из базы данных, вызвав
ExecuteReaderвместоExecuteNonQuery. Например, если в таблице импортаDATAsбыло полеIDENTITY, вы могли бы добавить предложениеOUTPUTвINSERTдля возврата назадINSERTED.[ID](предполагается, чтоID- это имяIDENTITY> ). Или вы можете передать результаты совершенно другого запроса или оба, поскольку множество наборов результатов можно отправлять и получать черезReader.NextResult(). Получение информации из базы данных невозможно при использованииSqlBulkCopy, но здесь есть несколько вопросов по S.O. людей, которые хотят сделать именно это (по крайней мере, в отношении вновь созданных значенийIDENTITY). - Для получения дополнительной информации о том, почему это происходит быстрее для общего процесса, даже если он немного медленнее при получении данных с диска в SQL Server, см. этот технический документ от Консультативной группы клиентов SQL Server: Максимизация пропускной способности с помощью TVP
-