15

At one of my employers, we worked on a REST (but it also applies to SOAP) API. The client, which is the application UI, would make calls over the web (LAN in typical production deployments) to the API. The API would make calls to the database.

One theme that recurs in our discussions is performance: some people on the team believe that you should not have multiple database calls (usually reads) from a single API call because of performance; you should optimize them so that each API call has only (exactly) one database call.

But is that really important? Consider that the UI has to make a network call to the API; that's pretty big (order of magnitude of milliseconds). Databases are optimized to keep things in memory and execute reads very, very quickly (eg. SQL Server loads and keeps everything in RAM and consumes almost all your free RAM if it can).

TLDR: Is it really significant to worry about multiple database calls when we are already making a network call over the LAN? If so, why?

To be clear, I'm talking about order of magnitude -- I know that it depends on specifics (machine hardware, choice of API and DB, etc.) If I have a call that takes O(milliseconds), does optimizing for DB calls that take an order of magnitude less, actually matter? Or is there more to the problem than this?

Edit: for posterity, I think it's quite ridiculous to make claims that we need to improve performance by combining database calls under these circumstances -- especially with a lack of profiling. However, it's not my decision whether we do this or not; I want to know what the rationale is behind thinking this is a correct way of optimizing web API calls.

  • Is there not another network call between the API layer and the database? – Sign Sep 3 '14 at 15:59
  • 3
    What did your timing tests show? – Dan Pichelman Sep 3 '14 at 16:15
  • @Sign There is no network call between the API and the DB. They're guaranteed to be on the same machine, from what I understand. – ashes999 Sep 3 '14 at 16:22
  • @DanPichelman that's what I'm asking too. Nobody seems to be taking and timing performance; we just get requirements to "fix the performance in X by combining all DB calls into a single call." – ashes999 Sep 3 '14 at 16:23
24

But is that really important? Consider that the UI has to make a network call to the API; that's pretty big (order of magnitude of milliseconds). Databases are optimized to keep things in memory and execute reads very, very quickly (eg. SQL Server loads and keeps everything in RAM and consumes almost all your free RAM if it can).

The Logic

In theory, you are correct. However, there are a few flaws with this rationale:

  1. From what you stated, it's unclear if you actually tested / profiled your app. In other words, do you actually know that the network transfers from the app to the API are the slowest component? Because that is intuitive, it is easy to assume that it is. However, when discussing performance, you should never assume. At my employer, I am the performance lead. When I first joined, people kept talking about CDN's, replication, etc. based on intuition about what the bottlenecks must be. Turns out, our biggest performance problems were poorly performing database queries.

  2. You are saying that because databases are good at retrieving data, that the database is necessarily running at peak performance, is being used optimally, and there is nothing that can be done to improve it. In other words, databases are designed to be fast, so I should never have to worry about it. Another dangerous line of thinking. That's like saying a car is meant to move quickly, so I don't need to change the oil.

  3. This way of thinking assumes a single process at a time, or put another way, no concurrency. It assumes that one request cannot influence another request's performance. Resources are shared, such as disk I/O, network bandwidth, connection pools, memory, CPU cycles, etc. Therefore, reducing one database call's use of a shared resource can prevent it from causing other requests to slow down. When I first joined my current employer, management believed that tuning a 3 second database query was a waste of time. 3 seconds is so little, why waste time on it? Wouldn't we be better off with a CDN or compression or something else? But if I can make a 3 second query run in 1 second, say by adding an index, that is 2/3 less blocking, 2/3 less time spent occupying a thread, and more importantly, less data read from disk, which means less data flushed out of the in-RAM cache.

The Theory

There is a common conception that software performance is simply about speed.

From a purely speed perspective, you are right. A system is only as fast as its slowest component. If you have profiled your code and found that the Internet is the slowest component, then everything else is obviously not the slowest part.

However, given the above, I hope you can see how resource contention, lack of indexing, poorly written code, etc. can create surprising differences in performance.

The Assumptions

One last thing. You mentioned that a database call should be cheap compared to a network call from the app to the API. But you also mentioned that the app and the API servers are in the same LAN. Therefore, aren't both of them comparable as network calls? In other words, why are you assuming that the API transfer is orders of magnitude slower than the database transfer when they both have the same available bandwidth? Of course the protocols and data structures are different, I get that, but I dispute the assumption that they are orders of magnitude different.

