I've written gigantic, 500+ line queries for MySQL, with probably 40 subqueries within. Obviously, what MySQL receives is a monstrosity that nobody in their right mind would try to understand.
But the query is assembled piece by piece, in separate functions, each subquery responsible for its own contribution to the final query string, with well-defined inputs and outputs. The entire file that generates the query reads quite logically.
And there's simply no alternative -- many pieces of processing involves 100,000+ rows, so round-trips between db and app would be prohibitively slow. The whole thing uses data from around 10 different tables, it's extremely relational.
But because it's structured well and written correctly, the whole thing executes in a small fraction of a second. (Trying to do it in a "NoSQL" style would probably take ten minutes of back-and-forth network communications.)
I've known a lot of programmers who would shy away from such a thing -- but that's because a lot of programmers don't bother to actually understand SQL the way they understand Ruby or JavaScript or PHP. It can do amazing feats of data processing, which is the whole point of a relational database. My advice is, dig deep into SQL. It can work wonders, but it's true that its "best practices" can be difficult to learn, and there's a lot of bad advice out there.
I've never seen that kind of huge queries for OLTP (online transaction processing) apps, but I've written ~1000 line SQL queries for off line batch jobs. Especially with the recent popularity of Hive that allows UDFs written in Java, one can do wonders with SQL.
One thing SQL really helps is it force you to think "data first". Instead of thinking algorithms, step by step what you want to do, it makes you think along the line: what data I got and what output I want to get out of it, not unlike functional programming, but with more focus on data sets.
500+ small beer I have seen stored procedures thousands of lines long in some of BT's smaller mainframe systems from memory it was COSMOS (the one that tracks every circuit in the country) and not CSS which is an even bigger system.
But the query is assembled piece by piece, in separate functions, each subquery responsible for its own contribution to the final query string, with well-defined inputs and outputs. The entire file that generates the query reads quite logically.
And there's simply no alternative -- many pieces of processing involves 100,000+ rows, so round-trips between db and app would be prohibitively slow. The whole thing uses data from around 10 different tables, it's extremely relational.
But because it's structured well and written correctly, the whole thing executes in a small fraction of a second. (Trying to do it in a "NoSQL" style would probably take ten minutes of back-and-forth network communications.)
I've known a lot of programmers who would shy away from such a thing -- but that's because a lot of programmers don't bother to actually understand SQL the way they understand Ruby or JavaScript or PHP. It can do amazing feats of data processing, which is the whole point of a relational database. My advice is, dig deep into SQL. It can work wonders, but it's true that its "best practices" can be difficult to learn, and there's a lot of bad advice out there.