Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

How not to structure database-backed web apps: performance bugs in the wild

blog.acolyer.org

Post reply on HN
Print view View original

61–70 of 319 posts

Re: How not to structure database-backed web apps: performance bugs in the wild

#61

Post by threeseed »

TekMol wrote: post #56
dagw wrote: post #47

Earlier quoted context omitted.

That has nothing to do with ORMs. I've seen plenty of people write code like: SELECT * FROM DATA for each r in result if r.x > 12 do_something(r.y) Exactly the same thing without any ORM. If anything ORMs should improve performance for novices since it makes it a lot easier for people to writer better queries that run on the database.

The ORM makes it more likely to do stuff in the application instead of the DB because many queries are hard or impossible to represent in an ORM fashion. Additionally, the ORM makes it much harder to see what is going on under the hood. For example you don't know if a value of an object is stored in the main table or lazy loaded from a related table. So you have no clue if echo "$user.name lives in $user.city" result…

> ORM makes it much harder to see what is going on under the hood

I've never in the last 20+ years seen an ORM that doesn't allow you to log the SQL queries with a single configuration option.

Re: How not to structure database-backed web apps: performance bugs in the wild

#62

Post by weavie »

patkai wrote: post #33
TekMol wrote: post #21

Give me an O! Give me an R! Give me an M! What does that spell? SLOW PERFORMANCE! Todays programmers dont understand data. They understand frameworks. To find the nr of all cars that are out of insurance they write: 10 Nr=0 20 Hey framework, give me all cars! Framework: Ok, here are 8001093 business objects representing all the cars in our DB. Each has all the attributes the car has. Color, mileage etc. 30 Thanks! 40…

I love to write SQL queries and even use them extensively in my code. However, almost everyone I talk to resists this, and warns me that "one day you will regret..." It gives me an uneasy feeling that I actually might, though it hasn't happened yet.

I think one of the problems with SQL queries is that as far as your application is concerned they are just strings. There's no typing information or even syntax checking. Also if you do something like 'select * from' then there the results returned aren't deterministic.

If you scatter these throughout your code and then the database schema changes, you have a hell of a refactoring job to make sure everything still works. One advantage of an Orm is that if you keep your objects in line with your database the generated sql will stay correct.

Personally, I am more than happy to take that hit. I think it is a price well worth paying in order to have optimised queries that do exactly what I what them to do. To make it easier for myself though I will always try to keep my queries in one place in the code. Then all my code needs to know is it is getting clients_older_than(32) or whatever..

Re: How not to structure database-backed web apps: performance bugs in the wild

#63

Post by skunkworker »

weavie wrote: post #49
idle_zealot wrote: post #38

Earlier quoted context omitted.

So long as you're using prepared statements, you should be okay.

What difference does using prepared statements make?

You can avoid SQL injection. It makes it harder to shoot yourself in the foot, though still possible to inject if your prepared statement includes a string interpolated variable.

Re: How not to structure database-backed web apps: performance bugs in the wild

#64

Post by guiriduro »

Wouldn't it just be better to invert the relation (sic) between App-ORM and DB, and have everyone better understand what data modelling and a DBMS is, then write SQL and some reverse-ORM to expose App services in SQL?

E.g. EXPOSE SERVICE(REST, GraphQL) BillOfMaterials (VARCHAR arg) AS (SELECT ... WHERE ... = arg etc.)

Re: How not to structure database-backed web apps: performance bugs in the wild

#65

Post by olavk »

mnm1 wrote: post #60
superasn wrote: post #28

This may be a little OT (OT because the points raised in the study are totally valid and mine is just a comment) but for small companies and solo developers ORM or whatever that gets the job done quickly is the way to go. Most sites and web apps never even break a 100k/day hit mark for which I believe inefficiency may not be the biggest issue. But wasting a month tryig to write native Sql queries can hurt your projec…

Even if you get 1 hit / day and that takes a long time to return, the ORM has failed your one customer. Inefficiency exists even at small scales with ORMs. If your developers don't know how to write SQL, let them learn. Or fire them if they won't.

> Inefficiency exists even at small scales with ORMs

ORMs are not inherently inefficient. And in my time I have seen plenty of n+1 style queries written with raw SQL.

Re: How not to structure database-backed web apps: performance bugs in the wild

#66

Post by weavie »

skunkworker wrote: post #63
weavie wrote: post #49

Earlier quoted context omitted.

What difference does using prepared statements make?

You can avoid SQL injection. It makes it harder to shoot yourself in the foot, though still possible to inject if your prepared statement includes a string interpolated variable.

Yes, good point.

Re: How not to structure database-backed web apps: performance bugs in the wild

#67

Post by ben-schaaf »

threeseed wrote: post #61
TekMol wrote: post #56

Earlier quoted context omitted.

The ORM makes it more likely to do stuff in the application instead of the DB because many queries are hard or impossible to represent in an ORM fashion. Additionally, the ORM makes it much harder to see what is going on under the hood. For example you don't know if a value of an object is stored in the main table or lazy loaded from a related table. So you have no clue if echo "$user.name lives in $user.city" result…

> ORM makes it much harder to see what is going on under the hood I've never in the last 20+ years seen an ORM that doesn't allow you to log the SQL queries with a single configuration option.

Heck, I'm disappointed in any ORM that doesn't log queries by default in development mode!

Re: How not to structure database-backed web apps: performance bugs in the wild

#68

Post by olavk »

ianamartin wrote: post #41
czechdeveloper wrote: post #16

Earlier quoted context omitted.

From my experience, most of my waiting time is IO, not CPU load. Even simple stuff, such as SELECT COUNT(*) to display total count in grid on big table can take seconds. It does not have to be complicated.

You have a problem. And it’s probably related to your db architecture, which was probably designed by devs with ORMs.

What kind of database architecture could cause SELECT * to be slow?

Re: How not to structure database-backed web apps: performance bugs in the wild

#69

Post by adrianmsmith »

weavie wrote: post #62
patkai wrote: post #33

Earlier quoted context omitted.

I love to write SQL queries and even use them extensively in my code. However, almost everyone I talk to resists this, and warns me that "one day you will regret..." It gives me an uneasy feeling that I actually might, though it hasn't happened yet.

I think one of the problems with SQL queries is that as far as your application is concerned they are just strings. There's no typing information or even syntax checking. Also if you do something like 'select * from' then there the results returned aren't deterministic. If you scatter these throughout your code and then the database schema changes, you have a hell of a refactoring job to make sure everything still wo…

You can get the best of both worlds by using e.g. jOOQ in Java (allows you to write e.g. db.select(MY_TABLE.MY_COL).from(MY_TABLE) where those values are generated from the database therefore they exist and are of the right type.

It maps 1:1 to the SQL statement that gets executed so there's no magic e.g. extra n+1 queries being introduced without you noticing.

But if you change your schema, re-generate, and immediate compile errors showing you where you're referencing something that's now been deleted, so it's not fragile like putting SQL in a String in your code (where if the schema changes, the compiler can't help you)

Post reply on HN