5 ms·

Application vs Database Programming

39 points by einhverfr 15y ago

dhconnelly 15y ago
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
Always test your data operations on data volumes (and user load) that mirror what you expect to see in production. This is important across the entirety of your dev stack.
- mickeyp 15y ago
  Indeed. If you are using Oracle you can use its Database Replay to replay the workload with the same characteristics as the production database on your test or dev environment. Pretty nifty stuff, actually.
- InclinedPlane 15y ago
  One of the biggest problems in database operations is that there are many potential query plans for a given query, but finding the optimal one can be difficult. More so, which query plan is optimal varies depending on table size. If you let the DB pick its own query plan all the time you can end up with inconsistent performance, often by an order of magnitude or more. However, if you turn on query plan stability (every query after the first one will have the same plan) then you can end up with a state where the query plan doesn't change as the data change, which can lead to serious performance issues as a table's size grows (if your table starts out small, the DB may choose a full table scan, which is very fast, but will bog down extremely when the table grows to thousands or millions of row). In short, performance is a hard problem, and if anything the tools for analyzing and optimizing performance for DB operations are well behind the state of the art for compiled code, for example.
  - gaius 15y ago
    Oracle is pretty smart about this, if a new plan is slower than the previous one, it seamlessly flips back.
    - politician 15y ago
      JIT compilers have been doing hotspot recompilation for a while now. Is there a reason why databases haven't adopted the same techniques? Is there some barrier to caching multiple query plans optimized for different load scenarios?
dmethvin 15y ago
It's really hard to say what the performance issue was here, but my experience is that SQL servers do a really good job of handling data sets if you use them correctly. If the problem was a "cache miss" it might mean that one or more tables needs proper indexes for example. Or, if the code involves a cursor and a code loop, that is a very bad sign.
- einhverfr 15y ago
  The problem was that I was doing it as follows: insert/insert/update. Wash rinse repeat. The better way to do it (which solved the problem was: Insert a bunch of rows, insert a bunch of rows, update a bunch of rows, no repeating.
zzzeek 15y ago
Asking as someone who works mostly with relational DBs, where do nonrelational databases fit along this spectrum ? If you're dealing with a large dataset in Cassandra or MongoDB, do you think in terms of instructions or sets, or somewhere in between (or something else entirely) ? My understanding is that if you need to extract a dataset according to complex criteria you'd be using something like map/reduce...or perhaps you'd be organizing the data on the persistence side to produce that query naturally.
- henrikschroder 15y ago
  Cassandra is essentially a key-value-store. Accessing data by key is fast, everything else is slow as molasses and something you really should avoid. So to work with that, you fit your data to that model. Think of it as a RDBMS where you can only do primary key lookups and you're pretty much there.
- bkirwi 15y ago
  That's exactly correct. If you're working with a pure key-value store, the only query you have is get(), plus maybe a scan over all keys. Stores like Cassandra give a little more structure to the data (column families, supercolumns, etc.) and you can use that to make certain types of queries simpler, but there's still nothing like the algebra that comes with relational DBs. Anything other than the most primitive logic is going to have to be written in client-side code, which is why you see things like MapReduce used to filter down large data sets or other complex client-side transformations. The advantage of writing on the client is that you get to use whatever the hell programming paradigm you like: pretty much any language that can deal with a hashmap can deal with NoSQL. (That's true of relational DBs, of course, since you don't have to write complex queries; but if you're not taking advantage of the relational structure, NoSQL tends to be easier / faster.)
  - zzzeek 15y ago
    Right, this is actually what I was getting at, without even being conscious of it when I asked that. Part of the appeal of NoSQL is that programmers don't need to learn a different paradigm of thinking other than the usual imperative model. I'm not sure if I like that rationale for using NoSQL. Strikes me as being lazy up front. Or maybe you don't really need ad-hoc queries.
