Hacker News Reader

Comment by ramraj07 | Hacker News Reader

Don't forget that you're paying a huge price using the sqlalchemy orm - https://docs.sqlalchemy.org/en/13/faq/performance.html

If I know an endpoint is going to be hit hard, I forgo trying to use the ORM (except to maybe get the table name from the model obj so some soul can trace it's usage here in the future) and directly do an engine.execute(<raw query>). Makes a huge difference. Next optimization I do is create stored procedures on the database. Only then I start thinking about changing the framework itself.

For folks like me who want to get prototypes off the ground in hours, flask and fastapi are godsend, and if that means I have to worry about serving thousands of requests a second soon thats a happy problem for sure.

View on HN

You can also use SQLAlchemy Core, which is an intermediate between the full-blown ORM and running actual strings of SQL. I've had a great experience with Core - I can easily have it output essentially the exact SQL I'd write by hand, but I get many benefits (like the ability to compose queries) that are nicer than dealing with raw SQL.

Definitely agree, I'm happy to write raw SQL, but SQLAlchemy Core is even better than that because of composability.

This is what I like most about SQLAlchemy, I can use the ORM most of the time, but in cases where it needs optimizations or aggregation queries I can drop down or refactor to core, while still using the same model/table definitions, ability to combine or filter queries dynamically and leverage IDE features like refactoring.

I'll happily forget that because it's such a small microscopic price that it's moot. You're way better off optimizing the actual query being made, which SQLAlchemy is great at because it doesn't hide the SQL from you. Don't use engine.execute(<raw query>), use SQLAlchemy Core if your endpoint is getting hammered.

To be clear, this is FUD. If you know how to make SQA emit the right SQL, the performance is basically the same as psycopg2 + your custom code, usually better. I've written many high volume SQA services and never once saw SQA per se as the bottleneck.

ORMs aren't inherently that heavyweight, as a Java developer I don't have performance concerns about hibernate.

That sounds to me like a Python problem and a "this specific ORM isn't performant" problem, not ORMs being bad as a whole. Python has never been the fastest language (it's far slower than, say, Java) and the GIL really prevents applications from scaling well without multiple instances.

And if you really want a "just load the data for me and do nothing else that incurs a performance hit" approach then you can use stateless objects and the ORM truly becomes just a wrapper around the DB to load and transform the data into an object for you and/or do a raw, whole-object update back to the DB.

Totally agree with you, only want to add that unfortunately the team doesn’t really know the given ORM they use, and I have seen some utterly stupid uses of eg. Hibernate (like eager fetching basically everything there is for queries where it is not required, or simply not knowing anything about the boundaries where an object is “attached” or not). Which one might argue that it is a defect in the tool, but I doubt you would blame an airplane for crashing when the “pilot” is not trained to drive it.

It's not just sqlalchemy but yes it is definitely a python problem. Problem or feature is up for discussion of course. The ability to mutate basically anything is powerful and you gain some tangible benifits from it.

For me, FastAPI is the sweet spot between performance and quick development.

Reply on news.ycombinator.com