Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

There are situations which require to have 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?

share|improve this question

3 Answers 3

up vote 6 down vote accepted

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

share|improve this answer

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.

share|improve this answer
1  
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 '13 at 21:57
    
Good catch Martin, thanks. –  Ali Razeghi Feb 22 '13 at 23:20
    
@MartinSmith You suspect correctly. It is MySQL. –  Hamed Momeni Feb 23 '13 at 9:43

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.

share|improve this answer
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 '13 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. –  Hamed Momeni Feb 23 '13 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 '13 at 22:29

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.