deleted 15y ago
[deleted]
thurn 15y ago
I don't agree at all that this phenomenon is restricted to database programming. Thinking in terms of higher-level operations is always more readable and often more efficient (the logical conclusion being things like Haksell and Prolog where there aren't really operations at all). For example, to find the common elements in two lists, you could use a for loop, or you could follow the OP's advice and use set operations to do something like: set(list1).intersection(set(list2))
- calibraxis 15y ago
  Yes, I often use clojure.set's relational operators. (select, project, join, etc.) Because it's common to deal with sequences of tuple-ish data. It's natural, once you lose this limiting notion of "objects" which aren't represented in a generic way.
henrikschroder 15y ago
Developer discovers there's a difference between the declarative paradigm and the imperative paradigm, writes blog post, news at 11. Snark aside, you can take the author's advice too far in the other direction as well. Relational algebra is all fine on paper, but relational integrity, foreign keys, strict normalization and n-way joins are horrible for performance. At the end of the day, the declarative SQL you write has to translate to a bunch of bulk reads from a hard drive somewhere, and you really need to understand execution plans as well if you want any sort of performance.
- cbsmith 15y ago
  THIS. I was going to write the same thing. It's really about declarative/functional programming vs. imperative/procedural programming. He's right though that as you work with more and more data, the impact of making the traditional imperative/procedural mistakes because a real problem.
protomyth 15y ago
SQL Databases are quite a lot like front-wheel drive cars on icy roads, you don't get a lot of warning before everything goes bad. The biggest problem is a query that finally exceeds what can be cached. You can go from less than a second to multiple minutes per query. It is much like the problem of doing high performance calculations where you want to use the CPU cache very efficiently and not have a cache miss. Worse is when some optimizers suddenly switch plans to something that is inefficient. Sybase had a really bad habit of this with big datasets. Sadly, a lot of the problem comes down to schema design. A lot of designs are just fine retrieving one item and its attributes from the database, but get into trouble when you try to retrieve a group of items with the same attribute from a database. Status reports (e.g. how many unpaid invoices do we have?) tend to sniff these problems out. I generally follow the rule "if a single attribute on an item is important to know, then knowing all of the items with that attribute is going to come up and should be designed for".

39 points by einhverfr 15y ago

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

Always test your data operations on data volumes (and user load) that mirror what you expect to see in production. This is important across the entirety of your dev stack.

mickeyp 15y ago
Indeed. If you are using Oracle you can use its Database Replay to replay the workload with the same characteristics as the production database on your test or dev environment. Pretty nifty stuff, actually.
InclinedPlane 15y ago
One of the biggest problems in database operations is that there are many potential query plans for a given query, but finding the optimal one can be difficult. More so, which query plan is optimal varies depending on table size. If you let the DB pick its own query plan all the time you can end up with inconsistent performance, often by an order of magnitude or more. However, if you turn on query plan stability (every query after the first one will have the same plan) then you can end up with a state where the query plan doesn't change as the data change, which can lead to serious performance issues as a table's size grows (if your table starts out small, the DB may choose a full table scan, which is very fast, but will bog down extremely when the table grows to thousands or millions of row). In short, performance is a hard problem, and if anything the tools for analyzing and optimizing performance for DB operations are well behind the state of the art for compiled code, for example.
- gaius 15y ago
  Oracle is pretty smart about this, if a new plan is slower than the previous one, it seamlessly flips back.
  - politician 15y ago
    JIT compilers have been doing hotspot recompilation for a while now. Is there a reason why databases haven't adopted the same techniques? Is there some barrier to caching multiple query plans optimized for different load scenarios?

dmethvin 15y ago

It's really hard to say what the performance issue was here, but my experience is that SQL servers do a really good job of handling data sets if you use them correctly. If the problem was a "cache miss" it might mean that one or more tables needs proper indexes for example. Or, if the code involves a cursor and a code loop, that is a very bad sign.

