20

There are situations which require having really big query joining several tables together with sub select statements in them to produce the desired results.

My question is, should we consider using multiple smaller queries and bring the logical operations into the application layer by querying the DB in more than one calls or it's better to have them all in one go?
For example consider the following query:

SELECT *
FROM   `users`
WHERE  `user_id` IN (SELECT f2.`friend_user_id`
                     FROM   `friends` AS f1
                            INNER JOIN `friends` AS f2
                              ON f1.`friend_user_id` = f2.`user_id`
                     WHERE  f2.`is_page` = 0
                            AND f1.`user_id` = "%1$d"
                            AND f2.`friend_user_id` != "%1$d"
                            AND f2.`friend_user_id` NOT IN (SELECT `friend_user_id`
                                                            FROM   `friends`
                                                            WHERE  `user_id` = "%1$d"))
       AND `user_id` NOT IN (SELECT `user_id`
                             FROM   `friend_requests`
                             WHERE  `friend_user_id` = "%1$d")
       AND `user_image` IS NOT NULL
ORDER  BY RAND() 
LIMIT %2$d

What's the best way of doing it?

Improve this question

4 Answers 4

Reset to default
16

I am going to disagree on large and complicated queries with datagod here. I see these only as problems if they are disorganized. Performance-wise, these are almost always better because the planner has much more freedom in how to go about retrieving the information. However, large queries do need to be written with maintainability in mind. In general, I have found that simple, well-structured SQL to be easy to debug even when a single query goes on for 200+ lines. This is because usually you have a pretty good idea of what kind of problem you are dealing with so there are only a few areas in the query that you have to check.

The maintenance problems, IME, come in when the structure of SQL breaks down. Long, complex queries in subselects impairs readability and troubleshooting, as do inline views, and both of these should be avoided in long queries. Instead, use VIEWs if you can (note if you are on MySQL, views do not perform all that well, but on most other db's they do), and use common table expressions where those don't work (MySQL doesn't support these btw).

Long complex queries work pretty well both from a maintainability and performance case where you keep your where clauses simple, and where you do as much as you can with joins instead of subselects. The goal is to make it so that "records aren't showing up" gives you a few very specific places in the query to check (is it getting dropped in a join or filtered out in a where clause?) and so the maintenance team can actually maintain things.

Regarding scalability, keep in mind that the more flexibility the planner has, that's a good thing too....

Edit: You mention this is MySQL, so views are unlikely to perform that well and CTE's are out of the question. Additionally the example given is not particularly long or complex so that's no problem.

Improve this answer
2
  • Note: I have had queries (not in MySQL, but still...) that were long and complex enough that the query plans generated were not optimal. In these cases, you can indeed get faster results breaking one extremely complex query into two less complex queries. That said, it's rare, and I'll generally write the complex query and find out if there's a problem rather than breaking the query into smaller chunks pre-emptively.
    – RDFozz
    Dec 15, 2017 at 16:34
  • When performance is not a huge concern, I also prefer to see all the joins and where clauses in one query so I know what tables are involved and what logic is being implemented. Especially true when building a dataset before any aggregation. Breaking them into multiple pieces, especially temp tables, makes individual queries more readable and performant, but after reading 200 lines of code, I am back to "so what was this all about?" and start over.
    – Kathmandude
    May 28, 2021 at 20:29
12

As somebody who has to support/cleanup these large and complicated queries, I would say it is far better to break them apart into several small easy to understand chunks. It is not necessarily better from a performance point of view, but you are at least giving SQL a better chance to come up with a good query plan.

Make life easier on the people that follow you, and they will say good things about you. Make it hard on them and they will curse you.

Improve this answer
3
  • 2
    the disadvantage of a string of simple queries though is that state changes significantly across them, making the overall debugging of the application more complex. I.e. you can debug large SQL queries often as trees but application code gets debugged statement by statement checking how state changes in statements. The real issues have to do with the fact that subselects and inline views are also their own trees.....
    – Chris Travers
    Feb 23, 2013 at 8:45
  • In my case the only one who has to manage the DB and the code is myself. And mostly my question was about performance point the query.
    – 2hamed
    Feb 23, 2013 at 9:47
  • You guys would have to take a look at the way I write my large batch processes. Break things down to simple queries, very easy to read. I am biased because the queries I end up trying to tidy up are routinely over 1000 lines long.
    – datagod
    Feb 23, 2013 at 22:29
5

My 2 cents on the 2 keywords query-performance and scalability:

Query-Performance: SQL Server parallelism already does a very good job breaking down queries into multi-threaded searches so I'm not sure how much of a query-performance improvement you'll see by doing it for SQL Server. You will have to look at the execution plan to see how much of a degree of parallelism you get when you execute it however and compare results both ways. If you end up having to use a query hint to get the same or better performance, then IMO it's not worth it as the query hint might not be optimal later.

Scalability: Reading the queries might be easier as datagod stated, and breaking it down into separate queries makes sense if you can use your new queries in other areas too, but if you're not going to use them for other calls as well, then it'll be even more stored procs to manage for 1 task, and IMO wouldn't contribute any to scalability.

Improve this answer
2
  • 2
    RE: "SQL Server" references although the OP hasn't specified any particular RDBMS I suspect they are on MySQL from the back ticks and LIMIT
    – Martin Smith
    Feb 22, 2013 at 21:57
  • @MartinSmith You suspect correctly. It is MySQL.
    – 2hamed
    Feb 23, 2013 at 9:43
2

Some times, there is no choice but to split the big/complex query into small queries. The best way to determine that would be to use EXPLAIN statement with the SELECT statement. The number of traces/scans that your db is going to make to fetch your data is the product of the "rows" values returned by your EXPLAIN query. In our case, we had a query joining 10 tables. For on particular record, the trace amounted to 409M that blogged our DB and pushed our CPU usage of our DB server over 300%. We were able to retrieve the same information by splitting the queries much much faster.

So, in short, in some cases splitting a complex/big query makes sense but in other it may lead to many performance or maintainabiliy issue and this should be treated on a case-by-case basis.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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