I am continuously querying database; should there be any problem?

In my application, I am querying database with same sql every one second. I need to know what measure should I take. Will it ever cause any problem like " The timeout period elapsed prior to obtaining a connection from the pool." or any other like that?

Currentl开发者_开发技巧y, i am creating a new connection every second and the disposing it after it is used. Should I reuse connection in this case.

Whats the best approach?

IMHO best practice is to pump-and-dump connections as quickly as possible - use them to get access to what you need and close them right away. Your enemy performance-wise isn't the overhead it takes to create a connection (although there is some involved there) - it's locking a table in your database.

If you're looking to optimize your application, you should try to implement some sort of caching mechanism that saves you from having to make a round-trip to the database for each lookup. That would be to your benefit performance-wise.

Another thing you can do is use read-only connections where you can - they require less overhead than traditional ones and will improve your performance also.

You should definitely open and close the connection each time. Indeed, if your using block has much code after the last use of the connection, call Close() to get it back in the pool as soon as possible. That way the chance of another use not needing to open a completely new connection is reduced (see What does "opening a connection" actually mean? for a bit more on when a real connection is opened and when one is taken from the pool).

Is this "once a second" an average across different threads, or all on the one thread? If it's all on the one thread it doesn't matter, indeed it might even be slightly faster to keep the connection object open, because either way there won't be contention for it.

I would certainly consider caching results, though this has downsides in memory use, along with potentially complicated issues about when the cached results need to be refreshed - really this could be anywhere from trivial to impossible depending on just what you are doing.

It's also clearly a query to go that extra mile when optimising.

Why do you need to do this?

You could try caching the data to reduce the load on your database. Do you need data that is 1 second old, or is 5 seconds ok.

Closing the connection after each time you use it is OK. It does not really get closed, it just goes back into the connection pool.

If the library you're using does indeed pool the connections for you then it doesn't make a difference. If not, then it would be better to use the same connection multiple times. Creating a connection is time consuming.

A few questions...

• What data are you getting from the database?
• Could that data be stored in application memory?

There is no problem in such approach if you dispose connections like this:

using (SqlConnection cnn = new SqlConnection(ConnectionString))
{
            SqlCommand cmd = new SqlCommand(commandText, cnn)
            {
                CommandType = CommandType.Text
            };

            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);

            return ds;
}

The only problem may happen - is decreasing your db performance if your hardware isn't good enought.