C# -SQL: как выполнить пакет StoredProcedure?

Рассматривали ли вы возможность передать XML-документ в хранимую процедуру, а затем выполнить итерацию по нему, чтобы найти данные для вставки?

Лично я, если я регулярно ожидаю делать довольно большие вставки (10 000 строк определенно подходят ...), я мог бы подумать о создании отдельной таблицы для входящих данных и использовать SqlBulkCopy для заполнения этой таблицы. Затем вы просто выполняете одну хранимую процедуру, которая перемещает данные в реальную таблицу.

Другой подход - отправить xml в базу данных и использовать sqlxml для его анализа (намного проще с SQL2005 и выше), но это требует дополнительной работы на сервере db.

"это не лучшее решение: я потеряю кучу времени в сетевом транспорте" Вы можете жить с потерей?

Если это то, что вы делаете нечасто, какое это имеет значение? Сначала измерьте это, если это проблема, то исправьте ее, лично я, вероятно, выбрал бы таблицу Marc Gravells для входящих вставок. Другой вариант - запускать вставки асинхронно, тогда вам не нужно ждать завершения каждой, прежде чем начинать следующую.

На это у меня ушли годы, но, наконец, я понял, что не стоит тратить время на оптимизацию кода, который в этом не нуждается.

Надеюсь, это поможет (хотя я не думаю, что это поможет, извините).

Это должно работать немного быстрее:

public void InsertUser(IEnumerable<string> userCodes)
{
   using (SqlConnection sqlConnection = new SqlConnection(this.connectionString), 
             SqlCommand sqlCommand = new SqlCommand("InsertUser", sqlConnection))
   {
      sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
      SqlParameter param = sqlCommand.Parameters.Add("@UserCode", SqlDbTypes.VarChar);
      sqlConnection.Open();

      foreach(string code in userCodes)
      {
          param.Value = code;
          sqlCommand.ExecuteNonQuery();///0.2 seconds
      }
   }
}

Это откроет только одно соединение и создаст только одну команду, даже если вы передадите ей 1000 пользователей. Тем не менее, он по-прежнему будет делать каждую вставку отдельно. И, конечно, если userCode не является строкой, вы захотите соответствующим образом перефакторить ее. Вы также можете изучить команду SQL Server ОБЪЕМНАЯ ВСТАВКА.

Если бы вы действительно были обеспокоены этим, вы могли бы (как вы сказали) объединить команды в следующие строки:

var cmd = new SqlCommand();
cmd.Connection = sqlConnection;
for (int i = 0; i < batchSize; i++) {
    cmd.CommandText += String.Format("EXEC InsertUser @UserCode{0};", i);
    cmd.Parameters.AddWithValue("@UserCode" + i.ToString(), XXXXX);
    //... etc ...
}

Поскольку в этой схеме вы будете использовать параметр, у вас не будет большего риска SQL-инъекции, чем если бы вы использовали сохраненную процедуру. Но я сомневаюсь, что вы действительно сэкономите на этом заметное количество времени. ИМО, вы должны просто упростить и сделать так, как вы это делаете сейчас.

Основываясь на ответе Джоэла, это самое быстрое решение, за исключением использования SqlBulkCopy или создания больших строк беспорядочного SQL и выполнения. (Я добавил транзакцию, которая значительно улучшит производительность)

public void InsertUser(IEnumerabler<string> userCodes)
{
   using (SqlConnection sqlConnection = new SqlConnection(this.connectionString))
   {
      sqlConnection.Open();
      SqlTransaction transaction = connection.BeginTransaction();
      SqlCommand sqlCommand = new SqlCommand("InsertUser", sqlConnection);
      sqlCommand.Transaction = transaction;
      sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
      SqlParameter param = sqlCommand.Parameters.Add("@UserCode", SqlDbTypes.VarChar);

      foreach(string code in userCodes)
      {
          param.Value = code;
          sqlCommand.ExecuteNonQuery();
      }      
      transaction.Commit();
   }
}

Согласно некоторым из приведенных выше ответов, наиболее заметное увеличение производительности при минимальных усилиях включает 2 изменения в существующем коде:

1. Обертывание обновлений в транзакции
2. Открытие только одного соединения и многократный вызов процедуры с разными параметрами.

BULK INSERT - вариант, но, вероятно, излишний для того, что вы хотите сделать.

А как насчет UpdateBatchSize для SQLDataAdaptor?

Наши специалисты по интерфейсу используют это, чтобы разбить несколько 10000 вызовов процессов на куски.

Статья

MSDN

Наша среда запрещает права "bulkadmin", поэтому мы не можем использовать BULKINSERT / bcp и т. д.

Думаю, это довольно старый вопрос.

В SQL Server 2008 теперь можно использовать параметр значения таблицы. Короче говоря, передайте все свои переменные в используемом определенном типе (таблице).

В SQL теперь вы можете обрабатывать все записи как отдельные элементы ... Фактически, используйте логику набора и получите реальную производительность.