4 ms·

I've been there with SQLAlchemy in late 2006, and I vowed never to return. Let me state this, SQLAlchemys pool/engine/SQL-Api is great, and it's got nothing wh

by pyalot2 13y ago

I've been there with SQLAlchemy in late 2006, and I vowed never to return. Let me state this, SQLAlchemys pool/engine/SQL-Api is great, and it's got nothing whatsoever todo with ORM. But SQLAlchemy does contain an ORM, and it does become unmaintainable just like every other ORM. It roughly goes like this: 1) You use the ORM 2) Things get slow 3) You write a bit of custom mappers 4) Soon all your logic can't be operated without custom mappers 5) Congratulations, you've now written all the handwired code you would have written without an ORM, plus all the ORM code on top, it's now unmaintainable, full of bugs and oversights AND slow. And that about sums up everybodies experience with ORMs unless they do trivially small things. It's at this junction people begin thinking about solving this ORM problem. And they learn it's bloody hard. Obligatory XKCD reference: There are N ORMs on the market, they all suck. Let's solve all these problem and do a new ORM to rule them all. There are now N+1 ORMs that all suck on the market.

IgorPartola 13y ago
Second this experience. A single row pk lookup using SQLAlchemy adds 30ms overhead to a query. That is huge. To avoid this, last time I touched it, I ended up just creating my own DB access library that spit out simple objects and used raw SQL underneath. I believe for any large system that is going to be the answer. Having said that, do not underestimate the number of apps doings "trivial" things. There are lots. The problem with SQLAlchemy I think is that it is actually advanced enough to have you use it right up to the point where you must rewite all your code because it suddenly is just dead slow. In contrast the Django ORM is too simplistic to get too far with it.
- pyalot2 13y ago
  Sure for simple things, of which there are many, ORMs can work sufficiently up to a point. But, the problem is that, you rarely get to go to your boss and say, well, this thing we've been using to do, you know, our data stuff, it's completely wrong. We need to throw everything away and start from scratch. The only situation in which you get to say that, is when you're explicitly make it clear that you're writing a raw prototype that'll not scale, and that nothing short of a complete rewrite is gonna be required as soon as you finish (and even then, it can turn out tricky to actually do that). Unfortunately, most people who get themselves into the ORM mess don't realize that this coming for them in the future. So they don't go their boss and make the prototype proposal. This all but cements the entire failure of the project right then.
  - collyw 13y ago
    I don't know my main experience is with the Django ORM. Others have mentioned that some ORMs seem to start from the relational model, and transform those into objects (which sounds like how the Django ORM works to me). I find this works well. It speeds up development of all the simple stuff , and by the time it gets complicated, you can drop down to SQL. I don't find the two ways of thinking incompatible, in fact the "extra" method on Djangos ORM is an intermediate where you let the ORM do its normal simple style of mapping, but allows you to add extra clauses to the SQL manually. Once you do this you see how the ORM works, and can choose quite easily when it is appropriate to use it. You loose a bit of the magic that comes with Django by the time your queries get more complex (like auto generated admin pages), but the benefits outweigh the costs immensely in my case.
    - IgorPartola 13y ago
      I am in the same boat as you currently. I work with Django almost exclusively these days and use the ORM in every project. The difference is that I am working with mostly normalized databases and datasets in the 100's of megabytes. This is a great place to use an ORM. You get all the simplicity and quick prototyping and pay very few penalties. When you have a slow query you can usually address it by adding some select_related() or prefetch_related() and you are done. What this thread is talking about is when you have datasets that are several gigabytes in size and up, when you have many dozens of tables, all interrelated and when you need to squeeze as much performance out of your hardware as you can. When your code gets complex enough where you cannot just insert an object, but have to do all manner of verification through half the DB first. Or where you need to operate on 100,000 rows in an intelligent manner very fast (think using stored procedures and complex query parameters). This is a place that you might end up and if your entire codebase depends on an ORM, you are often out of luck. In the long run, I think there several things to do: (1) know SQL well, (2) use an ORM when you know the project will be small, and (3) anticipate that if you are growing and using an ORM, you will need a re-write.
      - collyw 13y ago
        I agree, and my application is pretty much what you describe in your last paragraph. 80% of the queries are simple and use the ORM directly. A few need an extra clause for stuff that isn't handled by the ORM, and I have a couple that need to be high performance (and are abstract enough that they don't fit well with an ORM), which are done via the raw SQL.

by pyalot2 13y ago

IgorPartola 13y ago

Second this experience. A single row pk lookup using SQLAlchemy adds 30ms overhead to a query. That is huge. To avoid this, last time I touched it, I ended up just creating my own DB access library that spit out simple objects and used raw SQL underneath. I believe for any large system that is going to be the answer. Having said that, do not underestimate the number of apps doings "trivial" things. There are lots. The problem with SQLAlchemy I think is that it is actually advanced enough to have you use it right up to the point where you must rewite all your code because it suddenly is just dead slow. In contrast the Django ORM is too simplistic to get too far with it.

pyalot2 13y ago
Sure for simple things, of which there are many, ORMs can work sufficiently up to a point. But, the problem is that, you rarely get to go to your boss and say, well, this thing we've been using to do, you know, our data stuff, it's completely wrong. We need to throw everything away and start from scratch. The only situation in which you get to say that, is when you're explicitly make it clear that you're writing a raw prototype that'll not scale, and that nothing short of a complete rewrite is gonna be required as soon as you finish (and even then, it can turn out tricky to actually do that). Unfortunately, most people who get themselves into the ORM mess don't realize that this coming for them in the future. So they don't go their boss and make the prototype proposal. This all but cements the entire failure of the project right then.
- collyw 13y ago
  I don't know my main experience is with the Django ORM. Others have mentioned that some ORMs seem to start from the relational model, and transform those into objects (which sounds like how the Django ORM works to me). I find this works well. It speeds up development of all the simple stuff , and by the time it gets complicated, you can drop down to SQL. I don't find the two ways of thinking incompatible, in fact the "extra" method on Djangos ORM is an intermediate where you let the ORM do its normal simple style of mapping, but allows you to add extra clauses to the SQL manually. Once you do this you see how the ORM works, and can choose quite easily when it is appropriate to use it. You loose a bit of the magic that comes with Django by the time your queries get more complex (like auto generated admin pages), but the benefits outweigh the costs immensely in my case.
  - IgorPartola 13y ago
    I am in the same boat as you currently. I work with Django almost exclusively these days and use the ORM in every project. The difference is that I am working with mostly normalized databases and datasets in the 100's of megabytes. This is a great place to use an ORM. You get all the simplicity and quick prototyping and pay very few penalties. When you have a slow query you can usually address it by adding some select_related() or prefetch_related() and you are done. What this thread is talking about is when you have datasets that are several gigabytes in size and up, when you have many dozens of tables, all interrelated and when you need to squeeze as much performance out of your hardware as you can. When your code gets complex enough where you cannot just insert an object, but have to do all manner of verification through half the DB first. Or where you need to operate on 100,000 rows in an intelligent manner very fast (think using stored procedures and complex query parameters). This is a place that you might end up and if your entire codebase depends on an ORM, you are often out of luck. In the long run, I think there several things to do: (1) know SQL well, (2) use an ORM when you know the project will be small, and (3) anticipate that if you are growing and using an ORM, you will need a re-write.
    - collyw 13y ago
      I agree, and my application is pretty much what you describe in your last paragraph. 80% of the queries are simple and use the ORM directly. A few need an extra clause for stuff that isn't handled by the ORM, and I have a couple that need to be high performance (and are abstract enough that they don't fit well with an ORM), which are done via the raw SQL.