Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

This is a discussion myself and some of my colleagues are having and thought I'd come out here and see what if there's a general consensus on it.

It basically comes down to the following 2 opinions on database calls: 1. Make one large call to get everything that may be needed to reduce database the number of DB calls 2. Make smaller separate calls based on what is requested to reduce the size of DB calls

Where this is especially coming into play is in common code. We'll use the example of an Employee class as that's fairly straight forward.

Let's say that your Employee class has 10 value attributes (first name, last name, hiredate, etc.) and then 2 class attributes ... 1 pointing to a Department class and then 1 supervisor that points back to another Employee object.

In mindset #1, you'd make one call that returns the Employee data as well as the fields needed to populate the Department and Supervisor attributes ... or at least the fields that most often used from those sub objects.

In mindset #2, you'd only populate the Employee object at first and then only populate the Department and Supervisor objects if and when they are actually requested.

2's stance is pretty straight-forward ... minimize the size of the requests and how many database objects need to be hit each time one of those requests is made. #1's stance is that even if it could be implemented properly, the sheer fact that the code would have to make multiple connections is going to cause more strain on the connection between the webserver and the database as opposed to reducing it.

The driving force behind researching this is that the amount of traffic between our webserver and database server is getting out of control.

share|improve this question

migrated from stackoverflow.com Sep 26 '11 at 13:34

This question came from our site for professional and enthusiast programmers.

7  
In my experience there isn't a "right answer" to this. There's a balance between latency and throughput. Low latency can tolerate lots of little requests or even one large one; however, high latency links tend to be better off moving a lot of data at once. Nevertheless, if the throughput is low in a high latency configuration, you're better off fetching smaller chunks to be more responsive. –  Nick Sep 26 '11 at 13:15
3  
Probably related to n+1 problem stackoverflow.com/questions/97197/… –  Valera Kolupaev Sep 26 '11 at 13:48
    
@Valera: for convenience here is the link posted on that question: realsolve.co.uk/site/tech/hib-tip-pitfall.php?name=n1selects –  rwong Sep 26 '11 at 14:06
4  
"the amount of traffic between our webserver and database server is getting out of control." What does that mean? Can you be specific on what the real problem is? Do you have performance problems? Have you done profiling and measuring? Please provide the actual results from actual measurements as part of the question. Otherwise, we're just guessing. –  S.Lott Sep 26 '11 at 14:46

8 Answers 8

up vote 5 down vote accepted

If the driving force behind this question is too much traffic, have you looked into caching frequently used objects? For example: AFter you get the Employee and Department and Supervisor objects, maybe it would a good idea to add them a cache so that if they are requested again in the near future, they are already in cache and don't need to be retrieved again. Of course, the cache will need to let rarely used objects expire out, and also needs to be able to remove objects that have been modified by the application and saved back to the database.

Depending on what language and frameworks you're using, there might already be a caching framework that can do some (or most) of what you need. If you use Java, you could look into the Apache Commons-Cache (I haven't used it for a while, and while it looks dormant, it is still available to use and it was pretty decent the last time I used it).

share|improve this answer

Your question seems based on the assumption that you have to guess what data will be needed for any given page. That's not the case. It's not as easy as the naive approach, but you can architect your code so you will know whether you're going to need department or supervisor attributes before making any database calls.

share|improve this answer

Always go for readability and clarity the first time you write something. You can then refactor if and when you need to. Do load testing to find the bottlenecks, in a lot of cases its not the number of calls causing the problem but badly written ones.

As for what classifies as too many, that depends on the application. For most web applications anything under 30 seconds is just about acceptable. I would speak to your users as to their expectations.

share|improve this answer

To me, Too many DB requests is making more requests than you need to load the data you require at any given time.

So I you don't need the data, don't waste memory getting it to avoid a second trip later. But if you need the amount of data, you should minimize the calls to the db.

So have both options, and use each one where the situation calls for it.

EDIT: Keep in mind that this ofcourse depends on your situation as well. If its a WebApp for example you should have different considerations than if its a desktop app accessing the DB within your network, as opposed to across the web for the WepApp.

share|improve this answer
    
What about in the event that you are writing common code and you're not sure in the manner in which your code will be used. Maybe you would never envision someone not needing the Supervisor but it turns out that the application you work on is the only one that needs it. Sure, you could write separate functions ... one to not include it and another to include it but at what point does your common code start requiring too much detailed knowledge in order to use? –  user107775 Sep 26 '11 at 15:13
    
