Самый быстрый метод для SQL Server вставляет, обновления, выбирает

Лично я большой поклонник кодогенерации. Я накатываю свой собственный доморощенный XML и во время сборки запускаю его через XSLT для генерации моих файлов .CS. Я описываю этот процесс в этом посте Использование XSLT для генерации кода счетчиков производительности . Хотя в ссылке обсуждается создание кода счетчиков производительности, я использую тот же процесс для создания своего DAL.

Итак, я бы создал XML, например:

<query name="LoadCustomerByName" returns="Customer">
  <parameter name="name" type="String"/>
  <text>SELECT ... FROM Customers WHERE name=@name</text>
</query>

, а затем XLST преобразовал бы это во что-то вроде:

public Customer LoadCustomerByName(
  SqlConnection conn,
  SqlTransaction trn,
  String name)
{
  using (Sqlcommand cmd = new SqlCommand(@"SELECT ... FROM ...", conn, trn))
  {
    cmd.Parameters.AddWithValue("@name", name);
    using (SqlDataReader rdr = cmd.ExecuteReader ())
    {
      Customer c = new Customer();
      // Load c from rdr
      return c;
    }
  }
}

Теперь я раскрыл много деталей о том, что на самом деле делает преобразование XSLT, но действительно важно то, что что этот метод дает мне абсолютный контроль над тем, как я создаю свой DAL, и он гибок во всех аспектах, поскольку сгенерированный код .CS полностью управляется моими XSLT. Я могу изменить XLST, и это приведет к повторной генерации каждого метода в моем DAL. Это упрощает изучение различных решений, позволяет мне добавлять в код инструменты (например, счетчики для измерения производительности и частоты использования каждого отдельного запроса) и многое другое.

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

Самый быстрый по времени выполнения или самый быстрый по времени программирования? Единственное, что вы можете сделать для увеличения пропускной способности на # 1, - это использовать несколько потоков и соединений для выполнения вставок - вы можете сделать это с помощью SQLCommand.BeginExecuteNonQuery

В этом ответе основное внимание уделяется операциям "выбор" и "обновление / создание / удаление". Я думаю, что реже обновлять более одной или нескольких записей за раз, и поэтому я также думаю, что «выбор» - это то место, где обычно возникают узкие места. При этом вам необходимо знать свое приложение (профиль). Лучше всего сосредоточить время оптимизации почти всегда на уровне базы данных в самих запросах, а не в клиентском коде. Клиентский код - это всего лишь сантехника: это не главная сила вашего приложения. Однако, поскольку сантехника имеет тенденцию повторно использоваться во многих различных приложениях, я сочувствую желанию сделать ее как можно ближе к оптимальной, и поэтому мне есть что сказать о том, как построить этот код.

У меня есть общий метод для выбора запросов / процедур на моем уровне данных, который выглядит примерно так:

private static IEnumerable<IDataRecord> Retrieve(string sql, Action<SqlParameterCollection> addParameters)
{
    //ConnectionString is a private static property in the data layer
    // You can implement it to read from a config file or elsewhere
    using (var cn = new SqlConnection(ConnectionString))
    using (var cmd = new SqlCommand(sql, cn))
    {
        addParameters(cmd.Parameters);

        cn.Open();
        using (var rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
                yield return rdr;
            rdr.Close();
        }
    }
}

И это позволяет мне писать общедоступные методы уровня данных, которые используют анонимные методы для добавления параметров. Показанный код работает с .Net 2.0+, но его можно написать еще короче, используя .Net 3.5:

public IEnumerable<IDataRecord> GetFooChildrenByParentID(int ParentID)
{
    //I could easily use a stored procedure name instead of a full sql query
    return Retrieve(
        @"SELECT c.* 
         FROM [ParentTable] p 
         INNER JOIN [ChildTable] c ON c.ParentID = f.ID 
         WHERE f.ID= @ParentID", delegate(SqlParameterCollection p)
       {
          p.Add("@ParentID", SqlDbType.Int).Value = ParentID;
       }
     );
}

Я собираюсь остановиться прямо здесь, чтобы снова указать вам на код чуть выше, который использует анонимный метод для создания параметров .

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

Однако я хочу продолжить, чтобы объяснить, как все это сочетается друг с другом. Остальное довольно просто, но также легко выбросить это в список или что-то подобное и сделать что-то не так, что в конечном итоге ухудшит производительность. Итак, двигаясь дальше, бизнес-уровень затем использует фабрику для преобразования результатов запроса в объекты (C # 3.0 или новее):

public class Foo
{
    //various normal properties and methods go here

    public static Foo FooFactory(IDataRecord record)
    {
        return new Foo
        {
            Property1 = record[0],
            Property2 = record[1]
            //...
        };
    }
}

Вместо того, чтобы хранить их в своем классе, вы также можете сгруппировать их все вместе в статический класс, специально предназначенный для придерживаться заводских методов.

Мне нужно внести одно изменение в исходный метод получения. Этот метод «дает» один и тот же объект снова и снова, и это не всегда работает так хорошо. Что мы хотим сделать иначе, чтобы заставить его работать, так это принудительно создать копию объекта, представленного текущей записью, чтобы, когда читатель мутирует для следующей записи, мы работали с чистыми данными. Я подождал, пока не покажу фабричный метод, чтобы мы могли использовать его в окончательном коде. Новый метод Retrieve выглядит следующим образом:

private static IEnumerable<T> Retrieve(Func<IDataRecord, T> factory,
                  string sql, Action<SqlParameterCollection> addParameters)
{
    //ConnectionString is a private static property in the data layer
    // You can implement it to read from a config file or elsewhere
    using (var cn = new SqlConnection(ConnectionString))
    using (var cmd = new SqlCommand(sql, cn))
    {
        addParameters(cmd.Parameters);

        cn.Open();
        using (var rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
                yield return factory(rdr);
            rdr.Close();
        }
    }
}

А теперь мы будем вызывать этот новый метод Retrieve () следующим образом:

public IEnumerable<Foo> GetFooChildrenByParentID(int ParentID)
{
    //I could easily use a stored procedure name instead of a full sql query
    return Retrieve(Foo.FooFactory,
        @"SELECT c.* 
         FROM [ParentTable] p 
         INNER JOIN [ChildTable] c ON c.ParentID = f.ID 
         WHERE f.ID= @ParentID", delegate(SqlParameterCollection p)
       {
          p.Add("@ParentID", SqlDbType.Int).Value = ParentID;
       }
     );
}

Очевидно, этот последний метод можно расширить, чтобы включить любую необходимую дополнительную бизнес-логику. Оказывается, этот код исключительно быстр, потому что он использует преимущества ленивых вычислений IEnumerable. Обратной стороной является то, что он имеет тенденцию создавать множество недолговечных объектов, что может снизить производительность транзакций, о которой вы спрашивали. Чтобы обойти это, я иногда нарушаю хороший n-уровень и передаю объекты IDataRecord непосредственно на уровень представления и избегаю создания ненужных объектов для записей, которые сразу же просто привязаны к элементу управления сеткой.

Код обновления / создания аналогичен, с той разницей, что вы обычно меняете только одну запись за раз, а не несколько.

Или я мог бы избавить вас от чтения этого длинного поста и просто посоветовать вам использовать Entity Framework;)