7 ms·

For simple "web apps", typically its a poor understanding of SQL that is the bottle neck. Since most web frameworks include an ORM, many developers don't even k

by esaym 7y ago

For simple "web apps", typically its a poor understanding of SQL that is the bottle neck. Since most web frameworks include an ORM, many developers don't even know much SQL any more and treat the database as a simple 'object' that you can call methods on and iterate through. Case in point: you're building a dash board for a web based store to show details about the 5 most recent orders. But each order references 5 other items (as a simple example). And each item references an SKU# that can come from 5 manufacturers. So an optimized query set from an ORM will end up querying the DB like this: First it selects the 5 rows from the order table, but since you left out any join clauses, it then runs 5 more selects on each of the rows returned from the order table to pull the related rows from the items table. So we are now up to 6 queries. But each row of the item table references 5 manufacturers (again, as a simple schema example). So each of the 'item' 'objects' (5 per order) has to then make a query to fetch the sku manufacturer. So that is 25 more select statement queries on top of the original 6. So using the ORM properly and building out the right join clauses, you would have made 1 query. Instead you made 31. Now lets say that DB is not on the same machine, but a different machine on the network. Throw in 2ms - 10ms latency per query and now you are looking at 62 - 310ms of latency to fetch your data. And that is just for 5 'rows' of data. Do the math for a more realistic dataset, 500-1000 rows or so. Now you're looking at minutes of latency for your data to come from the DB. The worse case I've seen recently was this exact case. To generate an inventory page to show an html table with about 300 rows, the DB was being queried over 300,000 times with a page load of about 7 minutes (and getting longer everyday). I added the proper join clauses to the ORM and one query was issued which would return the result set in about 700ms which put the page load time to about 1 second. A 1 second page load imo is still not very good. But it seemed to impress everyone that with 1 hour of work, a page load went from from 7 minutes to 1 second.... For what ever reason, most people don't understand this anymore. Instead of learning SQL, the line of thought is to upgrade the machine, move to a 'no-sql' DB, introduce some caching layer, and many number of other ideas. But "lets make sure we are querying the DB instead of iterating through it" is never discussed.

