4 ms·

Funny, I had this conversation with one of my professors the other day. I've never written straight SQL--only used ORM. He showed us a giant SQL query and my

Funny, I had this conversation with one of my professors the other day. I've never written straight SQL--only used ORM. He showed us a giant SQL query and my first question was "do people really write it this way, instead of building up from intermediate results?" It doesn't look maintainable. He said yes, and that the SQL programmers are proud of writing huge queries. The query engine will optimize the hell out of it, so it's the right thing to do.

tom_b 15y ago
Wow! Please tell me that you used ORM as a personal choice on a project you were putting together rather than being handed an ORM before a little SQL in coursework. Nothing wrong with ORMs at all. Just that I would have expected at least a little SQL as introduction to relational (set) thinking. I hope that we haven't simply gone completely OO-imperative in classes these days. BTW, wait until you see the SQL generated by some report generation tools - pages and pages of very weird SQL
- dhconnelly 15y ago
  Don't worry--the ORM was for personal projects. The course covered relational algebra and calculus before even talking about SQL, and mapping came after that.
  - mechanical_fish 15y ago
    The course covered relational algebra and calculus before even talking about SQL This is the peril of leaving programming education to the computer scientists: People are routinely forced to rebuild a car's engine before being told what a stoplight is or how to merge onto the freeway. And it might help explain why my coworkers think I'm some sort of RDBMS genius when all I've ever done was read SQL for Web Nerds: Database researchers love to talk about relational algebra, n-tuples, normal form, and natural composition, while throwing around mathematical symbols. This patina of mathematical obscurity tends to distract your attention from their bad suits and boring personalities, but is of no value if you just want to use a relational database management system. In fact, this is all you need to know to be a Caveman Database Programmer: A relational database is a big spreadsheet that several people can update simultaneously. A caveman I may be, but I tell you, if you can type SHOW PROCESSLIST, EXPLAIN PLAN, and CREATE INDEX with effect, write a self-join, and use the GROUP BY… HAVING syntax, many people will speak of you as they would of Gandalf. It's embarrassing, really.
    - gruseom 15y ago
      A relational database is a big spreadsheet that several people can update simultaneously As someone who works on a "big spreadsheet that several people can update simultaneously", I can't resist making an off-topic comment. The analogy between spreadsheets and relational databases is seductive because both appear to use tables (actually, spreadsheets use a grid addressing system which only looks like a table). But really the two are from different planets: their data models as well as their computational models are incompatible. For example, compare how databases relate data (foreign keys) to how spreadsheets do it (cell references). What do these have in common? Basically nothing. The point may seem esoteric, but there have been lots of attempts to combine spreadsheets and databases and they all run aground on this mismatch. Crude integration a la import/export works ok, but the perennial dream of having a true spreadsheet on top of a relational database is out of the question unless you sacrifice efficiency, which you can't. I do wonder sometimes how far you could get implementing SQL on top of spreadsheet data. As an aside, it's interesting that the research literature on databases is vast and rich while the literature on spreadsheets (alas!) approximates nil.
dwmartin18 15y ago
"I've never written straight SQL" Wow. Just curious, have you ever programmed professionally? If not, I'd highly recommend you learn to write raw SQL. And not just the basics but really learn it in depth. ORMs are definitely very useful but I've never been able to complete a single project without writing a fair amount of SQL because the ORM was too slow or couldn't give me what I needed.
- dhconnelly 15y ago
  Yes, for two years. I was a math major in undergrad, though, and I never had to do it for work. I've only worked on one database-backed application, and it used a NoSQL database. That's why I took this course while I'm in grad school.