Where it gets murkey

This whole question is about "multiple" versus "single" database calls. But it's unclear how many are multiple. Because of what I said above, as a general rule of thumb, I recommend making as few database calls as necessary. But that is only a rule of thumb.

Here is why:

  1. Databases are great at reading data. They are storage engines. However, your business logic lives in your application. If you make a rule that every API call results in exactly one database call, then your business logic may end up in the database. Maybe that is ok. A lot of systems do that. But some don't. It's about flexibility.
  2. Sometimes to achieve good decoupling, you want to have 2 database calls separated. For example, perhaps every HTTP request is routed through a generic security filter which validates from the DB that the user has the right access rights. If they do, proceed to execute the appropriate function for that URL. That function may interact with the database.
  3. Calling the database in a loop. This is why I asked how many is multiple. In the example above, you would have 2 database calls. 2 is fine. 3 may be fine. N is not fine. If you call the database in a loop, you have now made performance linear, which means it will take longer the more that is in the loop's input. So categorically saying that the API network time is the slowest completely overlooks anomalies like 1% of your traffic taking a long time due to a not-yet-discovered loop that calls the database 10,000 times.
  4. Sometimes there are things your app is better at, like some complex calculations. You may need to read some data from the database, do some calculations, then based on the results, pass a parameter to a second database call (maybe to write some results). If you combine those into a single call (like a stored procedure) just for the sake of only calling the database once, you have forced yourself to use the database for something which the app server might be better at.
  5. Load balancing: You have 1 database (presumably) and multiple load balanced application servers. Therefore, the more work the app does and the less the database does, the easier it is to scale because it's generally easier to add an app server than setup database replication. Based on the previous bullet point, it may make sense to run a SQL query, then do all the calculations in the application, which is distributed across multiple servers, and then write the results when finished. This could give better throughput (even if the overall transaction time is the same).

TL;DR

TLDR: Is it really significant to worry about multiple database calls when we are already making a network call over the LAN? If so, why?

Yes, but only to a certain extent. You should try to minimize the number of database calls when practical, but don't combine calls which have nothing to do with each other just for the sake of combining them. Also, avoid calling the database in a loop at all costs.

3

Sounds like your team is optimizing before they have a reason to. Have you measured time to execute these requests? Chances are forcing this paradigm will create worse performance for the end user as the round trips to the web server will have a much higher latency than the connection time from the web server to the database. On top of that most web browsers will only make 2 concurrent connections to a single web server, so for complex pages you will likely run into a bottleneck there.

Either way optimization decisions should not be made without data to back it up. Measure it and figure out what is best for your application.

  • 1
    This is a good comment about our poor performance practices, but doesn't answer my question about whether DB calls are something to worry about when I have a network call already. – ashes999 Sep 3 '14 at 16:24
  • 1
    In general, I have found making multiple database calls to not be a problem. This is mostly because of connection pooling and the small latency between the DB and web server. There is a point where making a bunch of different db calls will negatively impact performance, but I do not have a hard number for you. It is all dependent on the environment and the application. Only measuring will give you the answer you seek. – brianfeucht Sep 3 '14 at 18:11
  • It shouldn't (necessarily) depend on specifics, because I'm talking about order of magnitude. – ashes999 Sep 3 '14 at 18:41
  • Just rough guesses (you need to measure): Average time to connect to DB from Web Server: 2ms Average time to connect to Web Server from Client: 20ms So assuming those numbers I randomly pulled out of the air are correct, you could do 10 database calls in the time it takes to do one web service call. Assuming that the database queries take the same amount of time. Those numbers on extremely dependent on environment. If the client making the web service call is local it may drop that by several orders of magnitude. – brianfeucht Sep 4 '14 at 15:30
2

We can't tell you.

We don't what your queries look like. We don't know how long they take to complete. We don't know how much overhead is involved in each request to your API server. We don't know how geographically dispersed your clients are. Etc.

If this is a scenario that requires optimization and is one in which you can decide whether to split or join the calls together, you need to benchmark it both ways: Decide what you're optimizing for (UI latency, server CPU load, contention, etc.) and pick the one the one that better achieves your optimization goal.


Aside from that, the only one thing I can add with relative certainty is this:

Within a single request, you should perform all the queries you need to perform to build a response.

