C# - What are Some High Performance Best Practices/Tips for ADO.NET

Question

I decided not to use an orm and going to use straight ADO.NET for my project. I know I know its gonna take longer to program but I just want pages to load at high speeds even at peak time.

Answer 1

One error I see repeated over and over again:

Instantiating and setting up everything (DbConnection, DbCommand, DbParameters) inside a method which is called repeatedly in a tight loop.

Most of the time you can refactor those local variables as class members instantiating them only once and keeping only the updates to DbParameters inside the method.

---

UPDATED to include sample code asked for in the comments

Disclaimer: This is a quick assembled sample for the sole intent of demonstrating the point about moving repetitive stuff out of the loop. Other better practices aren't necessarily implemented.

        public static void Show() {
            List people = new List();

            //Everything is done inside the loop
            PersonDal personDal = new PersonDal();
            foreach (Person person in people) {
                personDal.Insert(person);
            }

            //Things are setup once outside the loop.
            using (DbConnection connection = SqlClientFactory.Instance.CreateConnection()) {
                // setup the connection
                BetterPersonDal betterPersonDal = new BetterPersonDal(connection);
                connection.Open();
                foreach (Person person in people) {
                    betterPersonDal.Insert(person);
                }
            }
        }
    }

    class Person {
        public int Id { get; set; }
        public string Name { get; set; }
    }

On this first implementation, every thing is set up every time the method is called:

class PersonDal {
    public int Insert(Person person) {
        DbProviderFactory factory = SqlClientFactory.Instance;

        using (DbConnection connection = factory.CreateConnection()) {
            connection.Open();

            connection.ConnectionString = "Whatever";

            using (DbCommand command = connection.CreateCommand()) {
                command.CommandText = "Whatever";
                command.CommandType = CommandType.StoredProcedure;

                DbParameter id = command.CreateParameter();
                id.ParameterName = "@Id";
                id.DbType = DbType.Int32;
                id.Value = person.Id;

                DbParameter name = command.CreateParameter();
                name.ParameterName = "@Name";
                name.DbType = DbType.String;
                name.Size = 50;
                name.Value = person.Name;

                command.Parameters.AddRange(new DbParameter[] { id, name });

                return (int)command.ExecuteScalar();
            }
        }
    }
}

Now we move the setup to the objects construction leaving it out of the loop:

class BetterPersonDal {
    private DbProviderFactory factory;
    private DbConnection connection;
    private DbCommand command;
    private DbParameter id;
    private DbParameter name;

    public BetterPersonDal(DbConnection connection) {
        this.command = connection.CreateCommand();
        this.command.CommandText = "Whatever";
        this.command.CommandType = CommandType.StoredProcedure;

        this.id = command.CreateParameter();
        this.id.ParameterName = "@Id";
        this.id.DbType = DbType.Int32;

        this.name = command.CreateParameter();
        this.name.ParameterName = "@Name";
        this.name.DbType = DbType.String;
        this.name.Size = 50;

        this.command.Parameters.AddRange(new DbParameter[] { id, name });
    }

    public int Insert(Person person) {
        this.id.Value = person.Id;
        this.name.Value = person.Name;

        return (int)command.ExecuteScalar();
    }
}

Answer 2

Have a look at the Improving .NET Application Performance and Scalability book (available online for free, in MSDN). There's a whole chapter about improving ADO.NET performance.

Answer 3

Be very smart about connection management. Opening a DB connection can be very expensive so try and keep that in mind when writing the database access layer.

Answer 4

If you're not going to use an ORM, are you also not going to cache your data? That's a big advantage of using an ORM. If there's no data cache, you'll need to look at ways to cache the HTML/JavaScript. This can be accomplished using OutputCache directive and SqlDependency. Or by publishing out static HTML and JavaScript files. Either way, you will be able to handle higher load if you're not constantly hitting the database on every request.

Some links:

ASP.NET Web Site Performance Improvement http://www.codeproject.com/KB/aspnet/aspnetPerformance.aspx

10 ASP.NET Performance and Scalability Secrets http://www.codeproject.com/KB/aspnet/10ASPNetPerformance.aspx

Omar AL Zabir blog on ASP.NET Ajax and .NET 3.5 http://msmvps.com/blogs/omar/archive/tags/performance/default.aspx

Answer 5

The article Martin links to is excellent. I would just add that you definitely want to use a DataReader instead of a DataSet (I love DataSets, but not for performance reasons).