6 ms·

If you are building anything more complex than a blog site and expect to take a decent amount of traffic, to the point that you may in fact care about optimizin

by n_are_q 15y ago

If you are building anything more complex than a blog site and expect to take a decent amount of traffic, to the point that you may in fact care about optimizing at all, going with an ORM that writes sql for you is a really really bad idea. I really don't understand the fascination with ORMs today. Some sort of sql-to-object translation layer is no doubt a great thing, but any time you write "sql" in a non-sql language like python or ruby you are letting go of any ability to optimize your queries. For reasonably complicated and trafficked websites that's a disaster simply waiting to happen. This isn't just blind speculation on my part, I've heard a great many stories where very significant resources had to be dedicated to removing ORM from the architecture, and the twitter example should familiar to most. I would go so far as to say that sql writing ORMs are a deeply misguided engineering idea in and of itself, not just badly implemented in its current incarnations. You can't possibly write data access logic entirely in your front end and expect some system to magically create and query a data store for you in the best or even close to the best way. I think the real reason people use ORMs is because they don't have someone at the company that can actually competently operate a sql database, and at any company of a decent size traffic-wise that's simply a fatal mistake. Unless you are going 100% nosql, at which point this discussion is irrelevant.

nettdata 15y ago
I disagree. ORM's aren't a problem at all as long as you have the ability to override problematic queries with named queries, etc. ORM's can provide very real advantages when it comes to caching, development time, etc., as long as you review what the ORM is doing and notice when it's doing it wrong. I've just spent 2 years architecting a high transaction global video game system using an ORM, and it worked well. In our case, the ORM provided acceptable SQL for about 85% of the queries, and we overrode the rest. The ability to quickly and easily allow the developers to write their own SQL, to be reviewed later by a DBA, was a life saver. Combine that with our stress and load testing, it was easy to see where the hot spots were and deal with them effectively. The problem comes from people who rely on the ORM to do everything for them without truly understanding how it works. ORM's, like anything, are a tool, and there is a time and place for them.
- n_are_q 15y ago
  Wrapping both caching logic and database access in an ORM like system is no doubt the right thing to do. Letting front end developers write queries to be converted by an orm and reviewed by a DBA later - in my opinion that's not the most efficient method of development. I probably would have invested in an extra DB person or two to help write the data access logic. But hey, I can't argue with results - if it worked for you that's great. But as a general statement I think that sort development methodology is highly conducive to errors and systematic problems that would not become evident until later, and at that point take a great deal of effort to fix.
  - nettdata 15y ago
    The two big systems I architected where I made the decision to go with ORM's were the online EA Sports system (all EA Sports games on all platforms, currently running in a 7 node Oracle cluster), and most recently, the Need For Speed World Online system. We launched the EA Sports system with Madden, and went from 50 to 11 million users hitting the DB in less than an hour. Then we rolled out the other EA Sports games. Needless to say, both systems were slightly bigger than a simple blogging site. In both cases, we had a large number of smart developers who we empowered with the use of an ORM; they understood the domain model, and they didn't have to worry about waiting for a "DB type" to write stored procedures, or develop a data model, etc. As a matter of fact, in both cases, I was the only DBA on the project, and it was a predominately part-time role. We'd meet, ensure we were all on the same page with the object/data model, and then they'd go and build it. The developers were able to immediately build and run and test and integrate something that was functional and operational, when they needed it. This was HUGE, and something that most people don't properly appreciate. Timelines were already insane enough as it was, the last thing we needed to do was artificially constrain ourselves by waiting for other (db) devs before work could go on. Especially when requirements had the potential to change from one day to the next. In both situations, we took advantage of very, very sophisticated testing procedures that would happen nightly, both functional and stress/load, and it pointed us at the bottlenecks of each nightly build that would require tuning and investigation. We intentionally set up our testing to be able to monitor and test the effectiveness of the ORM, and to point it out when it didn't work efficiently. The devs would do the majority of the heavy lifting with the initial data model, and the results would be tested, reviewed, and then modified if required. The performance modifications were not a lot of effort to fix, either. Usually it was a very slight data model change, or using a named query to take advantage of a database-specific features. And CLOBS. Every database seems to handle them differently, so we had to hack some solutions. Having done large scale database development for almost 25 years, using the classic stored procedure approach and the ORM approach, I'll say again that ORM's are a great solution for certain projects with the right staff, and aren't a crutch or some lazy choice if used properly.
