• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Junilu Lacar
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Knute Snortum
  • Tim Cooke
  • Devaka Cooray
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Ganesh Patekar

Does code becomes slow if it has too many database reads operations from database table

 
Ranch Hand
Posts: 1257
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If the code has too many read operations does it become slow because of that?

Thanks  
 
Saloon Keeper
Posts: 10669
228
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It can. But usually poorly written queries are a bigger problem, because they don't access the database in an efficient manner.
 
Rancher
Posts: 4271
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What do you mean by "too many"?

Code that makes 1000 DB calls is going to take more time than code that makes just one of those 1000 calls (well, in most cases).
That's pretty much life.
 
Marshal
Posts: 65814
250
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes.

Please try out some code with too many read operations and see what happens. Isn't there a curve whereby reading very many records is slower than repeatedly reading many but reading very few repeatedly is slower than reading many? Try it with 1 record, 1000 records and 10000000 records and see what happens. Doesn't the DBMS tell you how long a read takes? How slow is too slow?
 
Monica Shiralkar
Ranch Hand
Posts: 1257
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Does not closing connections properly is also a reason that can make the program slow?
 
Stephan van Hulst
Saloon Keeper
Posts: 10669
228
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No.
 
Dave Tolls
Rancher
Posts: 4271
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, until you run out of connections it won't...
 
Monica Shiralkar
Ranch Hand
Posts: 1257
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No closing connections properly can lead to running out of connections. That means that it can become a reason for applications to run slowly.    Although it is different from original question what happens when a code tries to get connection but max number of connections available are already occupied. Does this code never run or it waits and then runs when it gets the connection ?
 
Stephan van Hulst
Saloon Keeper
Posts: 10669
228
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I wouldn't call that slowing down. Your application just stops working properly. Most libraries throw an exception when you run out of connections.
 
Monica Shiralkar
Ranch Hand
Posts: 1257
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So once it send the exception does it retry automatically when connection is available or that code never executes at all?
 
Saloon Keeper
Posts: 5809
146
Android Mac OS X Firefox Browser VI Editor Tomcat Server Safari
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Running out of connections is something you should try to avoid rather than work around. Look into Stephan's suggestion of reducing the number of calls and speeding up the queries, if possible.
 
Monica Shiralkar
Ranch Hand
Posts: 1257
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Example. If a database gives 30 concurrent connections and code runs multiple times for thousands of records the  running out of connections will eventually happen.
 
Campbell Ritchie
Marshal
Posts: 65814
250
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Monica Shiralkar wrote:. . . running out of connections will eventually happen.

It is the responsibility of whoever programmed the database reading to make sure the connections are closed. The database manager will tell users what sort of performance they can expect and guide them about how long they should or shouldn't leave connections open.

As people have told you, running out of connections doesn't slow your code, but makes it run very quickly . . . down an exception's stack trace!
 
Dave Tolls
Rancher
Posts: 4271
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Connection pools generally have a queuing mechanism when requests exceed the available connections.

If, in some situations, connections are not being returned promptly then you will increase the likelihood of this queue being used.

Indeed, a general slowing of the application is one of the signs of the connection pool struggling to provide.
This doesn't have to end with the app completely locking up, as most pools have a reclaim mechanism for connections that have been inactive for a certain length of time.
So it all depends on where in your code, and how many places, you are failing to hand back connections.
Somewhere that is infrequently exercised will have little effect, somewhere more middling will slow the app down, somewhere common will probably cause it to die.

So failing to close connections properly does result in an app slowing down, and may (but not necessarily) result in an app throwing lots of connection exceptions.
 
Monica Shiralkar
Ranch Hand
Posts: 1257
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks. So there is no automatic retry for attempting connection unless we explicitly code for that in the catch block.
 
Dave Tolls
Rancher
Posts: 4271
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The connection pool does the retry of the physical connection, and handles the queuing of the request for a connection if needed.
 
Campbell Ritchie
Marshal
Posts: 65814
250
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I wouldn't code that in a catch myself. Try this sort of thing:-
 
Fire me boy! Cool, soothing, shameless self promotion:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!