einhverfr 15y ago
The problem was that I was doing it as follows: insert/insert/update. Wash rinse repeat. The better way to do it (which solved the problem was: Insert a bunch of rows, insert a bunch of rows, update a bunch of rows, no repeating.

zzzeek 15y ago

Asking as someone who works mostly with relational DBs, where do nonrelational databases fit along this spectrum ? If you're dealing with a large dataset in Cassandra or MongoDB, do you think in terms of instructions or sets, or somewhere in between (or something else entirely) ? My understanding is that if you need to extract a dataset according to complex criteria you'd be using something like map/reduce...or perhaps you'd be organizing the data on the persistence side to produce that query naturally.

henrikschroder 15y ago
Cassandra is essentially a key-value-store. Accessing data by key is fast, everything else is slow as molasses and something you really should avoid. So to work with that, you fit your data to that model. Think of it as a RDBMS where you can only do primary key lookups and you're pretty much there.
bkirwi 15y ago
That's exactly correct. If you're working with a pure key-value store, the only query you have is get(), plus maybe a scan over all keys. Stores like Cassandra give a little more structure to the data (column families, supercolumns, etc.) and you can use that to make certain types of queries simpler, but there's still nothing like the algebra that comes with relational DBs. Anything other than the most primitive logic is going to have to be written in client-side code, which is why you see things like MapReduce used to filter down large data sets or other complex client-side transformations. The advantage of writing on the client is that you get to use whatever the hell programming paradigm you like: pretty much any language that can deal with a hashmap can deal with NoSQL. (That's true of relational DBs, of course, since you don't have to write complex queries; but if you're not taking advantage of the relational structure, NoSQL tends to be easier / faster.)
- zzzeek 15y ago
  Right, this is actually what I was getting at, without even being conscious of it when I asked that. Part of the appeal of NoSQL is that programmers don't need to learn a different paradigm of thinking other than the usual imperative model. I'm not sure if I like that rationale for using NoSQL. Strikes me as being lazy up front. Or maybe you don't really need ad-hoc queries.

deleted 15y ago

[deleted]

thurn 15y ago

I don't agree at all that this phenomenon is restricted to database programming. Thinking in terms of higher-level operations is always more readable and often more efficient (the logical conclusion being things like Haksell and Prolog where there aren't really operations at all). For example, to find the common elements in two lists, you could use a for loop, or you could follow the OP's advice and use set operations to do something like: set(list1).intersection(set(list2))

calibraxis 15y ago
Yes, I often use clojure.set's relational operators. (select, project, join, etc.) Because it's common to deal with sequences of tuple-ish data. It's natural, once you lose this limiting notion of "objects" which aren't represented in a generic way.

henrikschroder 15y ago

Developer discovers there's a difference between the declarative paradigm and the imperative paradigm, writes blog post, news at 11. Snark aside, you can take the author's advice too far in the other direction as well. Relational algebra is all fine on paper, but relational integrity, foreign keys, strict normalization and n-way joins are horrible for performance. At the end of the day, the declarative SQL you write has to translate to a bunch of bulk reads from a hard drive somewhere, and you really need to understand execution plans as well if you want any sort of performance.

cbsmith 15y ago
THIS. I was going to write the same thing. It's really about declarative/functional programming vs. imperative/procedural programming. He's right though that as you work with more and more data, the impact of making the traditional imperative/procedural mistakes because a real problem.

protomyth 15y ago

SQL Databases are quite a lot like front-wheel drive cars on icy roads, you don't get a lot of warning before everything goes bad. The biggest problem is a query that finally exceeds what can be cached. You can go from less than a second to multiple minutes per query. It is much like the problem of doing high performance calculations where you want to use the CPU cache very efficiently and not have a cache miss. Worse is when some optimizers suddenly switch plans to something that is inefficient. Sybase had a really bad habit of this with big datasets. Sadly, a lot of the problem comes down to schema design. A lot of designs are just fine retrieving one item and its attributes from the database, but get into trouble when you try to retrieve a group of items with the same attribute from a database. Status reports (e.g. how many unpaid invoices do we have?) tend to sniff these problems out. I generally follow the rule "if a single attribute on an item is important to know, then knowing all of the items with that attribute is going to come up and should be designed for".