dmethvin 15y ago
SQL is just like any other language. If you're new to it then sure it's going to look strange. But a large well-designed query can be many times faster than "building up from intermediate results". Your procedural code is off in another process from the SQL server, maybe even in another server across the country. Pulling the intermediate results back and forth between them is expensive. ORM is great when a database is being used as a simple program data persistence layer, but if you limit yourself to that then you might as well use a NoSQL solution.
- mattmanser 15y ago
  The more I've worked with SQL, the more I'm starting to question this idea that huge elegant queries are any good. I used to almost be fanatical about only returning exactly the information I needed from the DB. They are such a nightmare to read, there's no exposure to them in the code that your IDE can make sense of, if there's a performance problem it's almost always down to one of those huge queries and in the end most software I work with the DB engine is either on the same box or on the same network so pushing across more rows to process in the app in a far more generic way doesn't cause the sort of latency problems you describe (seems a pretty rare config imo). So I disagree. Also I disagree with your admittedly fairly throw away comment about ORM & NoSQL. Even if it is just a data persistence layer bunging everything in a NoSQL solution is usually going to be the wrong call in the long run. If you're dealing with data it almost always pays off to keep it in a relational structure because if the app/business is a success someone will invariably start wanting to use the data in other ways. I think NoSQL is good for a very small subset of data storage problems, I want to use it, but have yet to have had a reason to.
  - ldh 15y ago
    You make good points, but I'm not entirely sure that the possibility of someday needing to do analytics on the data precludes one from using NoSQL now. I've heard/read about a good deal of large scale sites running on NoSQL alongside a relational database setup for reporting. Seems like it's not that big a deal to move some of that data into a SQL store if/when it becomes necessary? If NoSQL gets you the simplicity and performance characteristics you want, maybe it's a premature optimization to limit your options based on hypothetical future scenarios. I could see it from either perspective.
mickeyp 15y ago
I wouldn't say "SQL Programmers" (or more likely, programmers who do SQL) are "proud" of their large queries, but they are a fact of life in most businesses. Report generation and other complex business logic does take up a lot of lines, and it is indeed "good form" to keep it in one query when it makes sense to do so, or farm out some of the work to views or even materialised views, depending on what you need. Once you're used to reading it you'll quickly realise that most queries are actually made up of many sub queries nested inside and that they are mostly independent of the larger, parent, query. Having said that, ORMs are terrible at generating efficient queries for anything but toy requirements. Optimising a query is hard for a human and even harder for an ORM. That massive query could just as well have been a product of the ORM. I'm sure developers out there get by with just an ORM and treat it like a Chinese wall between them and the mystery black box that is the RDBMS. I think that's a huge mistake. Relational DBs are incredibly powerful.

tom_b 15y ago

Wow! Please tell me that you used ORM as a personal choice on a project you were putting together rather than being handed an ORM before a little SQL in coursework. Nothing wrong with ORMs at all. Just that I would have expected at least a little SQL as introduction to relational (set) thinking. I hope that we haven't simply gone completely OO-imperative in classes these days. BTW, wait until you see the SQL generated by some report generation tools - pages and pages of very weird SQL

dhconnelly 15y ago
Don't worry--the ORM was for personal projects. The course covered relational algebra and calculus before even talking about SQL, and mapping came after that.
- mechanical_fish 15y ago
  The course covered relational algebra and calculus before even talking about SQL This is the peril of leaving programming education to the computer scientists: People are routinely forced to rebuild a car's engine before being told what a stoplight is or how to merge onto the freeway. And it might help explain why my coworkers think I'm some sort of RDBMS genius when all I've ever done was read SQL for Web Nerds: Database researchers love to talk about relational algebra, n-tuples, normal form, and natural composition, while throwing around mathematical symbols. This patina of mathematical obscurity tends to distract your attention from their bad suits and boring personalities, but is of no value if you just want to use a relational database management system. In fact, this is all you need to know to be a Caveman Database Programmer: A relational database is a big spreadsheet that several people can update simultaneously. A caveman I may be, but I tell you, if you can type SHOW PROCESSLIST, EXPLAIN PLAN, and CREATE INDEX with effect, write a self-join, and use the GROUP BY… HAVING syntax, many people will speak of you as they would of Gandalf. It's embarrassing, really.
  - gruseom 15y ago
    A relational database is a big spreadsheet that several people can update simultaneously As someone who works on a "big spreadsheet that several people can update simultaneously", I can't resist making an off-topic comment. The analogy between spreadsheets and relational databases is seductive because both appear to use tables (actually, spreadsheets use a grid addressing system which only looks like a table). But really the two are from different planets: their data models as well as their computational models are incompatible. For example, compare how databases relate data (foreign keys) to how spreadsheets do it (cell references). What do these have in common? Basically nothing. The point may seem esoteric, but there have been lots of attempts to combine spreadsheets and databases and they all run aground on this mismatch. Crude integration a la import/export works ok, but the perennial dream of having a true spreadsheet on top of a relational database is out of the question unless you sacrifice efficiency, which you can't. I do wonder sometimes how far you could get implementing SQL on top of spreadsheet data. As an aside, it's interesting that the research literature on databases is vast and rich while the literature on spreadsheets (alas!) approximates nil.

