CODEWITHSUNDEEP
c#sql-serverasp.net-mvcazureado.net
Vinicius Gonçalves
Vinicius Gonçalves

Reputation: 2724

Slow - Open and close connection to SQL Server

I have an ASP.NET MVC application running on Windows Azure with SQL Azure. The application uses SqlClient to connect to the database. I'm always using the same connectionString (To use connection pool):

        using (SqlConnection conn = new SqlConnection("Static Connection String"))
        {
            using (var command = conn.CreateCommand())
            {                    
                conn.Open();
                return command.ExecuteNonQuery();
            }
        }

I noticed that there is a considerable time between the opening of the connections that leaving the slow implementation. For example, if I have a page with four selectlist, the application needs to open the connection four times to fill the lists. If you do this with one command, returning all lists, the performance is incredible, but when I open and close the connection to get the result lists separately the performance falls too.

With a windows forms application that does not happen.

My doubt is, is there any limitation to the environment in which I am running?

Upvotes: 0

Views: 1995

Answers (1)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131237

The problem is that you execute 4 queries, not that you open 4 connections - you don't. Connection pooling means that you reuse the same connection.

You still have to send a request to the server though, wait and retrieve the answer. It's the 4 roundtrips that kill performance. 4 queries will be up to 4 times slower than a single query, no matter what. If the data retrieved is small, the roundtrip overhead is way more expensive than the query itself.

You should try to reduce database calls or eliminate them altogether:

  • Use batching to combine multiple queries into a single call.
  • Use caching for lookup data so that you don't have to retrieve the same selections all the time.
  • Finally, use output caching to avoid rendering the same page if the request parameters don't change.

Upvotes: 1

Related Questions