Блог

У меня есть много устаревшего кода доступа к данным, в основном SqlCommand с вызовами хранимых процедур, которые мы использовали для выполнения большого количества вставок statment в базу данных. Пока SQL server находился на том же компьютере, что и приложение, производительность была приемлемой, но теперь мы пытаемся переместить часть данных в SQL Azure.

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

 var conn = new SqlConnection("connString")
var cmd = new SqlCommand(conn, "spMyStoreProc");
cmd.Params.Add("@a", SqlDbType.VarChar, 10);
cmd.Params.Add("@b", SqlDbType.Int);

using(conn)
{
 conn.Open();
 foreach(var rec in recordsToInsert)
 {
   cmd.Parameters["@a"].Value = rec.A;
   cmd.Parameters["@b"].Value = rec.B;
   cmd.ExecuteNonQuery();
 }
 conn.Close();
}
  

Я пробовал приведенный выше код с транзакциями и без них.

Я также пытался использовать «пакетную» инструкцию SQL для выполнения нескольких SPS при каждом обращении к серверу. Вот так:

 var cmd = new SqlCommand(conn);
cmd.CommandText = "EXEC spMyStoreProc @a='a' @b=2; EXEC spMyStoreProc @a='b' @b=4;"
  

Это значительно повышает производительность операции, но поскольку у меня довольно много SP, где каждый SP имеет около 20-50 параметров, писать этот код для всех команд insert в этом компоненте доступа к данным становится довольно утомительно.

Это лучший способ достичь этого, или я могу как-то подсказать ADO.NET Я хочу выполнять свои вызовы как пакет (не нашел ничего, что указывало бы на его возможность, но чувствую, что я, по крайней мере, должен спросить), Чтобы избежать задержки в сети и т.д. Между каждым отдельным вызовом SP?

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

Заранее спасибо

Вероятно, у вас должна быть одна хранимая процедура, которая вызывает все остальные хранимые процедуры — вероятно, это будет наименьший объем работы. Итак, из кода вы вызываете хранимые процедуры только один раз… итак, учитывая, что это одни и те же параметры, которые вы передаете каждый раз (потому что ваш код, похоже, подразумевает это), вы в основном сделали бы что-то вроде этого:

 
CREATE PROCEDURE sp_RunBatch(@param1, @param2, etc [all the parameters you need])
AS

exec spMyStoreProc @a='a'
exec spMyStoreProc2 @b='b' 

  

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

Кроме того, если вам не хочется передавать 20/30 параметров каждому SP, вы можете создать тип данных, определенный пользователем в таблице, для каждого набора параметров, которые вы можете передать. Таким образом, каждый SP получает 1 или 2 параметра, и код становится намного проще и читабельнее.

Редактировать:

Это хороший справочник по определяемым пользователем типам таблиц: http://msdn.microsoft.com/en-us/library/bb675163.aspx

И вот как передать табличные типы на SQL server:http://msdn.microsoft.com/en-us/library/bb675163.aspx

Альтернативой подходу M.R. было бы отправить все ваши параметры в виде XML-документа, затем проанализировать XML-документ для извлечения ваших параметров. Это может немного упростить интерфейс.

Однако я думаю, что вы были на чем-то, когда обсуждали возможность объединения всех команд в одну строку. Но вместо того, чтобы создавать их вручную, рассмотрите возможность создания метода расширения объекта SqlCommand, который возвращает единственную строку для выполнения, используя синтаксис sp_executesql, и выполняет всю строку за один проход.

Таким образом, у вас будет цикл, который выглядит следующим образом, и вы вызовете новый метод расширения ToInlineSql:

 string sqlCommand = "";
foreach(var rec in recordsToInsert) 
{   cmd.Parameters["@a"].Value = rec.A;   
    cmd.Parameters["@b"].Value = rec.B;
    sqlCommand  = cmd.ToInlineSql(); 
}
// execute sqlCommand 
  

Метод расширения ToInlineSql может выглядеть следующим образом (peuso-code, вам нужно будет добавить определенные вещи, такие как проверка типа данных и так далее) [и вот ссылка на sp_executesql:

 public static class SqlCmdExt 
{
      public static string ToInlineSql(this SqlCommand cmd)
      {
            string sql = "sp_executesql "   cmd.CommandText  ;
            foreach (SqlParameter p in cmd.Parameters)
            {
                  sql  = ", @"   p.Name   " "   p.DataType.ToString() ;
                  sql  = ", "   p.Name   " = "   p.Value;
            }
            sql  = ";";
            return sql;
      }
}