@user107775 I usually write only two functions for each case; one that returns only the property values, and one that return the class with all related classes. This is because MOST times, you only need the properties. This way, you don't need detail knowledge, just the one get the basics and the other everything. I find it to be a reasonable balance. (However some especific cases call for more optimization, but that is on a case by case basis). –  AJC Sep 26 '11 at 15:18

Connect to DB, send out request and have it parsed usually takes significant time compared to retrieving results, so the overall trend is to concatenate as many queries as possible in one request.

Still, doing this all in one shot will make the code unmaintainable. Instead, it is usually attained by an additional abstraction layer: the code schedules several requests as they are needed, then the engine parses this as one big request (possibly using cache on the way) and then replies are dispatched as needed.

Of course not always all can be retrieved in one query - you'll often have a query that provides data necessary for building the next query, so you will have to repeat it. Still staggering bundles of queries and performing as many as possible at once is better than hundreds small shots to the database.

So, plan what you need, request and retrieve it, if more is needed, request and retrieve it again, and then utilize the data in generating content. Definitely avoid using database requests like local variable initialization scattered all over the code.

share|improve this answer

We don't know enought about your application to know which choice are you guilty of optimizing too soon. How often is the Supervisor data utilized? Seems like it could be a waste, but we don't know. If you keep them separate, you may be able to monitor your system to see how often they end up being used together. Than you can make a decision to just combine them in one call. Otherwise, if you start creating a bottle neck with this one big call, where do you begin to trouble-shoot? Difficult to identify what makes sense to omit. More data fields may get added to this process.

It would be interesting to know how much of this is coming from db memory vs disk. There's nothing to make me feel that department is more or less likely to changed compared to address.

share|improve this answer

Both of the strategies here are perfectly valid. There are advantages and disadvantages to each:

One call for all 3 objects:

  • will perform faster
  • will get you exactly what you need in the case where you need it
  • will probably only be useable in one case (it may be a very common case though)
  • will be more difficult to maintain
  • will have to be maintained more often (as it will change if any of the 3 objects' schemas or needed data change)

One call per object (3 calls total)

  • Gives you a general-purpose call to populate a single instance of each object type; they can then be used independently
  • Will be more maintainable as the query structure will be simpler.
  • Will be slower (not necessarily 3 times as slow, but overhead is increased for the same data)
  • Can cause issues with retrieving unneeded data (pulling the entire record when you need one field is wasteful)
  • May cause N+1 issues when a many-to-one relationship exists, if the single-record query is sent out N times, one per record in the collection.
share|improve this answer
    
In response to a couple of your concerns (#3 and 5 in the second list) ... What if Supervisor and Department are only used 1/3 (or less) the time? What if the code was designed to get all children as soon as the List<> object coded to contain them was first referenced? ... would that ease most of the wariness? –  user107775 Sep 26 '11 at 15:11
    
If the ancillary objects are only rarely needed, then in the general case this will perform faster (less data to retrieve) but the worst case will be slower (same data or more retrieved, using three times the communication overhead from your computer). As for the N+1 problem, you simply need to be able to architect the query that retrieves a list of objects to be able to do so based on the foreign key to the "one" side of the relationship, and then pull multiple rows out of the query result. You can't use a version of the query that has to have the record's primary key. –  KeithS Sep 26 '11 at 15:22

These are the rules I use, maybe they'll be of use to you.

  1. Measure first! I won't even look at code that "might be slow" unless I can actually see traffic flowing to that resource and that resource is responding slowly.
  2. 1 Request = K queries. The number of times I talk to the database is fully determined by the kind of resource requested; and never by the nature of the request or state of that resource; In your example, that's probably at most 3 queries: 1 for Employees, 1 for Departments and 1 for Supervisors; It doesn't matter how many of each there happen to be.
  3. Don't query what you won't Use. If this is HTTP we're talking about, theres no sense in querying data for later; there is no later; each request starts from a clean slate. Sometimes I need most of the columns from a table, but on occasion I only need one or two; when I know exactly the fields I need, I'll ask for just that.
  4. Throw hardware at the problem. Servers are cheap; Sometimes you can get enough performance just by moving the database to a beefier box; or sending some queries to a read-only replica.
  5. First invalidate the cache, then implement caching. The urge to put often used or hard to query data in a cache is strong; but all-too-often, evicting unused data or expiring superseded data is overlooked. If you know how to take data out of the cache; then you are safe putting it in the cache; If it turns out its more expensive to invalidate the cache than to just do the query; then you didn't need a cache.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.