dwmartin18 15y ago

"I've never written straight SQL" Wow. Just curious, have you ever programmed professionally? If not, I'd highly recommend you learn to write raw SQL. And not just the basics but really learn it in depth. ORMs are definitely very useful but I've never been able to complete a single project without writing a fair amount of SQL because the ORM was too slow or couldn't give me what I needed.

dhconnelly 15y ago
Yes, for two years. I was a math major in undergrad, though, and I never had to do it for work. I've only worked on one database-backed application, and it used a NoSQL database. That's why I took this course while I'm in grad school.

dmethvin 15y ago

SQL is just like any other language. If you're new to it then sure it's going to look strange. But a large well-designed query can be many times faster than "building up from intermediate results". Your procedural code is off in another process from the SQL server, maybe even in another server across the country. Pulling the intermediate results back and forth between them is expensive. ORM is great when a database is being used as a simple program data persistence layer, but if you limit yourself to that then you might as well use a NoSQL solution.

mattmanser 15y ago
The more I've worked with SQL, the more I'm starting to question this idea that huge elegant queries are any good. I used to almost be fanatical about only returning exactly the information I needed from the DB. They are such a nightmare to read, there's no exposure to them in the code that your IDE can make sense of, if there's a performance problem it's almost always down to one of those huge queries and in the end most software I work with the DB engine is either on the same box or on the same network so pushing across more rows to process in the app in a far more generic way doesn't cause the sort of latency problems you describe (seems a pretty rare config imo). So I disagree. Also I disagree with your admittedly fairly throw away comment about ORM & NoSQL. Even if it is just a data persistence layer bunging everything in a NoSQL solution is usually going to be the wrong call in the long run. If you're dealing with data it almost always pays off to keep it in a relational structure because if the app/business is a success someone will invariably start wanting to use the data in other ways. I think NoSQL is good for a very small subset of data storage problems, I want to use it, but have yet to have had a reason to.
- ldh 15y ago
  You make good points, but I'm not entirely sure that the possibility of someday needing to do analytics on the data precludes one from using NoSQL now. I've heard/read about a good deal of large scale sites running on NoSQL alongside a relational database setup for reporting. Seems like it's not that big a deal to move some of that data into a SQL store if/when it becomes necessary? If NoSQL gets you the simplicity and performance characteristics you want, maybe it's a premature optimization to limit your options based on hypothetical future scenarios. I could see it from either perspective.

mickeyp 15y ago

I wouldn't say "SQL Programmers" (or more likely, programmers who do SQL) are "proud" of their large queries, but they are a fact of life in most businesses. Report generation and other complex business logic does take up a lot of lines, and it is indeed "good form" to keep it in one query when it makes sense to do so, or farm out some of the work to views or even materialised views, depending on what you need. Once you're used to reading it you'll quickly realise that most queries are actually made up of many sub queries nested inside and that they are mostly independent of the larger, parent, query. Having said that, ORMs are terrible at generating efficient queries for anything but toy requirements. Optimising a query is hard for a human and even harder for an ORM. That massive query could just as well have been a product of the ORM. I'm sure developers out there get by with just an ORM and treat it like a Chinese wall between them and the mystery black box that is the RDBMS. I think that's a huge mistake. Relational DBs are incredibly powerful.