snorkel 15y ago
I completely agree. I didn't even realize the "N+1 Selects Problem" was a problem because it should be referred to as "My Training Wheels Fell Off and Now My Bike Falls Over When I Sit On It".
dasil003 15y ago
> any time you write "sql" in a non-sql language like python or ruby you are letting go of any ability to optimize your queries. No you're not. Look at ActiveRecord, it lets you drop to any level of SQL optimization you need. In ActiveRecord 3 with ARel queries are composable, allowing lazy loading and the breaking of queries into appropriate locations according to your code architecture. I can't speak to other ORMs, maybe they really are as bad as your opinion would indicate, but I suspect what you're really complaining about people who don't know how SQL works being enabled to write horrible data persistence code by ORMs with a pretty facade. That's a legitimate problem, but the fact that a tool can be abused is not an argument against the tool itself. We'll never build anything great if we are driven primarily by what the ignorant will do with it, after all, every single person on the planet is ignorant of most things, our tool development should be driven by what they enable experts to do.
- n_are_q 15y ago
  Things like lazy loading is a red flag to me that you are doing something wrong, so if your framework allows you to do that that's not necessarily something to brag about :). Random IO that is triggered by merely accessing a property without knowledge of the programmer is not the best approach if you want to scale, you are better off doing deliberate fetches as a result of previously fetched data. If you are breaking and composing queries, how are they broken and compose by the orm, as joins or as sub queries? If as joins does your orm know the best columns to join on? You could replace everything with named sql functions (dropping to the lowest level of optimization as you mention above), but at that point what is your orm really doing for you. Anyway, sorry, I'm not sold :). Maybe if you effectively replicated the database engine in your front end framework I would come closer to being sold, but even then you don't have the same rapid in memory access to statistics about tables to make the right optimization decisions, etc..
  - joevandyk 15y ago
    Lazy loading in ActiveRecord works like: users = User.where(:age => 10) # no rows fetched # Once you access something on users, then the query happens users.each do |user| # something end It's not really that "magic", it's useful. ActiveRecord uses joins (mostly). If you are using a "non-standard" table schema, you can tell ActiveRecord what column to use on joins.
ssmoot 15y ago
ORMs are fun to write. ;-) I don't think they're necessarily misguided. DataMapper made efforts to circumvent the N+1 problem, in most cases probably pretty effectively. Partial Updates are also pretty easy. Slamming every field into every INSERT/UPDATE is obviously a bad idea. I think the missing sauce for ORMs is funding. Getting the basics together takes time and money, and it's hard to pull off in your free time. On the other hand, having written many an ORM, I think there's still plenty of room to advance the state of the art. One of the biggest untapped (AFAIK) opportunities is using Statistics for query tuning. It's the life-blood of databases, but statistics are noticeably lacking in ORMs. Even simple counters could allow you to tune lazy-loads, JOINs, pre-fetches, etc on-the-fly.
ma2rten 15y ago
Replace ORM in your argument with C and SQL with assembler. Just like high level languages it's an abstraction layer and it can really help you put stuff like caching, escaping (think SQL injections) into once place. Also it's much easier to change a function name in your abstraction layer, then to change your database schema.
contextfree 15y ago
The advantage of using LINQ database querying in C#, and it's a big one in my experience, is that your queries are actually typechecked by the compiler like any other code, making it a lot easier to refactor. (In the context of Python/Ruby which don't even have typecheckers I have no idea what the draw is). The disadvantage is that due to some organizational dysfunction at MSFT there's still no really satisfactory ORM infrastructure surrounding the query engines. (as for your "misguided engineering idea in itself" claim, I don't really see how it's fundamentally different from writing SQL in the first place to be translated by the database into query execution plans, vs. writing the query execution plans directly).
- n_are_q 15y ago
  The difference at a high level is that sql has a syntax and set of capabilities that is quite unique, and every single database vendor has its own extensions or differences driven by their particular approach. To really replicate all of this in code you would have to go beyond the basic data structures and syntax of that programming language. And at that point might as well just have sql. It's a paradigm and an approach expressed through its own syntax, you can't easily copy all of it in a totally different programming language.. As for checking for type safety, I think frameworks that do sql-to-object mapping (with type safety), and also handle cache for you, are a very useful thing. Making raw calls on database connections is definitely too far "in the other direction" :).
  - contextfree 15y ago
    On the syntactic level SQL is just a poorly designed language. LINQ query expressions actually do a better job of expressing the semantics of the SQL-like set/collection operations, in a compositional manner. It's definitely true though that SQL databases currently have a lot of capabilities (like, errmm, DML) that at least the Microsoft ORMs don't support other than by dropping down to SQL. I don't think this is a problem with the LINQ IQueryable paradigm, though, but just a problem with the Microsoft ORMs being incomplete. I don't have much experience with ORMs or mapping frameworks other than LINQ-based ones, but it seems like it would be pretty difficult to typecheck queries expressed as SQL strings, at least dynamic ones, at compile time. Do the frameworks you mention typecheck the actual query itself at compile time, or do they just check at runtime that the data returned from the query matches what you want?
