4 ms·

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 "bu

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.
  - mattmanser 15y ago
    I'm challenging the idea that if you're just using an ORM for persisting objects you're probably doing it wrong. For the vast set of programming problems having that user table that's relationally linked to a person table that's relationally linked to a company table that's relationally linked to, etc. gives you far more flexibility than having to start dealing with getting objects out of a key-value pair store and manually piecing together complex relationships just to add a simple page to the client's admin page to say how many people have setup their account properly (as a random real-world example where the admin emails their staff and they setup their own user details). Once you get past the basic CRUD of a problem and start having to use the data for functionality, basically past anything more than a toy program, the relational model through an ORM is going to be much simpler than a NoSQL solution. I just don't get how this statement makes sense: 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 I wouldn't because as soon as you start adding fairly simple functionality the NoSQL solution would rapidly become a nightmare. To me NoSQL would be the go to solution if I had a few very basic data structures that I had to do a lot of work on, not the opposite as dmethvin suggests, lots of different objects with simple operations. In concrete examples, Google, NoSQL, Instagram, probably NoSQL, something like Freckle (Time Tracking), SQL, Salesforce, SQL all the way. But as I said I've not had a project or personal project yet where I've gone 'Aha, now is the time! NoSQL here I come!' so push back if I'm doing it wrong!
    - dmethvin 15y ago
      It sounds like we are agreeing then. If you are using the data for ad-hoc reporting via a SQL query tool for example, you're not just using your database as a simple program data persistence layer. If you're not careful though, your program-oriented database won't be very useful outside the realm of the program. As an example, I worked on a database where they persisted a good chunk of data in each record as a JSON string. They did it because they didn't want the hassle of updating the database schema. It worked fine for their needs inside the program, but it was kind of hard to do an ad-hoc query on that.

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.
- mattmanser 15y ago
  I'm challenging the idea that if you're just using an ORM for persisting objects you're probably doing it wrong. For the vast set of programming problems having that user table that's relationally linked to a person table that's relationally linked to a company table that's relationally linked to, etc. gives you far more flexibility than having to start dealing with getting objects out of a key-value pair store and manually piecing together complex relationships just to add a simple page to the client's admin page to say how many people have setup their account properly (as a random real-world example where the admin emails their staff and they setup their own user details). Once you get past the basic CRUD of a problem and start having to use the data for functionality, basically past anything more than a toy program, the relational model through an ORM is going to be much simpler than a NoSQL solution. I just don't get how this statement makes sense: 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 I wouldn't because as soon as you start adding fairly simple functionality the NoSQL solution would rapidly become a nightmare. To me NoSQL would be the go to solution if I had a few very basic data structures that I had to do a lot of work on, not the opposite as dmethvin suggests, lots of different objects with simple operations. In concrete examples, Google, NoSQL, Instagram, probably NoSQL, something like Freckle (Time Tracking), SQL, Salesforce, SQL all the way. But as I said I've not had a project or personal project yet where I've gone 'Aha, now is the time! NoSQL here I come!' so push back if I'm doing it wrong!
  - dmethvin 15y ago
    It sounds like we are agreeing then. If you are using the data for ad-hoc reporting via a SQL query tool for example, you're not just using your database as a simple program data persistence layer. If you're not careful though, your program-oriented database won't be very useful outside the realm of the program. As an example, I worked on a database where they persisted a good chunk of data in each record as a JSON string. They did it because they didn't want the hassle of updating the database schema. It worked fine for their needs inside the program, but it was kind of hard to do an ad-hoc query on that.