70

I have been working for different companies, and I have noticed that some of them prefer to have views that will join a table with all its "relatives". But then on the application some times, we only need to use only 1 column.

So would it be faster to just make simple selects, and then "join" them on the system code?

The system could be php, java, asp, any language that connect to the database.

So the question is, what is faster going from a Server Side(php, java, asp, ruby, python...) to the database run one query that get everything we need or going from the server side to the database and run a query that only get the columns from one table at the time?

  • 2
    Which implementation of 'SQL' are you using? MySQL, Microsoft SQL Server, Oracle, Postgresql, etc? Please update your tag. – RLF Sep 18 '14 at 12:30
  • 1
    Mysql and Postgresql – sudo.ie Sep 18 '14 at 12:45
  • 6
    My experience is that MySQL doesn't like complicated queries and is usually faster with very simply queries (but more). Postgres' query optimizer is much better and there it is usually more efficient to run a single large query. – a_horse_with_no_name Sep 18 '14 at 12:51
  • 3
    @a_horse_with_no_name That is very broad generalization, specially in the context of this question. MySQL optimizer is indeed very simple by design, and can cause problems with joins and sub-queries -specially on older versions of MySQL- that have otherwise produce faster plans in PostgreSQL, while MySQL can be very fast for pure OLTP loads. However, in the context of the question, a single large query will be faster that, let's say -in the worse possible scenario- a SELECT inside a programming loop (no matter the RDBMS used). – jynus Sep 18 '14 at 13:54
  • 2
    @jynus: well, the question is very broad (plus: I said "in my experience" - other people might have different experiences). A query inside a LOOP is never a good idea and almost always the result of poor design or lack of understanding how to work with a relational database. – a_horse_with_no_name Sep 18 '14 at 15:11
69

What would address your question is the subject JOIN DECOMPOSITION.

According to Page 209 of the Book

High Performance MySQL

You can decompose a join by running multiple single-table queries instead of a multitable join, and then performing the join in the application. For example, instead of this single query:

SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id = tag.id
JOIN post ON tag_post.post_id = post.id
WHERE tag.tag = 'mysql';

You might run these queries:

SELECT * FROM tag WHERE tag = 'mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

Why on earth would you do this ? It looks wasteful at first glance, because you've increased the number of queries without getting anything in return. However, such restructuring can actually give significant performance advantages:

  • Caching can be more efficient. Many applications cache "objects" that map directly to tables. In this example, if the object with the tag mysql is already cached, the application will skip the first query. If you find posts with an ID of 123, 567, or 908 in the cache, you can remove them from the IN() list. The query cache might also benefit from this strategy. If only one of the tables changes frequently, decomposing a join can reduce the number of cache invalidations.
  • Executing the queries individually can sometimes reduce lock contention
  • Doing joins in the application makes it easier to scale the database by placing tables on different servers.
  • The queries themselves can be more efficient. In this example, using an IN() list instead of a join lets MySQL sort row IDs and retrieve rows more optimally than might be possible with a join.
  • You can reduce redundant row accesses. Doing a join in the application means retrieving each row only once., whereas a join in the query is essentially a denormalization that might repeatedly access the same data. For the same reason, such restructuring might also reduce the total network traffic and memory usage.
  • To some extent, you can view this technique as manually implementing a hash join instead of the nested loops algorithm MySQL uses to execute a join. A hash join might be more efficient.

As a result, doings joins in the application can be more efficient when you cache and reuse a lot of data from earlier queries, you distribute data across multiple servers, you replace joins with IN() lists, or a join refers to the same table multiple times.

OBSERVATION

I like the first bulletpoint because InnoDB is a little heavy-handed when it crosschecks the query cache.

As for the last bulletpoint, I wrote a post back on Mar 11, 2013 (Is there an execution difference between a JOIN condition and a WHERE condition?) that describes the nested loop algorithm. After reading it, you will see how good join decomposition may be.