gnaritas 15y ago
> If you are building anything more complex than a blog site and expect to take a decent amount of traffic, to the point that you may in fact care about optimizing at all, going with an ORM that writes sql for you is a really really bad idea. Then explain the massive success of Rails. Quite simply, you are wrong.
halo 15y ago
If you went back a few decades then the exact same argument would have been made by replacing 'ORM' with high-level languages and 'SQL' for assembly. The trade-offs are similar.

by n_are_q 15y ago

nettdata 15y ago

I disagree. ORM's aren't a problem at all as long as you have the ability to override problematic queries with named queries, etc. ORM's can provide very real advantages when it comes to caching, development time, etc., as long as you review what the ORM is doing and notice when it's doing it wrong. I've just spent 2 years architecting a high transaction global video game system using an ORM, and it worked well. In our case, the ORM provided acceptable SQL for about 85% of the queries, and we overrode the rest. The ability to quickly and easily allow the developers to write their own SQL, to be reviewed later by a DBA, was a life saver. Combine that with our stress and load testing, it was easy to see where the hot spots were and deal with them effectively. The problem comes from people who rely on the ORM to do everything for them without truly understanding how it works. ORM's, like anything, are a tool, and there is a time and place for them.

n_are_q 15y ago
Wrapping both caching logic and database access in an ORM like system is no doubt the right thing to do. Letting front end developers write queries to be converted by an orm and reviewed by a DBA later - in my opinion that's not the most efficient method of development. I probably would have invested in an extra DB person or two to help write the data access logic. But hey, I can't argue with results - if it worked for you that's great. But as a general statement I think that sort development methodology is highly conducive to errors and systematic problems that would not become evident until later, and at that point take a great deal of effort to fix.
- nettdata 15y ago
  The two big systems I architected where I made the decision to go with ORM's were the online EA Sports system (all EA Sports games on all platforms, currently running in a 7 node Oracle cluster), and most recently, the Need For Speed World Online system. We launched the EA Sports system with Madden, and went from 50 to 11 million users hitting the DB in less than an hour. Then we rolled out the other EA Sports games. Needless to say, both systems were slightly bigger than a simple blogging site. In both cases, we had a large number of smart developers who we empowered with the use of an ORM; they understood the domain model, and they didn't have to worry about waiting for a "DB type" to write stored procedures, or develop a data model, etc. As a matter of fact, in both cases, I was the only DBA on the project, and it was a predominately part-time role. We'd meet, ensure we were all on the same page with the object/data model, and then they'd go and build it. The developers were able to immediately build and run and test and integrate something that was functional and operational, when they needed it. This was HUGE, and something that most people don't properly appreciate. Timelines were already insane enough as it was, the last thing we needed to do was artificially constrain ourselves by waiting for other (db) devs before work could go on. Especially when requirements had the potential to change from one day to the next. In both situations, we took advantage of very, very sophisticated testing procedures that would happen nightly, both functional and stress/load, and it pointed us at the bottlenecks of each nightly build that would require tuning and investigation. We intentionally set up our testing to be able to monitor and test the effectiveness of the ORM, and to point it out when it didn't work efficiently. The devs would do the majority of the heavy lifting with the initial data model, and the results would be tested, reviewed, and then modified if required. The performance modifications were not a lot of effort to fix, either. Usually it was a very slight data model change, or using a named query to take advantage of a database-specific features. And CLOBS. Every database seems to handle them differently, so we had to hack some solutions. Having done large scale database development for almost 25 years, using the classic stored procedure approach and the ORM approach, I'll say again that ORM's are a great solution for certain projects with the right staff, and aren't a crutch or some lazy choice if used properly.

snorkel 15y ago

I completely agree. I didn't even realize the "N+1 Selects Problem" was a problem because it should be referred to as "My Training Wheels Fell Off and Now My Bike Falls Over When I Sit On It".

dasil003 15y ago

> any time you write "sql" in a non-sql language like python or ruby you are letting go of any ability to optimize your queries. No you're not. Look at ActiveRecord, it lets you drop to any level of SQL optimization you need. In ActiveRecord 3 with ARel queries are composable, allowing lazy loading and the breaking of queries into appropriate locations according to your code architecture. I can't speak to other ORMs, maybe they really are as bad as your opinion would indicate, but I suspect what you're really complaining about people who don't know how SQL works being enabled to write horrible data persistence code by ORMs with a pretty facade. That's a legitimate problem, but the fact that a tool can be abused is not an argument against the tool itself. We'll never build anything great if we are driven primarily by what the ignorant will do with it, after all, every single person on the planet is ignorant of most things, our tool development should be driven by what they enable experts to do.

