0

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?

Currently, 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?

Faisal
  • 4,054
  • 6
  • 34
  • 55
  • Can you tell us a reason for doing that? You might end up in big problems if your query starts to take more than 1 second to execute. – darioo Dec 07 '10 at 10:51

5 Answers5

1

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.

Aaronontheweb
  • 8,224
  • 6
  • 32
  • 61
  • Caching is not a solution here. Its always a new data at every trip. – Faisal Dec 07 '10 at 10:58
  • The only real way to do it is to use a DataReader - http://msdn.microsoft.com/en-us/library/aa720705(v=VS.71).aspx - it's a read-only, forward-only cursor so it's much more performant. Additionally it uses deferred execution so you can keep hitting the same DataReader once a second to pull the latest data out of your database if you wanted. – Aaronontheweb Dec 07 '10 at 17:34
1

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.

Community
  • 1
  • 1
Jon Hanna
  • 110,372
  • 10
  • 146
  • 251
0

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.

Shiraz Bhaiji
  • 64,065
  • 34
  • 143
  • 252
  • Caching is not solution here in my case. Every database query expects a new result set here. – Faisal Dec 07 '10 at 10:59
  • But question is WHY ? may be somebody has better solution to your problem. – TalentTuner Dec 07 '10 at 11:06
  • There are a lots of documents being uploaded on our server and their record is saved in database. We have a service that queries database every second to find the new documents and then processes each document (filtering and renaming etc) according to its meta info. And updates the database record at the end. – Faisal Dec 07 '10 at 11:17
  • Are the users so bloody impatient that they can't wait, for instance, 30 seconds before being able to use the uploaded document? 1 second seems a bit overkill. – jgauffin Dec 07 '10 at 11:23
0

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?
diolemo
  • 2,621
  • 2
  • 21
  • 28
0

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.

Vladimir
  • 1,630
  • 2
  • 18
  • 28