Fastest method for SQL Server inserts, updates, selects

Question

I use SPs and this isn't an SP vs code-behind "Build your SQL command" question. I'm looking for a high-throughput method for a backend app that handles many small transactions. I use SQLDataReader for most of the returns since forward only works in most cases for me.

I've seen it done many ways, and used most of them myself.

1. Methods that define and accept the stored procedure parameters as parameters themselves and build using cmd.Parameters.Add (with or without specifying the DB value type and/or length)

2. Assembling your SP params and their values into an array or hashtable, then passing to a more abstract method that parses the collection and then runs cmd.Parameters.Add

3. Classes that represent tables, initializing the class upon need, setting the public properties that represent the table fields, and calling methods like Save, Load, etc

I'm sure there are others I've seen but can't think of at the moment as well. I'm open to all suggestions.

Answer 1

This answer focuses mainly on 'select' vs update/create/delete operations. I think it's rarer to update more than one or a few records at a time, and so I also think 'select' is where the bottlenecks tend to occur. That said, you need to know your application (profile). The best place to focus your optimization time is almost always at the database level in the queries themselves, rather than the client code. The client code is all just the plumbing: it's not the main force of your app. However, as plumbing tends to be re-used in many different apps, I do sympathize with the desire to get it as close to optimal as possible, and therefore I do have plenty to say on how to build that code.

I have a generic method for select queries/procedures in my data layer that looks something like this:

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();
        }
    }
}

And that lets me write public data layer methods that use anonymous methods to add the parameters. The code shown works with .Net 2.0+, but can be written even shorter using .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;
       }
     );
}

---

I'm gonna stop right here so I can point you again to the code just above that uses the anonymous method for parameter creation.

This is very clean code, in that it puts the query definition and parameter creation in the same place while still allowing you to abstract away the boilerplate database connection/calling code to somewhere more re-usable. I don't think this technique is covered by any of the bullet points in your question, and it happens to be pretty darn fast as well. I think this about covers the thrust of your question.

---

I want to continue, though, to explain how this all fits together. The rest is fairly straightforward, but it's also easy to throw this to a list or similar and get things wrong, ultimately hurting performance. So moving on, the business layer then uses a factory to translate query results to objects (c# 3.0 or later):

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]
            //...
        };
    }
}

Rather than having these live in their class, you could also group them all together into a static class specifically intended to hold the factory methods.

I need to make one change to the original retrieve method. That method "yields" the same object over and over, and this doesn't always work that well. What we want to do differently to make it work is to force a copy of the object represented by the current record, so that when the reader mutates for the next record we're working with clean data. I waited until after showing the factory method so we can use that in the final code. The new Retrieve method looks like this:

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();
        }
    }
}

And now we would call that new Retrieve() method like this:

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;
       }
     );
}

Obviously this last method can be expanded to include any additional business logic needed. It also turns out this code is exceptionally fast, because it takes advantage of the lazy evaluation features of IEnumerable. The downside is that it tends to create a lot of short-lived objects, and that can hurt the transactional performance you asked about. To get around this I sometimes break good n-tier and pass the IDataRecord objects directly to the presentation tier and avoid unnecessary object creation for records that are simply bound to a grid control right away.

Update/Create code is similar, with the difference that you're usually only changing one record at a time rather than many.

Or, I could save you reading this long post and just tell you to use Entity Framework ;)