Массовая вставка в сервер SQL

Я пытаюсь вставить массу записей в SQL Server 2005 из Vb.Net. Хотя вставка работает нормально, я делаю все возможное, чтобы сделать это как можно быстрее. В настоящее время для 100 000 записей требуется ~ 11 минут. Каков будет предложенный подход для вставки большого количества записей в SQL Server из приложения?

Мой нынешний apporach в основном открывает соединение, перебирает мой список информации и запускает отдельные SQL-вставки, а затем закрывает соединение. У кого-нибудь есть лучшее предложение о том, как это сделать?

Текущая функция:

Public Sub BatchInsert(ByVal ParamCollections As List(Of SqlParameter()))

    Dim Conn As SqlConnection = New SqlConnection(DBHelper.DatabaseConnection)
    Using scope As TransactionScope = New TransactionScope()
    Using Conn

        Dim cmd As SqlCommand = New SqlCommand("sproc_name", Conn)

        Conn.Open()
        cmd.CommandType = CommandType.StoredProcedure

        For i = 0 To ParamCollections.Count - 1

            cmd.Parameters.Clear()
            cmd.Parameters.AddRange(ParamCollections(i))
            cmd.ExecuteNonQuery()

        Next

        Conn.Close()
        scope.Complete()
    End Using
    End Using

End Sub
Источник

6 ответов

Решение

Используйте класс SqlBulkCopy, он сможет проходить через эти 100K строк гораздо быстрее, чем отдельные вставки.

О, и если вы можете, я бы настоятельно рекомендовал вам реализовать класс, поддерживающий IDataReader, для подачи в метод SqlBulkCopy.WriteToServer(IDataReader), это позволит вам генерировать данные последовательно, по одной строке за раз. Если вы импортируете из текстового файла, например, построение некоторых IEnumerable<T> методы, которые используют yield return и преобразование его в объект IDataReader позволит вам очень естественно передавать данные на сервер.

Чтобы противостоять потере возможности отката с помощью BCP, вы можете перенести данные во временную таблицу, а затем выполнить обычный INSERT INTO операторы на сервере впоследствии, массово перенося данные из временной таблицы в рабочую таблицу, это позволит вам использовать транзакцию для последней части переноса и будет по-прежнему выполняться намного быстрее, чем исходные отдельные операторы вставки.

РЕДАКТИРОВАТЬ: и вот пример (C#, но должно быть легко преобразовать в VB.Net) использования API массовой загрузки.

Источник

Благодаря всеобщей помощи, я смог выполнить свою задачу. SQLBulkCopy идеально подходит для моих нужд (хотя были и другие отличные предложения). Используя SqlBulkcopy, время возросло с 11 минут до 45 секунд. Я не могу поверить в разницу!

Для дальнейшего использования вот несколько битов информации:

  • Чтобы использовать SQL Bulk Copy, ваши данные должны быть в форме DataSet, DataReader или DataTable. Некоторый XML также разрешен.

Основной код реализации:

    Public Sub PerformBulkCopy(ByVal dt As DataTable)

    Using Conn As SqlConnection = New SqlConnection(DBHelper.DatabaseConnection)
        Conn.Open()

        Using s As SqlBulkCopy = New SqlBulkCopy(Conn)

            s.DestinationTableName = "TableName"
            s.WriteToServer(dt)
            s.Close()

        End Using

        Conn.Close()
    End Using
End Sub

Очень информативная ссылка, которую я нашел:

Использование Sql Bulk Copy

Спасибо всем за помощь! Я искренне ценю это.

Источник

Поместите свои данные для импорта в CSV-файл и запустите утилиту bcp для данных. Вы не можете работать быстрее с последовательными вызовами, вставляющими по одной строке за раз, вам, безусловно, нужна массовая утилита, если вы хотите повысить производительность.

Класс SQLBulkCopy позволит вам передавать все данные в коллекции, чтобы сервер мог обрабатывать все сразу, исключая взад и вперед. Так что если вы хотите избежать создания временных файлов (что я и сделал бы), то посмотрите на этот класс.

Просто оставить соединение открытым - это хорошее начало, но у вас все еще есть накладные расходы, связанные с отправкой строки, сохранением ее в SQL, возвратом результата, а затем вы должны перейти к следующей строке.

Источник

Вот несколько сравнений скорости различных методов импорта текстовых файлов на сервер sql: http://weblogs.sqlteam.com/mladenp/archive/2006/07/22/10742.aspx

Надеюсь, поможет.

Источник

Есть также сохраненный процесс (называемый Bulk Insert), который поможет вам. Он использует bcp под обложками.

проверьте эту ссылку, чтобы увидеть синтаксис

текст ссылки

Источник

Это зависит от того, как реализован класс массового копирования. Но есть инструмент командной строки, включенный в установки SQL Server, который делает именно это (вероятно, то же самое). Это называется "BCP". Я использую его прямо сейчас, и он должен быть в состоянии прорвать 100 000 строк в считанные секунды.

В документации MSDN эта утилита называется "массовый импорт".

Источник
Другие вопросы по тегам