What qualifies "too many database requests" in code?

Question

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.

Answer 1

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).

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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.

Answer 7

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.

Answer 8

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.