iaabtpbtpnn 7y ago
You've done a great job of explaining the type of DB interactions that most frequently cause performance issues at my workplace. I don't understand why developers are so unwilling to just learn SQL.
- jrockway 7y ago
  I am inclined to agree. I have searched for the perfect ORM and have decided that objects and relations do not map. And it's really not hard to write: db.Query("select foo, bar from baz") var baz Baz row.Scan(&baz.foo, &baz.bar) to get an object.
  - julvo 7y ago
    The problem I have with having queries like this in my code is that your schema and your code can get out of sync and these are errors only occuring at runtime. Therefore, looking for a safer solution, I've also looked at various options in my last Go project, and ended up with SQLBoiler. It supports fetching related documents in a single query and seems to be quite performant.
    - jasonkester 7y ago
      Stored procedures. Blow them away and recreate them all from source after you run your schema and data migration scripts. Then you'll know at build time whether your queries still match the schema. It helps to also not be afraid of the star when writing queries. That can keep you safe from missing new columns. You just need to take a bit of care and think about what you're doing.
    - gregw2 7y ago
      To keep code+schema in sync, try using liquibase or flyway or similar tools to manage all SQL DDL, end ensure your build+deploy process always runs your liquibase/flyway scripts.
    - jrockway 7y ago
      I apply migrations when the app starts, and have a policy that the code version n-1 should be compatible with schema version n, so that releases can be rolled back safely. (How do you handle replicas? The database gets globally locked and the first replica does the migrations. Yup, you have downtime. Don't change your SQL schema if you want high availability.) I don't think that an ORM saves you from having to have a versioning policy, schema migrations, and tests for that. If your code expects a data structure that looks like map[string]string{"foo":"bar"} but the database column is now called "quux", you're not going to find what the code is looking for at runtime. The difference is you get an empty string instead of an explicit error. Ultimately I find it very annoying. When I was at Google I used spanner, and everything in the database was just a protocol message. Want to rename something? Feel free. Only the code cares, the database still uses the tag number. Want to add a field? Code compiled against an older version of the protocol message will just silently ignore it. In the end, I think bolting on relational semantics to an object database is a lot easier than bolting on object semantics to a relational database. But to do that, you have to undo 50 years of thinking about database storage.
tracker1 7y ago
If only more people used simple object structures to data mapping. Working on an overly normalized database, with in-database logging on all access via stored procedures for all access, and hitting bottlenecks on a few dozen users. A "well structured" database can lead to performance issues. Every join and sub-query has a cost and they add up. The funny thing is that on this project, I largely stepped back from anything to do with the Database because the target was only around 100 users or so on a deployment that could largely be dealt with by faster hardware. Now, stuck trying to scale to more users than that and stuck with layers of procedures/functions that don't perform well at all. I wanted a few loosely coupled tables with some destructured (json) data in them. Wouldn't be having half the issues today. Also, not a fan of ORMs... it's usually easier to do simpler mapping directly or a simpler data mapper that isn't a more typical full on ORM.
- tmm84 7y ago
  I haven't much experience with stored procedures but I have seen views cause load on a DB. Almost every app I have worked on had a database that were several tables for a single object and several for the where clause. As good as the table layout seemed to be in terms of logic it just led to slow queries like you said joins and sub-queries come at a cost. Then again, I have seen clients balk at the idea of fixing the database and insist on a more unreadable yet performant query. But its their time and money so I don't fight back. Sometimes, a really good DBA is what a project needs because so few programmers are good at performant design.
- jrumbut 7y ago
  Granted you can get a performance boost from denormalizing, and even maintain integrity, I still find the denormalization I want today won't be the denormalization I want tomorrow. Moving from denormalization to denormalization can be hard, and you end up with a sui generis application, versus a normalized database where so much has been written about how to deal with problems up to a reasonably large scale, although the document store style does seem to be reasonably popular so maybe I'm missing something.
  - tracker1 7y ago
vonseel 7y ago
This reminds me of Django’s select_related. You can get pretty far with select-related and prefetch_related alone, but sometimes there’s a need to break out SQL in order to get past some kind of performance barrier. I started my career in Django without any SQL background and then moved to a financial company where everything was done in MSSQL. If I never have to write or read a SQL query again, I would consider that a good thing. Although I had a few fun learning experiences.

by esaym 7y ago

iaabtpbtpnn 7y ago

You've done a great job of explaining the type of DB interactions that most frequently cause performance issues at my workplace. I don't understand why developers are so unwilling to just learn SQL.

jrockway 7y ago
I am inclined to agree. I have searched for the perfect ORM and have decided that objects and relations do not map. And it's really not hard to write: db.Query("select foo, bar from baz") var baz Baz row.Scan(&baz.foo, &baz.bar) to get an object.
- julvo 7y ago
  The problem I have with having queries like this in my code is that your schema and your code can get out of sync and these are errors only occuring at runtime. Therefore, looking for a safer solution, I've also looked at various options in my last Go project, and ended up with SQLBoiler. It supports fetching related documents in a single query and seems to be quite performant.
  - jasonkester 7y ago
    Stored procedures. Blow them away and recreate them all from source after you run your schema and data migration scripts. Then you'll know at build time whether your queries still match the schema. It helps to also not be afraid of the star when writing queries. That can keep you safe from missing new columns. You just need to take a bit of care and think about what you're doing.
  - gregw2 7y ago
    To keep code+schema in sync, try using liquibase or flyway or similar tools to manage all SQL DDL, end ensure your build+deploy process always runs your liquibase/flyway scripts.
  - jrockway 7y ago
    I apply migrations when the app starts, and have a policy that the code version n-1 should be compatible with schema version n, so that releases can be rolled back safely. (How do you handle replicas? The database gets globally locked and the first replica does the migrations. Yup, you have downtime. Don't change your SQL schema if you want high availability.) I don't think that an ORM saves you from having to have a versioning policy, schema migrations, and tests for that. If your code expects a data structure that looks like map[string]string{"foo":"bar"} but the database column is now called "quux", you're not going to find what the code is looking for at runtime. The difference is you get an empty string instead of an explicit error. Ultimately I find it very annoying. When I was at Google I used spanner, and everything in the database was just a protocol message. Want to rename something? Feel free. Only the code cares, the database still uses the tag number. Want to add a field? Code compiled against an older version of the protocol message will just silently ignore it. In the end, I think bolting on relational semantics to an object database is a lot easier than bolting on object semantics to a relational database. But to do that, you have to undo 50 years of thinking about database storage.

tracker1 7y ago

If only more people used simple object structures to data mapping. Working on an overly normalized database, with in-database logging on all access via stored procedures for all access, and hitting bottlenecks on a few dozen users. A "well structured" database can lead to performance issues. Every join and sub-query has a cost and they add up. The funny thing is that on this project, I largely stepped back from anything to do with the Database because the target was only around 100 users or so on a deployment that could largely be dealt with by faster hardware. Now, stuck trying to scale to more users than that and stuck with layers of procedures/functions that don't perform well at all. I wanted a few loosely coupled tables with some destructured (json) data in them. Wouldn't be having half the issues today. Also, not a fan of ORMs... it's usually easier to do simpler mapping directly or a simpler data mapper that isn't a more typical full on ORM.

tmm84 7y ago
I haven't much experience with stored procedures but I have seen views cause load on a DB. Almost every app I have worked on had a database that were several tables for a single object and several for the where clause. As good as the table layout seemed to be in terms of logic it just led to slow queries like you said joins and sub-queries come at a cost. Then again, I have seen clients balk at the idea of fixing the database and insist on a more unreadable yet performant query. But its their time and money so I don't fight back. Sometimes, a really good DBA is what a project needs because so few programmers are good at performant design.
jrumbut 7y ago
Granted you can get a performance boost from denormalizing, and even maintain integrity, I still find the denormalization I want today won't be the denormalization I want tomorrow. Moving from denormalization to denormalization can be hard, and you end up with a sui generis application, versus a normalized database where so much has been written about how to deal with problems up to a reasonably large scale, although the document store style does seem to be reasonably popular so maybe I'm missing something.
- tracker1 7y ago

vonseel 7y ago

This reminds me of Django’s select_related. You can get pretty far with select-related and prefetch_related alone, but sometimes there’s a need to break out SQL in order to get past some kind of performance barrier. I started my career in Django without any SQL background and then moved to a financial company where everything was done in MSSQL. If I never have to write or read a SQL query again, I would consider that a good thing. Although I had a few fun learning experiences.