In other words, if the response can't be generated until all N queries are performed, it's usually senseless to separate them. If you can generate meaningful results, whether intermediate or complete, after each query, start benchmarking.

1

Two thoughts:

First, to the consumer using the API, he is making one call to accomplish a task. What happens after your server receives the call to fill the request should not be so rigid. If that one call from a consumer requires 10 sub-work items to pull the data together and return it, then that should be acceptable.

Second: Do you see an actual database performance problem with the process in question? My experience has shown that often trying to put all aspects of a database request into a single call can result in a less efficient call than simply making three or four calls for data. Modern databases are very efficient in caching and execution plans. Often, when you try to do too much you will see procedures with cursors (very bad for performance because data is acted on row by row, not as a set at once) and code that results in a less efficient plan than if you had broken the call up into several small easy steps.

Out of simple organization of code, I do agree that each API call should possibly call a single stored procedure (or db function) which in turn is responsible for filling the request. There may be more than one step in the procedure.

  • I agree with you about measuring performance, which nobody seems to be doing. There's no proof that this is faster, but it just keeps coming up. Performance comes up as an issue when we have some calls that may make, say, 1000 DB SELECTs. – ashes999 Sep 3 '14 at 16:25
  • @ashes999 while you may gain speed looking at the number of db calls, it's more likely found in indexing strategy etc. not the number of calls. As everyone has indicated, look at performance data. – Richard Sep 3 '14 at 16:30
  • Richard, I agree, and I actually know that. My question is why various people keep bringing up this point that "multiple DB calls are slow" when there's a network call involved. I really don't see how it can be significant. – ashes999 Sep 3 '14 at 16:44
  • @ashes999 Sorry, maybe you should go into a little more detail about the network call, since that seems obvious, I sense there is a little more to your question. I feel we are missing something in your questions. You will always suffer some network latency, and each call potentially does increase by "x" times for each call (in simple terms). The statement at face value is true, multiple network calls will be slower than one network call to the db. That's why I suggest one call to a stored procedure, then, that can make multiple calls to the db without the multi network calls. – Richard Sep 3 '14 at 17:15
1

If the database is on a different server than your REST service, each database call will result in a network roundtrip and that can significantly hurt performance:

I once observed a single webservice call be translated to about 500 database queries - this was hardly a problem when both the webservice and the database are located on the same machine, but turned into a response time of 6-7 seconds when they were on different machines.

Obviously, 500 roundtrips to the database is pretty extreme. I'm not sure what your performance requirements are, but as a rule-of-thumb I would say that if you stay under around 10 database queries per REST-call you should not experience a significant performance hit.

1

We have a couple of applications which are very, very chatty. There is a database call for every. Single. Little. Thing. Serving reference data again and again and again is a major part of the workload on the system. All that scheduling of worker threads, acquiring and dropping locks, plan cache checking etc. adds up even if there is not actual disk IO. Contention is higher because transactions have to hold locks across multiple DB calls and so throughput is much lower than it could be. Those teams are now looking at having to buy new, very expensive DB servers because of this.

So, although the majority of the elapsed time in your system's current configuration is taken with REST API calls, ignoring performance at the DB level is storing problems for the future.

0

The optimization path presented is simply the wrong way to look at things.

API calls should be atomic. In other words, I should be able to make 1 web API call to perform the action I want. Whether that is to fetch data, update a record or whatever. It should NEVER take more than 1 call to cause the action. And attempting to leverage transactions across multiple calls should be shunned like the plague.

Sometimes a single action is rather complex. For example, fetching data which is combined from several sources: again, this should be a single call. Either the entire thing works or the entire thing fails.

Now, saying that a single API call should only execute one DB query is a bit moronic. As you've pointed out, the overhead for marshalling the call across the network is often orders of magnitude more expensive in terms of overall time.

I can somewhat understand their statement that a single query runs can faster than several; but this gives a false impression as it ignores the total DB and network load. Only by profiling the various ways of pulling data out of the DB can you figure out what the problem really is. I'm sure everyone has a story where a particular query executed 100 times more often than expected killed the system until a proper index was put in place...

Ultimately you aren't going to be able to convince them with just talk. Set up a test case for both approaches and profile them. Pay attention to total time to acquire the data you need, amount of network traffic generated, number of and timing of the database calls etc. Take a holistic approach - meaning that you look at the entire system - and you should end up with plenty of data to either eat crow or show them the golden path.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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