n_are_q 15y ago
Things like lazy loading is a red flag to me that you are doing something wrong, so if your framework allows you to do that that's not necessarily something to brag about :). Random IO that is triggered by merely accessing a property without knowledge of the programmer is not the best approach if you want to scale, you are better off doing deliberate fetches as a result of previously fetched data. If you are breaking and composing queries, how are they broken and compose by the orm, as joins or as sub queries? If as joins does your orm know the best columns to join on? You could replace everything with named sql functions (dropping to the lowest level of optimization as you mention above), but at that point what is your orm really doing for you. Anyway, sorry, I'm not sold :). Maybe if you effectively replicated the database engine in your front end framework I would come closer to being sold, but even then you don't have the same rapid in memory access to statistics about tables to make the right optimization decisions, etc..
- joevandyk 15y ago
  Lazy loading in ActiveRecord works like: users = User.where(:age => 10) # no rows fetched # Once you access something on users, then the query happens users.each do |user| # something end It's not really that "magic", it's useful. ActiveRecord uses joins (mostly). If you are using a "non-standard" table schema, you can tell ActiveRecord what column to use on joins.

ssmoot 15y ago

ORMs are fun to write. ;-) I don't think they're necessarily misguided. DataMapper made efforts to circumvent the N+1 problem, in most cases probably pretty effectively. Partial Updates are also pretty easy. Slamming every field into every INSERT/UPDATE is obviously a bad idea. I think the missing sauce for ORMs is funding. Getting the basics together takes time and money, and it's hard to pull off in your free time. On the other hand, having written many an ORM, I think there's still plenty of room to advance the state of the art. One of the biggest untapped (AFAIK) opportunities is using Statistics for query tuning. It's the life-blood of databases, but statistics are noticeably lacking in ORMs. Even simple counters could allow you to tune lazy-loads, JOINs, pre-fetches, etc on-the-fly.

ma2rten 15y ago

Replace ORM in your argument with C and SQL with assembler. Just like high level languages it's an abstraction layer and it can really help you put stuff like caching, escaping (think SQL injections) into once place. Also it's much easier to change a function name in your abstraction layer, then to change your database schema.

contextfree 15y ago

The advantage of using LINQ database querying in C#, and it's a big one in my experience, is that your queries are actually typechecked by the compiler like any other code, making it a lot easier to refactor. (In the context of Python/Ruby which don't even have typecheckers I have no idea what the draw is). The disadvantage is that due to some organizational dysfunction at MSFT there's still no really satisfactory ORM infrastructure surrounding the query engines. (as for your "misguided engineering idea in itself" claim, I don't really see how it's fundamentally different from writing SQL in the first place to be translated by the database into query execution plans, vs. writing the query execution plans directly).

n_are_q 15y ago
The difference at a high level is that sql has a syntax and set of capabilities that is quite unique, and every single database vendor has its own extensions or differences driven by their particular approach. To really replicate all of this in code you would have to go beyond the basic data structures and syntax of that programming language. And at that point might as well just have sql. It's a paradigm and an approach expressed through its own syntax, you can't easily copy all of it in a totally different programming language.. As for checking for type safety, I think frameworks that do sql-to-object mapping (with type safety), and also handle cache for you, are a very useful thing. Making raw calls on database connections is definitely too far "in the other direction" :).
- contextfree 15y ago
  On the syntactic level SQL is just a poorly designed language. LINQ query expressions actually do a better job of expressing the semantics of the SQL-like set/collection operations, in a compositional manner. It's definitely true though that SQL databases currently have a lot of capabilities (like, errmm, DML) that at least the Microsoft ORMs don't support other than by dropping down to SQL. I don't think this is a problem with the LINQ IQueryable paradigm, though, but just a problem with the Microsoft ORMs being incomplete. I don't have much experience with ORMs or mapping frameworks other than LINQ-based ones, but it seems like it would be pretty difficult to typecheck queries expressed as SQL strings, at least dynamic ones, at compile time. Do the frameworks you mention typecheck the actual query itself at compile time, or do they just check at runtime that the data returned from the query matches what you want?

gnaritas 15y ago

> If you are building anything more complex than a blog site and expect to take a decent amount of traffic, to the point that you may in fact care about optimizing at all, going with an ORM that writes sql for you is a really really bad idea. Then explain the massive success of Rails. Quite simply, you are wrong.

halo 15y ago

If you went back a few decades then the exact same argument would have been made by replacing 'ORM' with high-level languages and 'SQL' for assembly. The trade-offs are similar.