[ACCEPTED]-C# - What are Some High Performance Best Practices/Tips for ADO.NET-ado.net

One error I see repeated over and over again:

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

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

UPDATED to include sample code asked for in the comments

Disclaimer: This 4 is a quick assembled sample for the sole 3 intent of demonstrating the point about 2 moving repetitive stuff out of the loop. Other 1 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();
    }
}

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

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

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

Some links:

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

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

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

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