4 ms·

One approach I've seen that works well for untangling gnarly SQL is to refactor those mega-queries into smaller, component queries that are pieced together by a

One approach I've seen that works well for untangling gnarly SQL is to refactor those mega-queries into smaller, component queries that are pieced together by application code. This keeps the processing on the DB server but avoids the pain that comes with programming in SQL. I rather like SQL, but it doesn't handle complexity well at all (e.g. maintaining a "variable" to be used in different parts of the query). For example, I once worked on a Foursquare clone originally written by a hardcore PostgreSQL nut. This system had a query that, if memory serves, returned a list of places of a certain type within a geographic area along with user activity on those places (votes, comments, etc). This was around a 75 line SQL query that actually wasn't that fast (response times from the DB were roughly 1 second even with every join indexed). We rewrote that query into 4 smaller queries (place ID's within that area, place ID's within that category, hydrating those places from the filtered ID's and then getting the user info), and that cut our DB response by about 70% in addition to making the system easier to work with. This required roughly 10 lines of Java code and a variable - a list of ID's that we got first and passed into each other query. It also freed us up to do other things - for instance, if performance were still a problem, queries 2-4 could have been done asynchronously behind a latch. By lifting the "glue" out of SQL and into a better language, it freed us to do new things, and it freed the database from having to juggle unnecessary complexity while planning and executing its queries.

crazygringo 13y ago
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.
- boomzilla 13y ago
  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.
- walshemj 13y ago
  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.
- grosskur 13y ago
  Agreed. The Postgres "with" statement is also a great way to make large queries readable: http://www.postgresql.org/docs/9.2/static/queries-with.html http://www.postgresql.org/docs/9.2/static/queries-with.html I first saw this demonstrated in Peter van Hardenberg's excellent Waza 2013 presentation, "Postgres: The Bits You Haven't Found": http://vimeo.com/61044807 http://vimeo.com/61044807
eftpotrm 13y ago
Generally that sort of thing IME is a sign that you're got a hardcore SQL but who isn't as good as they think and / or a poor SQL dialect. I've known far, far larger things than that written in pure server side SQL (often made more readable by breaking it into sub-queries, table variables and common table expressions) that were very, very fast. SQL is much more powerful than many realise, but there's a great many developers who aren't as good at it as they think.
dventimi 13y ago
Another approach is to refactor mega-queries into smaller, component queries using SQL views that are then pieced together in another, simpler query. YRMV.
- chris_wot 13y ago
  Not the worst approach, but they can be very hard to maintain. If the view has an error, then it can be hard to troubleshoot what is going on.

crazygringo 13y ago

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.

boomzilla 13y ago
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.
walshemj 13y ago
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.
grosskur 13y ago
Agreed. The Postgres "with" statement is also a great way to make large queries readable: http://www.postgresql.org/docs/9.2/static/queries-with.html http://www.postgresql.org/docs/9.2/static/queries-with.html I first saw this demonstrated in Peter van Hardenberg's excellent Waza 2013 presentation, "Postgres: The Bits You Haven't Found": http://vimeo.com/61044807 http://vimeo.com/61044807

eftpotrm 13y ago

Generally that sort of thing IME is a sign that you're got a hardcore SQL but who isn't as good as they think and / or a poor SQL dialect. I've known far, far larger things than that written in pure server side SQL (often made more readable by breaking it into sub-queries, table variables and common table expressions) that were very, very fast. SQL is much more powerful than many realise, but there's a great many developers who aren't as good at it as they think.

dventimi 13y ago

Another approach is to refactor mega-queries into smaller, component queries using SQL views that are then pieced together in another, simpler query. YRMV.

chris_wot 13y ago
Not the worst approach, but they can be very hard to maintain. If the view has an error, then it can be hard to troubleshoot what is going on.