Как правильно и эффективно повторно использовать подготовленный оператор в С#.NET(SQL Server)?

Я просмотрел множество вопросов, но, очевидно, мой SO-fu не справился с задачей, поэтому я здесь. Я пытаюсь эффективно использовать подготовленные операторы, и я имею в виду не только параметризацию одного оператора, но и компиляцию для многократного использования. Мой вопрос лежит вокруг параметров и повторного использования и того, как правильно их реализовать.

Как правило, я следую этой процедуре (надуманный пример):

SqlConnection db = new SqlConnection(...);
SqlCommand s = new SqlCommand("select * from foo where [email protected]", db);
s.Parameters.Add("@a", SqlDbType.VarChar, 8);
s.Prepare();
...
s.Parameters["@a"] = "bozo";
s.Execute();

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

Я попробовал s.Parameters.Clear(), но это фактически удаляет сами параметры, а не только значения, поэтому мне по существу нужно снова вернуться к параметрам и повторно Prepare, которые, казалось бы, также. Нет благодарности.

В этот момент я остаюсь с итерацией через s.Parameters и установкой их всех в значение null или какое-либо другое значение. Правильно ли это? К сожалению, в моем текущем проекте у меня есть запросы с ~ 15 параметрами, которые нужно выполнить ~ 10000 раз за запуск. Я могу запустить эту итерацию в метод, но задавался вопросом, есть ли лучший способ сделать это (без сохраненных процедур).

Мое текущее обходное решение - это метод расширения SqlParameterCollection.Nullify, который устанавливает все параметры равными нулю, что отлично подходит для моего случая. Я просто запускаю это после выполнения.

Я нашел несколько практически идентичных, но (IMHO) вопросов без ответа:

Подготовленные операторы и встроенный пул соединений в .NET

Объединение пула соединений SQLite/С# и готовность к запуску отчета (Серж был так близко к ответу!)

Лучший ответ, который я мог найти, - это (1) здравый смысл выше и (2) эта страница:

http://msdn.microsoft.com/en-us/magazine/cc163799.aspx

Ответ 1

При повторном использовании подготовленного SqlCommand, безусловно, все, что вам нужно сделать, - установить значения параметров для новых? Вам не нужно очищать их после использования.

Для себя я не видел СУБД, выпущенную за последние 10 лет, которая получила какую-либо заметную выгоду от подготовки заявления (я полагаю, если бы сервер БД находился в пределах своего процессора, он мог бы, но это не типично). Вы уверены, что подготовка необходима?

Запуск той же команды "~ 10000 раз за ход" немного пахнет мне, если вы не загружаетесь из внешнего источника. В этом случае может потребоваться массовая загрузка? Что каждый прогон делает?

Приветствия -

Ответ 2

Чтобы добавить к ответу Саймона, до Sql 2005 Command.Prepare(), было бы улучшено кэширование плана запроса для специальных запросов (обычно были скомпилированы SPROC). Тем не менее, в более поздних версиях Sql при условии, что ваш запрос параметризован, также могут быть кэшированы временные запросы, которые также параметризуются, что уменьшает требуется для Prepare().

Вот пример сохранения коллекции SqlParameters, изменяющей только значение значений этих параметров, которые меняются, чтобы предотвратить повторное создание параметров (т.е. сохранение и создание объектов-параметров):

using (var sqlConnection = new SqlConnection("connstring"))
 {
    sqlConnection.Open();
    using (var sqlCommand = new SqlCommand
       {
          Connection = sqlConnection,
          CommandText = "dbo.MyProc",
          CommandType = CommandType.StoredProcedure,
       })
    {
       // Once-off setup per connection
       // This parameter doesn't vary so is set just once
       sqlCommand.Parameters.Add("ConstantParam0", SqlDbType.Int).Value = 1234;
       // These parameters are defined once but set multiple times
       sqlCommand.Parameters.Add(new SqlParameter("VarParam1", SqlDbType.VarChar));
       sqlCommand.Parameters.Add(new SqlParameter("VarParam2", SqlDbType.DateTime));

       // Tight loop - performance critical
       foreach(var item in itemsToExec)
       {
         // No need to set ConstantParam0
         // Reuses variable parameters, by just mutating values
         sqlParameters["VarParam1"].Value = item.Param1Value; // Or sqlParameters[1].Value
         sqlParameters["VarParam2"].Value = item.Param2Date; // Or sqlParameters[2].Value
         sqlCommand.ExecuteNonQuery();
       }
    }
}

Примечания:

Если вы вставляете большое количество строк и concurrency с другими обитателями базы данных важно, и если граница транзакции ACID не важна, вы можете рассмотреть возможность пакетной и фиксации обновлений таким образом, чтобы было заблокировано менее 5000 строк удерживаются на столе за раз, чтобы предотвратить эскалацию блокировки стола.
В зависимости от того, какую работу выполняет ваш proc, может быть возможность распараллеливать цикл, например. с TPL. Очевидно, что соединение и команды не являются потокобезопасными. Каждая задача потребует своего собственного соединения и команды повторного использования - локальная перегрузка Parallel.ForEach идеально подходит для этого.