As for all other points from the book, the developers really look for performance as the bottom line. Some rely on external means (outside of the application) for performance enhancements such as using a fast disk, get more CPUs/Cores, tuning the storage engine, and tuning the configuration file. Others will buckle down and write better code. Some may resort to coding all the business intelligence in Stored Procedures but still not apply join decomposition (See What are the arguments against or for putting application logic in the database layer? along with the other posts). It's all up to the culture and tolerance of each developer shop.

Some may be satisfied with performance and not touch the code anymore. Other simply don't realize there are great benefits one can reap if they try join composition.

For those developers that are willing ...

GIVE IT A TRY !!!

  • 3
    As for that link about changing to 3 queries... I know and respect Baron, Vadim, and Peter, but I disagree with this misleading suggestion. Most of the arguments in favor of the split up are so rare as to be not worth mentioning. Stick with a single query with JOINs, then let's work on improving it. – Rick James Oct 29 '16 at 19:31
  • 2
    @RickJames I agree with the spirit of your comment. Over the years, I have seen join decomposition work for some and fail for others. Even with the proper SQL skillset, it could work against you if the join decomposition isn't done right. At my current employer, many depts love scaling up and out, especially when legacy code is involved and deep pockets are available. With those who have caviar taste but egg salad budgets, join decomposition could be worth the risk but must be done right. – RolandoMySQLDBA Oct 29 '16 at 20:11
  • I'd love to see how this works in an Oracle environment if I had the rights and time. – Rick Henderson Sep 7 '17 at 14:34
  • One other way it can be faster is that if you're doing ordering, it will be less calculations overall to order smaller lists than to order one large list. – Evan Siroky Oct 30 '18 at 17:46
25

In Postgres (and probably any RDBMS to a similar extent, MySQL to a lesser extent), fewer queries are almost always much faster.

The overhead of parsing and planning multiple queries is already more than any possible gain in most cases.

Not to speak of additional work to be done in the client, combining the results, which is typically much slower at that. An RDBMS specializes in that kind of task and operations are based on original data types. No casting to text and back for intermediate results or transforming to native types of the client, which may even lead to less correct (or incorrect!) results. Think of floating point numbers ...

You also transfer more data between DB server and client. This may be negligible for a hand full of values, or make a huge difference.

If multiple queries mean multiple round trips to the database server, you also collect multiple times the network latency and transaction overhead, possibly even connection overhead. Big, big loss.

Depending on your setup, network latency alone may take longer than all the rest by orders of magnitude.

Related question on SO:

There may be a turning point for very big, long running queries because transactions collect locks on DB rows on the way. Very big queries may hold many locks for an extended period of time which may cause friction with concurrent queries.

  • Just out of curiosity, what do you consider very big? – Sablefoste Jan 9 '17 at 20:14
  • @Sablefoste: Very much depends on your access patterns. A critical point is where concurrent transactions start queuing up, waiting for locks to be released.Or if you accumulate enough locks to eat a substantial part of your resources. Or if your queries run long enough to interfere with autovacuum ... – Erwin Brandstetter Jan 10 '17 at 4:26
  • But if we take a somewhat typical situation - a query that uses an outer join and returns lots of redundant data for "parent" table, which then has to be parsed and sorted out by the app (most probably, some ORM library) versus a small select that fetches all required IDs first and then another smaller select with IN() instead of outer join? Won't the second approach be more efficient (considering both DB and app consumed CPU and communications bandwidth)? – JustAMartin Sep 14 '17 at 7:47
  • 1
    @JustAMartin: That sounds like the kind of query that's almost certainly faster when handled by the query planner of the RDBMS - assuming correct queries. Concerning returns lots of redundant data for "parent" table: Why would you return redundant data? Only return the data you need. – Erwin Brandstetter Jan 10 '18 at 13:35
  • 1
    With outer join RDBMS returns data from the parent table duplicated for every joined child, which means some network & memory overhead, and then some additional parsing in ORM tool to throw the duplicate parent values away and keep only one parent with n children. So, with single query we save on efficient work of RDBMS query planner, less network (or local pipe) requests but lose on additional unneeded payload and shifting data around in ORM library. I guess, it's as always - measure before optimizing. – JustAMartin Jan 10 '18 at 14:23

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.