20 ms·

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

486 points by godelmachine 8y ago

deleted 8y ago
[deleted]
yread 8y ago
This is cool. Are there more papers as practically useful for a (web) developer as this one?
mozumder 8y ago
Its always amazing to see web pages take 2-4 seconds of back-end processing. On a modern CPU, that's about 10 billion instructions. 10 billion instructions to send a few kilobytes of data. There's such a waste in back-end server design. If you're measuring response times are in seconds, and not in microseconds, you're doing something seriously wrong.
- crocal 8y ago
  Too much CPU and too much available memory will get you there. When in time of abundance, we humans are not good at optimizing.
  - sheepz 8y ago
    It's more of an economics issue. It does not make sense to optimize, because bandwidth and CPU power are so abundant. Optimizing will take time away from adding new features.
    - crocal 8y ago
      Except you can’t add so many features because you have sucked up all resources already ^^
    - theandrewbailey 8y ago
      Failure to optimize in these situations creates a performance bug.
- purerandomness 8y ago
  And that's exactly how client-side rendering became popular again. "It's taking too long on the server, let's offload everything to every single client out there." Now we have two problems.
- menzoic 8y ago
  Modern frontends take even longer to completely load.
- mr__y 8y ago
  on the other hand there are savings in development time, which might or might not be more expensive than cpu cycles in question. Especially for smaller scale projects. I'm not saying that 4 seconds to load a page is something to be happy about, but sometimes it's cheaper to "waste" cpu cycles than to spend more time developing faster solution.
  - willvarfar 8y ago
    Although its really difficult to fathom how loading a webpage takes billions of cycles anyhow. It never used to. Remember the Internet in 2000? Pages are getting slower despite broadband speeds going through the roof and processors getting faster and there being so much more RAM etc.
    - mcbits 8y ago
      Yep, I remember trying to optimize my 1-pixel spacer GIF from 53 bytes down to 47 bytes to lighten up the load of a 200KB page of random interwoven table, font, and center tags that took 30-60 seconds to download.
  - radicalbyte 8y ago
    It only saves development time for the first version in my experience unless it's being used by an highly capable team. The months lost to the clueless developers massaging their horrible ORM code dominates in time. In the long term properly factored code wins; and lightweight ORMs have a place. Heavy ORMs (Hibernate, old Entity Framework) should only be used if you absolutely must have specific features and are incapable of building that into a more logical place in your code. Use a light weight ORM for simple CRUD actions (one with a small dependency graph). For performance critical create/update operations use stored procedures (just be careful with complex business logic; if you implement it, remember to test it). Use light weight ORM for mapping efficient SQL views to api/frontend. Use a workflow/async/job processing for anything which has to be long running or crosses system bounds (think credit card processing).
    - jlebrech 8y ago
- czechdeveloper 8y ago
  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.
  - rypskar 8y ago
    >Even simple stuff, such as SELECT COUNT(*) edit seems like my old performance book was outdated or wrong :(
    - ianamartin 8y ago
      Nope. Not even close.
    - czechdeveloper 8y ago
      SELECT COUNT(*) should be the one which is resolved by index/metadata
  - ianamartin 8y ago
    You have a problem. And it’s probably related to your db architecture, which was probably designed by devs with ORMs.
    - olavk 8y ago
      What kind of database architecture could cause SELECT * to be slow?
      - kyberias 8y ago
        Any. "Architecture" has nothing to do with that. SELECT COUNT(*) is usually "slow".
      - deleted 8y ago
        [deleted]
    - theandrewbailey 8y ago
- collyw 8y ago
  In my experience this seems to be a problem of people using frameworks without getting to understand them in any depth (probably because they are off chasing the next hyped up thing). Django is easy to get started with. Its also easy to produce a shitload of queries unless you spend some time getting to understand the ORM and how to optimize queries with it. In my previous work we were generating over a 1000 queries for the front page (we were showing maybe 20-25 products with different options). Everything was done in nested loops, with new queries to the database each iteration. The kid who had written that was apparently building his own framework when I looked up his webpage. Learn to use Django properly before you go building your own crap versions please.
crocal 8y ago
... Which begs the question: what good is an ORM if it does not prevent by design such issues? Here, we are essentially saying users of ORM must also have in their mind the SQL version. Or call an expert after the mess is done :/...
- deleted 8y ago
  [deleted]
- willvarfar 8y ago
  I'm not very sure that Jooq and SQLAlchemy and other 'not quite ORMs' really help that much either. I think, as the article suggests, that 'opaque' is as much the problem as the classic Object-Relational impedance mismatch, but I would posit that the opaqueness isn't just that the programmer can't _see_ what happens, but that they also don't _care_. So I'd put my faith in tools instead. If programmers don't want to think about this, let the tools try instead. The static analyser was interesting. Although static analysers have shallow comprehension, they were able to identify anti-patterns that were common enough to make it a useful linter. And of course their classic-mistakes approach can be applied to other languages too, even if each language and ORM needs its own corpus. I sincerely hope that JetBrains and other IDEs bring this kind of analysis into their IDEs. I write a lot of non-Ruby code, and the JetBrain IDEs do keep suggesting nice 'code simplification' and 'generate boilerplate' checkers as I type. But while they check my regex for well-formed-ness and colour my SQL but don't have any kind of meta analysis and comprehension. Missing opportunity. You could take this further for normal non-DB code too - they could warn me when I have inefficient O(n) search in a loop and so on.
  - arkh 8y ago
    > You could take this further for normal non-DB code too - they could warn me when I have inefficient O(n) search in a loop and so on. For phpstorm you have the EA extended plugins which gives lot of hints like that. I'm sure you could find the same kind of plugins or write one backed by a static analysis tool for other languages.
- shangxiao 8y ago
siscia 8y ago
I never understood why I don't enjoy working with ORM but reading this article make it clearer for me. They blend the distinction between working in memory vs accessing the db. From one side it is very convenient, however I really feel that such performance sensitive operation should be carefully considered and that most SQL should be written by hand.
- t0mbstone 8y ago
  Or you could just spend 15 minutes learning how to avoid N+1 queries by using the includes() function, and some of the other super obvious performance optimization patterns that every junior Rails dev should know...
- olavk 8y ago
  Writing SQL by hand does not solve typical performance problems like n+1 queries. If on the other hand you know enough to avoid n+1 queries, then you can also avoid them when using an ORM, and save a lot of work. If you like writing SQL by hand, by all means do so, but you will not automatically get better performance by handwritten SQL as compared to ORM generated SQL.
  - marcus_holmes 8y ago
    No, it's not automatically more performant; it's more that you can optimise hand-written SQL for your specific schema and use case. You can't do that with ORM-written SQL.
    - olavk 8y ago
      Depends on what kind of optimizations, and on the ORM I guess. The major problems described in the article, like n+1 queries and filtering on the client rather than in the database seem to be easier to fix in an ORM than in hand-written SQL with boilerplate wrappers. (Of course they are also easier to introduce in an ORM if you don't know what you are doing, but such is the curse of powerful abstractions.) Even things like query hints can be easily applied in the ORM's I know. It is kind of hacky since it breaks abstraction layers - but so is query hints in SQL. But of course there can be some special cases where you just have to drop down to raw SQL for some reason. All ORM's I know allow this.
slifin 8y ago
This should be translated for other back end languages, maybe followed by how to use a relational database as a relational database
TekMol 8y ago
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 Foreach Cars as Car 50 If Car->insurance_end_date < FancyDateLib.now() Nr++ I see variants of this everywhere. And the performance impact is just untoppable. It's often several million times slower then a simple sql query. But beefy hardware with lots of ram and memory takes care of it. 'Our software is enterprise grade, so of course it cannot run on commodity hardware'.
- sfvisser 8y ago
  People do this because it’s easier, takes less time, and is within the paradigm their head is already in all day. We should fix our abstractions instead of telling people they don’t understand data. The latter is fine every so often, but doesn’t scale and isn’t even true most of the time.
  - ianamartin 8y ago
    Or maybe we should just nut up and tell people they don’t understand data?
    - girvo 8y ago
      Why not both? Truly, we should work to have better abstractions. We shouldn’t glorify making our own lives more difficult. And we shouldn’t glorify ignorance either! It’s not an either/or dichotomy here
- patkai 8y ago
  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.
latch 8y ago
I've worked on moderately busy backend platforms (~10K-20k rps handled on a ~4 e5-2650 and aiming for 5ms 95p response times). It greatly depends on what you're doing, but for the majority of systems which are read heavy (and that most certainly includes "dynamic" sites like Amazon or Wikipedia), I hold to two major beliefs: 1 - Have very long TTLs on your internal cache servers with a way to proactively purge (message queues) and refresh in the background. Caching shouldn't be a compromise between freshness and performance. Have both! 2 - Generate message/payloads/views asynchronously in background workers and have your synchronous path as streamlined as possible (select 1 column from 1 table with indexes filters). Avoid serialization. Precaculate and denormalize. Any personalization or truly dynamic content can be done: 1 - By having the client make separate requests for that data 2 - Merging the data into the payload with some composition. 3 - Glueing bytes together (easier/safer with protocol buffers than json) Do things asynchronously. Use message queues / streams. Beyond that, GC becomes noticeable. For example, Go's net/http used to (might still) allocate much more than other 3rd party options.
- iKSv2 8y ago
  Do this (point 1) properly and ~80% of your "speed" problems are solved, for a simple web-app-db tiered app.
- blattimwind 8y ago
  0 - Caching antipattern 101: key = calculate_cache_key() if not cache.has(key): data = expensive_calculation() cache.store(key, data) else: data = cache.get(key)
  - memset 8y ago
    This is interesting because this is generally how I implement caching! What would pseudocode look like for a non-antipattern?
    - brightball 8y ago
      Depends on what you are doing. To frame it another way, what happens if 100 requests come in at the same time for that expensive value when it isn’t in the cache yet? The expensive calculation will be run 100 times at the same time. Ideally, you’d rather refresh the cache value in the background once and never allow duplicate requests for it from the web. If you’re running a language that makes it easier to deduplicate requests for certain data, the original approach will last longer. The CacheEx library in Elixir, for example, will only run the expensive calculation once, set the cache and then send the value back to everything that requested it while it was loading.
mping 8y ago
My 2 cents are: if you don't know what queries your orm API is generating, you shouldn't use one. I mean, sometimes there are bugs here and there, but people should know the methods they call. It's the equivalent of making a rest call and complaining about latency. Yeah, it's a method, why is it taking so long?
- arwhatever 8y ago
  That's an excellent point. I'm very comfortable with my ORM of choice as well as its performance, because I've spent time running SQL Profiler while stepping through my own code to learn what my ORM does, and when it does it.
superasn 8y ago
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 project a lot more.
- mnm1 8y ago
  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.
  - olavk 8y ago
    > 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.
    - mnm1 8y ago
      Lazy loading often fetches too little and eager loading too much. Most orms select all columns in all tables regardless of need. The query translation and hydration overhead of transforming the data into objects even in a fast language are always going to be problems, it's just a matter of how big, over a system without the orm. Since orms are superfluous, every line of code in them adds unnecessary overhead. They are indeed inherently inefficient by definition.
      - olavk 8y ago
        > The query translation and hydration overhead of transforming the data into objects even in a fast language are always going to be problems No they are not. Have you measured this in a real-world application? This overhead is negligible compared to the cost of the query itself. And without an ORM you still have to load the data into some kind of objects or data structures before you pass it to presentation, you will just have to write the code yourself. But yeah, lazy loading in a loop will kill performance. So don't do that.
ianamartin 8y ago
The only big problem with ORMs is when developers are allowed to use them to construct database schemata. As long as you have a not-crazy person in charge of the initial design, you can allow—and even encourage—developers to make versioned changes to the database. If you let us start from scratch with ORMs, it will suck for everyone. There’s a fundamental mismatch between what developers think of as objects and how relational designers think of tables. ORMs can smooth that over. But nothinking can undo the wrong-headed-ness of a developer thinking of a relational dB as an object store.
ssijak 8y ago
ORM for saving objects and very simple queries. Writing SQL or using something like JOOQ in Java to write type-safe SQL for everything else.
- collyw 8y ago
  I disagree. I have used Django extensively. The ORM handles raw SQL queries, but which is great when you need something beyond the capabilities of ORM, but you loose a lot as well when you go that route. Pagination and sorting are pretty easy additions when you retrieve data using the ORM in the standard way, and now you need to add extra code to handle those specifically. I don't think you can use the Django admin with raw SQL (I never tried, but it doens't make too much sense, as it basically generates a set of views per table). Model methods don't make sense using SQL queries. You should be able to write SQL if you want to be able to use the ORM effectively and I am certainly glad I knew it well before started using Django's ORM.
  - ssijak 8y ago
    What makes you think Django ORM is good ORM? What is bad about adding "extra code" if that code is doing a useful and correct thing? And I wonder why people find Django Admin useful for anything than simple CRUD apps. Writing code so it can go along with django admin is a strange way of thinking, there should be other priorities first.
mnm1 8y ago
ORMs are really only useful for throwaway projects and beginners. I have yet to see one without serious downsides in both performance and speed of development, something that they are touted to improve but actually make worse. The ORM I'm stuck with now (Doctrine2) adds a 10x overhead to queries. For the most part, we don't even bother optimizing queries in such situations because why waste time on something that could at most improve performance by 1/10th? The real fix is to get rid of the ORM. That requires a rewrite of two apps that have been in development for four years each. I can't think of a worse architectural decision for business (CRUD) software than using an ORM. Irreversible and forever fucked.
- thraxil 8y ago
  Don't know much about Doctrine2, but that sounds pretty terrible. I'm sorry you are forced to work with something so inefficient. I've been building apps with Django and Django's ORM for the last 10 years and found essentially zero overhead in most cases. Every once in a while there's a slow page, I open up the debug toolbar which shows me every SQL query that was used to generate the page in a nice waterfall diagram, I see something a little odd and change the order of some filters or add a `select_related`, or `prefetch_related` or discover that some third party library is making a dumb call (unavoidable problem of using third party libraries on any platform) and find a workaround for that. Every once in a blue moon, it appears easier to write a raw SQL statement than figure out what needs to be done to get the ORM to generate it, so I do that. Of course, Django's ORM makes it stupid easy to use a raw SQL statement: https://docs.djangoproject.com/en/2.0/topics/db/sql/ https://docs.djangoproject.com/en/2.0/topics/db/sql/ I've worked with a few other ORMs in Python and other languages over the years as well (in Go, Erlang, Elixir, Clojure, nodejs) and never really encountered any where the ORM had a noticeable performance overhead (dominated by the network latency back and forth from the database) and I've yet to work with one that I couldn't just do a raw query when needed. The closest I've seen is when I started using GORM in Go, I found it running slowly and discovered that it automatically adds a "soft delete" functionality so every query gets an additional "and not is_deleted" clause added. Disabled that feature and it was fine. That was my own fault though for starting to build before I finished reading the documentation, as it was pretty clearly explained in a later section. (OK, also the ORMs I was using in Perl and Java back in the late 90's/early 00's were also pretty terrible, but those were prehistoric times.) You always have to be careful of N+1 problems, but that's not just an ORM thing. You run into that as soon as you have any abstraction in your code. Once you have refactored to `get_list_of_items(some criteria)` and `get_item_details(item)` functions/methods/whatever, whether it is using an ORM underneath or raw SQL, developers working on the app have to know that they can't loop over the results of the first and call the second on each of them. Tradeoffs between reusability and performance are nothing new though and not at all specific to web applications or ORMs.
guiriduro 8y ago
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.)
- girvo 8y ago
  Yep! See here[0] and here[1] for that idea done with Postgres [0] https://postgrest.org/en/v5.0/ https://postgrest.org/en/v5.0/ [1] https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/
jlebrech 8y ago
what people need isn't an ORM but a DSL, if only there was a Domain Specific Language for Querying the database in a Structured manner.
- deleted 8y ago
  [deleted]
knesek 8y ago
When I was inexperienced I feared ORMs because of the negative performance impacts I've read they could have. I constantly worried about what would happen if the amount of data increased and I hit ORM induced problem that I could not resolve without major rewrite of data access layer. However, whenever I've actually hit those problems in production, I found the similar thing the authors of the article did - ORM induced performance problems can be fixed by 1-5 lines of code changes, if you knew where the actual problem was. In fact, I learnt that there's no "ORM induced performance problems", there are only problems induced by lack of understanding of how ORM works. As for the knowing where the performance problem is, I find that skill (that I think should be basic) is in fact very elusive in the engineers I encounter. When I interview people, even in what would be upper intermediate to senior level in terms of years of experience, alarmingly small number have ever done or could do (or even would do) performance profiling like described in the article. Yet they do describe how they changed this ORM for that ORM, this DB for that DB, this language for that language, in the name of higher performance. I've seen or heard of: - Teams spending weeks exchanging SQL DB for No SQL DB because of unsolvable performance problem. When hitting the same problem with NoSQL DB, they find that addition of a simple index is solution in both cases - Teams spending weeks exchanging Hibernate for OpenJPA in a complex application, because of performance, without doing any performance analysis, because they've read article that says Hibernate is slow - Teams choosing complex architectures they don't really understand, for performance reasons, without being able to articulate performance requirements of the system they're building These days, whenever someone mentions performance as a reason for anything, I judge their competence based on their response to the question "And how are you measuring and monitoring it?"
- ravenstine 8y ago
  > Teams spending weeks exchanging SQL DB for No SQL DB because of unsolvable performance problem. When hitting the same problem with NoSQL DB, they find that addition of a simple index is solution in both cases I don't get the idea that one has to pick either SQL or NoSQL, but a lot of people seem to think this way. Why not use both? The SQL portion can more or less be treated as a rich index of relationships, and the NoSQL portion can handle performance when it makes more sense for data to be embedded in parent documents.
qaq 8y ago
The problem with ORMs is they have to cater to the lowest common feature-set of the supported RDBMS.
- guipsp 8y ago
  This is not the case for pretty much any modern ORM
  - qaq 8y ago
    Sure it is show me the ORM that will support 2p commit, window functions, CTEs, JSON operators in PG (just a random set of features)
    - YorickPeterse 8y ago
      http://sequel.jeremyevans.net/ http://sequel.jeremyevans.net/
    - bpicolo 8y ago
      SQLAlchemy supports all of the above - in general it's really terrific about being extensible enough to support the corner cases.
      - qaq 8y ago
        It does look very nice and does support all of the above. I stand corrected (not a Python dev. :))
mukundasai111 8y ago
I figure you can have a touch of segment An, a touch of segment B. My home city is Mumbai (in fact, the rural city of Thane, north of Mumbai) a standout amongst the most thick expansive urban areas on the planet. Open transportation in Mumbai, as far as recurrence, limit, dependability, and cost is now superior to anything any American city (I'm including NYC that). It could be better; for example the city is served by standard check prepares however the metro railroad (whatever measure a tram is) that goes underground is still under development. In spite of all that, and regardless of the way that the tallest working in the city is a unimportant 88 stories[1] the foundation is stressing under the weight each day. All the steady employments (government, tech, managing an account) are moved in only maybe a couple parts of the city (South Mumbai and the Andheri-Bandra-Dadar triangle, perhaps Powai; my geology may be somewhat obsolete in light of the fact that I haven't lived there for a long time). What's more, land in those spots is cosmically expensive[2] (here's a non-extravagance 3-bed condo for USD 1.3m in South Mumbai). That implies everybody whose isn't living with guardians who purchased decades back in those spots has 3-hour round trek drives each day (I was one of those individuals). Building taller wouldn't densify the city since rich individuals will probably purchase that new lodging and they have littler families by and large. Enhancing open transportation won't change laws of separation and movement - like I said it's as of now very great. It would be better for individuals in North Mumbai if there were more occupations closer to them so they wouldn't need to drive so far consistently. Rural, auto driven, sprawl isn't perfect for city development, however nor is the grouping of occupations in a single place. http://sobhalake-garden.in/price.html http://sobhalake-garden.in/price.html
unilynx 8y ago
Pretty cool to read. We built a (currently proprietary) CMS with our own scripting language, and instead of going the ORM way, we merged basic SQL into the language itself. We did that mostly to eliminate sending raw strings to databases (and all the injection risks and complexity that comes with it) but it does allow a few extra optimisations because the compiler can look at both the query and the language using it. So if I do our equivalent of the ORM API Misuse case: IF(RecordExists(SELECT * FROM schema.variants WHERE track_inventory = 0)) { ... } (RecordExists is a function that only checks whether the query returned something, and has been marked that way) the compiler will already reduce this to: IF(RecordExists(SELECT FROM schema.variants WHERE track_inventory = 0 LIMIT 1)) { ... } And likewise a function that selects all columns from a database and returns only one field, has the select reduced to only selecting that one column. The drawback, of course, is that any SQL features of the underlying database not exposed by the scripting language, are unreachable unless you fallback to sending raw query strings again.
- psadauskas 8y ago
  Of course it depends on the exact circumstances, but your query would probably be faster as `SELECT COUNT(*)` than the `LIMIT 1` you optimize to now. In fact, that’s one of the specific optimizations mentioned in the article.
  - adamzochowski 8y ago
    count(*) ends up counting all records, reading one first row is much much faster than finding and counting all. https://wiki.postgresql.org/wiki/Slow_Counting https://wiki.postgresql.org/wiki/Slow_Counting
  - unilynx 8y ago
    That depends a lot on the implementation of the database and the communication protocol. In our case, that made no difference
lukaseder 8y ago
> “compared to other popular ORM frameworks such as Django and Hibernate, Rails has 2x more applications on GitHub with 400 or more stars than Django and Hibernate combined.” As if the whole world was hosted on GitHub...
acutesoftware 8y ago
I need a very good reason before using any external library in an attempt to keep the total code base as clean as possible. It is just too easy to be rushed and bring in a heap of code, so I prefer to use SQL instead of ORM's. All access to the database is done in a single module and they are wrapped in functions like below def get_table_as_list(user_id, cols, tbl, where_clause, params_as_list, conn_str, order_by="1", maxrows='2000'): """ This should be the ONLY place that selects from the database """ db = get_db_conn(conn_str) cur = db.cursor() where_clause += ' AND user_id = %s' sql = "SELECT " + cols + " FROM " + tbl + " WHERE " + where_clause + " ORDER BY " + order_by + " LIMIT " + maxrows params_as_list.append(str(user_id)) cur.execute(sql, params_as_list) res = list(cur.fetchall()) cur.close() db.close() return res The database is designed and built first, then in the application the definitions are done like below all_tables = [ {'tbl':'as_note', 'cols':['id','title','pinned', 'important','content','folder'], 'col_types':['id','Text','Checkbox','Checkbox', 'Note','Text'], }, {'tbl':'as_task', 'cols':['id','Title','Pinned', 'Important','Notes','folder','Done'], 'col_types':['id','Text','Checkbox','Checkbox','Note','Text','Checkbox'], }] So far it is working well, and it is very simple to add new tables to the schema and have them working in the application.
- blattimwind 8y ago
  It's nice to see that people are still able to write 2002-era PHP in Python today.
- tasuki 8y ago
  What exactly is the problem you're attempting to solve with this? Bonus questions: What about maintenance or admin queries which aren't tied to a specific user_id? What about sql injection?
  - acutesoftware 8y ago
    > What exactly is the problem you're attempting to solve with this? Keeping all database access in one place to avoid having selects around the codebase. > What about maintenance or admin queries which aren't tied to a specific user_id? This is the web interface for users, all admin stuff is done elsewhere > What about sql injection? The selects are passed as parametised queries, so the where clause would be 'title = %s AND folder = %s'
Rafert 8y ago
Interestingly their code checker is just a bunch of regular expressions: https://github.com/hyperloop-rails/static-checker https://github.com/hyperloop-rails/static-checker - I would've expected custom Rubocop rules. Rubocop already knows about `where.first? => find_by` for exmple: https://github.com/rubocop-hq/rubocop/blob/master/lib/rubocop/cop/rails/find_by.rb https://github.com/rubocop-hq/rubocop/blob/master/lib/ruboco...
- Balf 8y ago
  They suggested naïve sequential string replacing for html templating, so I would absolutely expect a bunch of regular expressions.
thermodynthrway 8y ago
It's a bit high level to mention this, but it doesn't have the one query problem I see constantly. Paging using offset and limit. I swear every app I've worked on uses it somewhere. And it's horrifically inefficient 90% of the time.
- vorpalhex 8y ago
  Can you elaborate on the issues with that? I've used that pattern and haven't found any major woes (yet), but also don't usually paginate complicated queries.
  - bpicolo 8y ago
    In postgres and mysql, at least, the database has to re-scan data every time you run a limit/offset query query. It gets progressively slower as your offset increases. The efficient way to handle it is to set a lower limit on the pkid (or other atomically increasing row) of the last record fetched, and fetch in ascending order e.g. SELECT * from my_table where id > (last_row_id_seen) ORDER BY id asc limit 20; Then you have an indexed jump to the correct rows to return. It's pretty straightforward to abstract a batched database iterator with this pattern for use application-wide (usually can be done in ~20-50 loc), but no ORM that I'm aware of supports this pattern natively. Do note that, by extension, it's impossible to efficiently batch iterate over tables that don't have a unique, orderable key in postgres and mysql.
    - kraftman 8y ago
      What if you need to order by something other than id?
      - thermodynthrway 8y ago
        Then, generally, give the user a bunch of filtering options and limit page depth to less than ~1000 entries
  - thermodynthrway 8y ago
projectramo 8y ago
The real joy of this article was the following: Some academics (Yang, Subramanian, Lu, Yan, Cheung) were able to produce massive improvements in about a dozen large, mature, battle tested open source projects using just a few lines of code. This should give hope to all those tepidly trying to get into open source. Just go and take a look at the dozens of open source projects in Django or whatever and you could improve the performance by keeping an eye on the ORM. Better yet you might find another thing that makes them even better with ease. Of course, I should add, I think the really clever thing the academics did is to come up with this random link clicking program to time the worst load times of the projects. That whole setup was gold.
- Cthulhu_ 8y ago
  What's intimidating to me is that to make a change like that, you first need to get the code, get it and the tests running, and actually understand what the code does - and whether your fix has the equivalent result or is subtly broken. They didn't just change a single line in an application, they wrote a huge benchmark suite and dug through miles of code to find issues like this. I've no clue how much time they spent on it, it must've been months.
  - projectramo 8y ago
    You think so? Maybe but the ORM lines should be pretty insulated. If you return the same results and only speed them up you should be okay. To take an example, replacing any? with exists? doesn't seem to require testing everywhere in the project. I imagine that the average fix had lines like that. I also suspect that the fixes were pareto distributed -- that a few fixes brought most of the benefit.
  - mklepaczewski 8y ago
    This is not necessarily true. You would be surprised how many absolutely trivial performance issues can be found in almost every project. Sometimes it's really just about moving computation of a constant value out of a 'for' loop.
    - 8y ago
brightball 8y ago
I experienced fixing most of thes first hand. I think 90% of the issues come from developers who haven’t worked directly with SQL enough to understand what the ORM is doing. The experience of building apps with hand coded SQL, while not good for a project, is an excellent teacher.
ahallock 8y ago
The problem with ORMs is that they make DB calls seem cheap, and when your application is young and you don't have a lot of rows, everything works wonderfully. Then all the N+1 problems start adding up and the SELECT COUNT(*) performance issues. Since the ORM code is baked into the models, your code is littered with these calls -- in the service layer, views, background workers. That's why I really like Promises/Futures in Javascript. You know exactly when you're executing a DB operation and you have to think about the implications more. It's not just a simple object accessor.
krainboltgreene 8y ago
There are some really glaring issues in this article. For example, the author suggests that each -> update is worse than update_all, and while the performance may be better it's comparing apples to oranges! update_all, in ActiveRecord, bypasses business logic, bypasses updating the updated_at, bypasses validation and more. It's not the same thing at all. There is no difference between where.first and find_by, that is just a bikeshed. Not every map(column) can be turned into pluck(column) if there are business logic wrappers around that column.
mendelk 8y ago
Having worked many years both with ORMs and with "old-school" devs slinging raw SQL strings and everything in between, I've come to the conclusion that "ORMs don't kill [performance], people do". :)
pjungwir 8y ago
> Inefficient Rendering (IR). This one was a surprise to me He's talking about the common Rails pattern of rendering a small Haml/ERB partial over & over in a loop. I've noticed big perf hits from this before, but never completely understood why. Rendering the exact same result directly in the loop body, without calling a second partial, gives a big speedup. There is an extensive discussion here: https://softwareengineering.stackexchange.com/questions/157108/rails-does-using-partials-slow-views-rendering https://softwareengineering.stackexchange.com/questions/1571... I would love to have some better understanding around this, although it sounds like no one has a clear idea of the cause. In development, I've noticed that Rails re-reads the partial file off disk every iteration of the loop. I don't know if that happens in production too, but if so it would explain a lot.
NightlyDev 8y ago
How the hell can these pages have common load times of ~500ms? This must be the worst case pages, right? Hundreds of milliseconds is very slow if network latency is out of the picture.
vladmihalcea 8y ago
Most ORM-related problems are related to lack of knowledge of how the tool works. However, if you understand how databases work, how to tune the driver and how to get the ORM tool to generate the same queries you'd otherwise write yourself, then you are fine. For more details, check out these [14 High-Performance Persistence Tips](https://vladmihalcea.com/14-high-performance-java-persistence-tips/ https://vladmihalcea.com/14-high-performance-java-persistenc...).
syastrov 8y ago
I’ve experienced a lot of n+1 queries problems as causes for bad performance. Often times this was a result of wrapping the ORM in abstraction layers (for business logic and fears of being “locked in” to the ORM). We rewrote that part of the application using a different ORM (in Python). Making use of a tool that could help find these problems automatically helped greatly and we didn’t have a single performance problem when we went into production the rewritten service. This module can detect the n+1 queries problem automatically in Python ORMs: https://github.com/jmcarp/nplusone https://github.com/jmcarp/nplusone Looking forward to seeing more automated tools like this in the Python/Django world.
- itsderek23 8y ago
  Scout also detects these for Django, ordering by the most performing N+1s: http://blog.scoutapp.com/articles/2018/04/30/finding-and-fixing-n-1s-in-django-apps http://blog.scoutapp.com/articles/2018/04/30/finding-and-fix...
  - sut-123 8y ago
    And bullet is used for Rails to detect N + 1 queries. https://github.com/flyerhzm/bullet https://github.com/flyerhzm/bullet
rmc 8y ago
One of the example Rails applications they use is the code which powers the OpenStreetMap website. They populated their install by randomly filling in fields on the website. Which doesn't include any map editing! For OSM they suggest changing how the diary feature operates, which is a tiny, almost irrelevant part of the OSM website software stack. The OSM database has millions of geographic objects, and they talk about the diary system on the website. > For example, when we profile the latest version of Openstreetmap, a collaborative editable map system, we find that a lot of time is spent on generating a location_name string for every diary based on the diary’s longitude, latitude, and language properties stored in the diary_entry table The paper claims to have filed bug reports, and has URLs. But those links don't exist. Paper: https://hyperloop-rails.github.io/220-HowNotStructure.pdf https://hyperloop-rails.github.io/220-HowNotStructure.pdf openstreetmap-website: https://github.com/openstreetmap/openstreetmap-website/ https://github.com/openstreetmap/openstreetmap-website/ Claimed Issues submitted: https://github.com/hyperloop-rails/issues-summary https://github.com/hyperloop-rails/issues-summary
- lelelelemon 8y ago
  Thanks for looking into this - I am one of the authors of the study. We have updated the submitted issues link to https://github.com/hyperloop-rails/study-replication/tree/master/issues-summary https://github.com/hyperloop-rails/study-replication/tree/ma.... Let us know if you have any further questions.
deleted 8y ago
[deleted]

486 points by godelmachine 8y ago

deleted 8y ago

[deleted]

yread 8y ago

This is cool. Are there more papers as practically useful for a (web) developer as this one?

mozumder 8y ago

Its always amazing to see web pages take 2-4 seconds of back-end processing. On a modern CPU, that's about 10 billion instructions. 10 billion instructions to send a few kilobytes of data. There's such a waste in back-end server design. If you're measuring response times are in seconds, and not in microseconds, you're doing something seriously wrong.

crocal 8y ago
Too much CPU and too much available memory will get you there. When in time of abundance, we humans are not good at optimizing.
- sheepz 8y ago
  It's more of an economics issue. It does not make sense to optimize, because bandwidth and CPU power are so abundant. Optimizing will take time away from adding new features.
  - crocal 8y ago
    Except you can’t add so many features because you have sucked up all resources already ^^
  - theandrewbailey 8y ago
    Failure to optimize in these situations creates a performance bug.
purerandomness 8y ago
And that's exactly how client-side rendering became popular again. "It's taking too long on the server, let's offload everything to every single client out there." Now we have two problems.
menzoic 8y ago
Modern frontends take even longer to completely load.
mr__y 8y ago
on the other hand there are savings in development time, which might or might not be more expensive than cpu cycles in question. Especially for smaller scale projects. I'm not saying that 4 seconds to load a page is something to be happy about, but sometimes it's cheaper to "waste" cpu cycles than to spend more time developing faster solution.
- willvarfar 8y ago
  Although its really difficult to fathom how loading a webpage takes billions of cycles anyhow. It never used to. Remember the Internet in 2000? Pages are getting slower despite broadband speeds going through the roof and processors getting faster and there being so much more RAM etc.
  - mcbits 8y ago
    Yep, I remember trying to optimize my 1-pixel spacer GIF from 53 bytes down to 47 bytes to lighten up the load of a 200KB page of random interwoven table, font, and center tags that took 30-60 seconds to download.
- radicalbyte 8y ago
  It only saves development time for the first version in my experience unless it's being used by an highly capable team. The months lost to the clueless developers massaging their horrible ORM code dominates in time. In the long term properly factored code wins; and lightweight ORMs have a place. Heavy ORMs (Hibernate, old Entity Framework) should only be used if you absolutely must have specific features and are incapable of building that into a more logical place in your code. Use a light weight ORM for simple CRUD actions (one with a small dependency graph). For performance critical create/update operations use stored procedures (just be careful with complex business logic; if you implement it, remember to test it). Use light weight ORM for mapping efficient SQL views to api/frontend. Use a workflow/async/job processing for anything which has to be long running or crosses system bounds (think credit card processing).
  - jlebrech 8y ago
czechdeveloper 8y ago
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.
- rypskar 8y ago
  >Even simple stuff, such as SELECT COUNT(*) edit seems like my old performance book was outdated or wrong :(
  - ianamartin 8y ago
    Nope. Not even close.
  - czechdeveloper 8y ago
    SELECT COUNT(*) should be the one which is resolved by index/metadata
- ianamartin 8y ago
  You have a problem. And it’s probably related to your db architecture, which was probably designed by devs with ORMs.
  - olavk 8y ago
    What kind of database architecture could cause SELECT * to be slow?
    - kyberias 8y ago
      Any. "Architecture" has nothing to do with that. SELECT COUNT(*) is usually "slow".
    - deleted 8y ago
      [deleted]
  - theandrewbailey 8y ago
collyw 8y ago
In my experience this seems to be a problem of people using frameworks without getting to understand them in any depth (probably because they are off chasing the next hyped up thing). Django is easy to get started with. Its also easy to produce a shitload of queries unless you spend some time getting to understand the ORM and how to optimize queries with it. In my previous work we were generating over a 1000 queries for the front page (we were showing maybe 20-25 products with different options). Everything was done in nested loops, with new queries to the database each iteration. The kid who had written that was apparently building his own framework when I looked up his webpage. Learn to use Django properly before you go building your own crap versions please.

crocal 8y ago

... Which begs the question: what good is an ORM if it does not prevent by design such issues? Here, we are essentially saying users of ORM must also have in their mind the SQL version. Or call an expert after the mess is done :/...

deleted 8y ago
[deleted]
willvarfar 8y ago
I'm not very sure that Jooq and SQLAlchemy and other 'not quite ORMs' really help that much either. I think, as the article suggests, that 'opaque' is as much the problem as the classic Object-Relational impedance mismatch, but I would posit that the opaqueness isn't just that the programmer can't _see_ what happens, but that they also don't _care_. So I'd put my faith in tools instead. If programmers don't want to think about this, let the tools try instead. The static analyser was interesting. Although static analysers have shallow comprehension, they were able to identify anti-patterns that were common enough to make it a useful linter. And of course their classic-mistakes approach can be applied to other languages too, even if each language and ORM needs its own corpus. I sincerely hope that JetBrains and other IDEs bring this kind of analysis into their IDEs. I write a lot of non-Ruby code, and the JetBrain IDEs do keep suggesting nice 'code simplification' and 'generate boilerplate' checkers as I type. But while they check my regex for well-formed-ness and colour my SQL but don't have any kind of meta analysis and comprehension. Missing opportunity. You could take this further for normal non-DB code too - they could warn me when I have inefficient O(n) search in a loop and so on.
- arkh 8y ago
  > You could take this further for normal non-DB code too - they could warn me when I have inefficient O(n) search in a loop and so on. For phpstorm you have the EA extended plugins which gives lot of hints like that. I'm sure you could find the same kind of plugins or write one backed by a static analysis tool for other languages.
shangxiao 8y ago

siscia 8y ago

I never understood why I don't enjoy working with ORM but reading this article make it clearer for me. They blend the distinction between working in memory vs accessing the db. From one side it is very convenient, however I really feel that such performance sensitive operation should be carefully considered and that most SQL should be written by hand.

t0mbstone 8y ago
Or you could just spend 15 minutes learning how to avoid N+1 queries by using the includes() function, and some of the other super obvious performance optimization patterns that every junior Rails dev should know...
olavk 8y ago
Writing SQL by hand does not solve typical performance problems like n+1 queries. If on the other hand you know enough to avoid n+1 queries, then you can also avoid them when using an ORM, and save a lot of work. If you like writing SQL by hand, by all means do so, but you will not automatically get better performance by handwritten SQL as compared to ORM generated SQL.
- marcus_holmes 8y ago
  No, it's not automatically more performant; it's more that you can optimise hand-written SQL for your specific schema and use case. You can't do that with ORM-written SQL.
  - olavk 8y ago
    Depends on what kind of optimizations, and on the ORM I guess. The major problems described in the article, like n+1 queries and filtering on the client rather than in the database seem to be easier to fix in an ORM than in hand-written SQL with boilerplate wrappers. (Of course they are also easier to introduce in an ORM if you don't know what you are doing, but such is the curse of powerful abstractions.) Even things like query hints can be easily applied in the ORM's I know. It is kind of hacky since it breaks abstraction layers - but so is query hints in SQL. But of course there can be some special cases where you just have to drop down to raw SQL for some reason. All ORM's I know allow this.

slifin 8y ago

This should be translated for other back end languages, maybe followed by how to use a relational database as a relational database

TekMol 8y ago

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 Foreach Cars as Car 50 If Car->insurance_end_date < FancyDateLib.now() Nr++ I see variants of this everywhere. And the performance impact is just untoppable. It's often several million times slower then a simple sql query. But beefy hardware with lots of ram and memory takes care of it. 'Our software is enterprise grade, so of course it cannot run on commodity hardware'.

sfvisser 8y ago
People do this because it’s easier, takes less time, and is within the paradigm their head is already in all day. We should fix our abstractions instead of telling people they don’t understand data. The latter is fine every so often, but doesn’t scale and isn’t even true most of the time.
- ianamartin 8y ago
  Or maybe we should just nut up and tell people they don’t understand data?
  - girvo 8y ago
    Why not both? Truly, we should work to have better abstractions. We shouldn’t glorify making our own lives more difficult. And we shouldn’t glorify ignorance either! It’s not an either/or dichotomy here
patkai 8y ago
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.

latch 8y ago

I've worked on moderately busy backend platforms (~10K-20k rps handled on a ~4 e5-2650 and aiming for 5ms 95p response times). It greatly depends on what you're doing, but for the majority of systems which are read heavy (and that most certainly includes "dynamic" sites like Amazon or Wikipedia), I hold to two major beliefs: 1 - Have very long TTLs on your internal cache servers with a way to proactively purge (message queues) and refresh in the background. Caching shouldn't be a compromise between freshness and performance. Have both! 2 - Generate message/payloads/views asynchronously in background workers and have your synchronous path as streamlined as possible (select 1 column from 1 table with indexes filters). Avoid serialization. Precaculate and denormalize. Any personalization or truly dynamic content can be done: 1 - By having the client make separate requests for that data 2 - Merging the data into the payload with some composition. 3 - Glueing bytes together (easier/safer with protocol buffers than json) Do things asynchronously. Use message queues / streams. Beyond that, GC becomes noticeable. For example, Go's net/http used to (might still) allocate much more than other 3rd party options.

iKSv2 8y ago
Do this (point 1) properly and ~80% of your "speed" problems are solved, for a simple web-app-db tiered app.
blattimwind 8y ago
0 - Caching antipattern 101: key = calculate_cache_key() if not cache.has(key): data = expensive_calculation() cache.store(key, data) else: data = cache.get(key)
- memset 8y ago
  This is interesting because this is generally how I implement caching! What would pseudocode look like for a non-antipattern?
  - brightball 8y ago
    Depends on what you are doing. To frame it another way, what happens if 100 requests come in at the same time for that expensive value when it isn’t in the cache yet? The expensive calculation will be run 100 times at the same time. Ideally, you’d rather refresh the cache value in the background once and never allow duplicate requests for it from the web. If you’re running a language that makes it easier to deduplicate requests for certain data, the original approach will last longer. The CacheEx library in Elixir, for example, will only run the expensive calculation once, set the cache and then send the value back to everything that requested it while it was loading.

mping 8y ago

My 2 cents are: if you don't know what queries your orm API is generating, you shouldn't use one. I mean, sometimes there are bugs here and there, but people should know the methods they call. It's the equivalent of making a rest call and complaining about latency. Yeah, it's a method, why is it taking so long?

arwhatever 8y ago
That's an excellent point. I'm very comfortable with my ORM of choice as well as its performance, because I've spent time running SQL Profiler while stepping through my own code to learn what my ORM does, and when it does it.

superasn 8y ago

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 project a lot more.

mnm1 8y ago
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.
- olavk 8y ago
  > 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.
  - mnm1 8y ago
    Lazy loading often fetches too little and eager loading too much. Most orms select all columns in all tables regardless of need. The query translation and hydration overhead of transforming the data into objects even in a fast language are always going to be problems, it's just a matter of how big, over a system without the orm. Since orms are superfluous, every line of code in them adds unnecessary overhead. They are indeed inherently inefficient by definition.
    - olavk 8y ago
      > The query translation and hydration overhead of transforming the data into objects even in a fast language are always going to be problems No they are not. Have you measured this in a real-world application? This overhead is negligible compared to the cost of the query itself. And without an ORM you still have to load the data into some kind of objects or data structures before you pass it to presentation, you will just have to write the code yourself. But yeah, lazy loading in a loop will kill performance. So don't do that.

ianamartin 8y ago

The only big problem with ORMs is when developers are allowed to use them to construct database schemata. As long as you have a not-crazy person in charge of the initial design, you can allow—and even encourage—developers to make versioned changes to the database. If you let us start from scratch with ORMs, it will suck for everyone. There’s a fundamental mismatch between what developers think of as objects and how relational designers think of tables. ORMs can smooth that over. But nothinking can undo the wrong-headed-ness of a developer thinking of a relational dB as an object store.

ssijak 8y ago

ORM for saving objects and very simple queries. Writing SQL or using something like JOOQ in Java to write type-safe SQL for everything else.

collyw 8y ago
I disagree. I have used Django extensively. The ORM handles raw SQL queries, but which is great when you need something beyond the capabilities of ORM, but you loose a lot as well when you go that route. Pagination and sorting are pretty easy additions when you retrieve data using the ORM in the standard way, and now you need to add extra code to handle those specifically. I don't think you can use the Django admin with raw SQL (I never tried, but it doens't make too much sense, as it basically generates a set of views per table). Model methods don't make sense using SQL queries. You should be able to write SQL if you want to be able to use the ORM effectively and I am certainly glad I knew it well before started using Django's ORM.
- ssijak 8y ago
  What makes you think Django ORM is good ORM? What is bad about adding "extra code" if that code is doing a useful and correct thing? And I wonder why people find Django Admin useful for anything than simple CRUD apps. Writing code so it can go along with django admin is a strange way of thinking, there should be other priorities first.

mnm1 8y ago

ORMs are really only useful for throwaway projects and beginners. I have yet to see one without serious downsides in both performance and speed of development, something that they are touted to improve but actually make worse. The ORM I'm stuck with now (Doctrine2) adds a 10x overhead to queries. For the most part, we don't even bother optimizing queries in such situations because why waste time on something that could at most improve performance by 1/10th? The real fix is to get rid of the ORM. That requires a rewrite of two apps that have been in development for four years each. I can't think of a worse architectural decision for business (CRUD) software than using an ORM. Irreversible and forever fucked.

thraxil 8y ago
Don't know much about Doctrine2, but that sounds pretty terrible. I'm sorry you are forced to work with something so inefficient. I've been building apps with Django and Django's ORM for the last 10 years and found essentially zero overhead in most cases. Every once in a while there's a slow page, I open up the debug toolbar which shows me every SQL query that was used to generate the page in a nice waterfall diagram, I see something a little odd and change the order of some filters or add a `select_related`, or `prefetch_related` or discover that some third party library is making a dumb call (unavoidable problem of using third party libraries on any platform) and find a workaround for that. Every once in a blue moon, it appears easier to write a raw SQL statement than figure out what needs to be done to get the ORM to generate it, so I do that. Of course, Django's ORM makes it stupid easy to use a raw SQL statement: https://docs.djangoproject.com/en/2.0/topics/db/sql/ https://docs.djangoproject.com/en/2.0/topics/db/sql/ I've worked with a few other ORMs in Python and other languages over the years as well (in Go, Erlang, Elixir, Clojure, nodejs) and never really encountered any where the ORM had a noticeable performance overhead (dominated by the network latency back and forth from the database) and I've yet to work with one that I couldn't just do a raw query when needed. The closest I've seen is when I started using GORM in Go, I found it running slowly and discovered that it automatically adds a "soft delete" functionality so every query gets an additional "and not is_deleted" clause added. Disabled that feature and it was fine. That was my own fault though for starting to build before I finished reading the documentation, as it was pretty clearly explained in a later section. (OK, also the ORMs I was using in Perl and Java back in the late 90's/early 00's were also pretty terrible, but those were prehistoric times.) You always have to be careful of N+1 problems, but that's not just an ORM thing. You run into that as soon as you have any abstraction in your code. Once you have refactored to `get_list_of_items(some criteria)` and `get_item_details(item)` functions/methods/whatever, whether it is using an ORM underneath or raw SQL, developers working on the app have to know that they can't loop over the results of the first and call the second on each of them. Tradeoffs between reusability and performance are nothing new though and not at all specific to web applications or ORMs.

guiriduro 8y ago

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.)

girvo 8y ago
Yep! See here[0] and here[1] for that idea done with Postgres [0] https://postgrest.org/en/v5.0/ https://postgrest.org/en/v5.0/ [1] https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/

jlebrech 8y ago

what people need isn't an ORM but a DSL, if only there was a Domain Specific Language for Querying the database in a Structured manner.

deleted 8y ago
[deleted]

knesek 8y ago

When I was inexperienced I feared ORMs because of the negative performance impacts I've read they could have. I constantly worried about what would happen if the amount of data increased and I hit ORM induced problem that I could not resolve without major rewrite of data access layer. However, whenever I've actually hit those problems in production, I found the similar thing the authors of the article did - ORM induced performance problems can be fixed by 1-5 lines of code changes, if you knew where the actual problem was. In fact, I learnt that there's no "ORM induced performance problems", there are only problems induced by lack of understanding of how ORM works. As for the knowing where the performance problem is, I find that skill (that I think should be basic) is in fact very elusive in the engineers I encounter. When I interview people, even in what would be upper intermediate to senior level in terms of years of experience, alarmingly small number have ever done or could do (or even would do) performance profiling like described in the article. Yet they do describe how they changed this ORM for that ORM, this DB for that DB, this language for that language, in the name of higher performance. I've seen or heard of: - Teams spending weeks exchanging SQL DB for No SQL DB because of unsolvable performance problem. When hitting the same problem with NoSQL DB, they find that addition of a simple index is solution in both cases - Teams spending weeks exchanging Hibernate for OpenJPA in a complex application, because of performance, without doing any performance analysis, because they've read article that says Hibernate is slow - Teams choosing complex architectures they don't really understand, for performance reasons, without being able to articulate performance requirements of the system they're building These days, whenever someone mentions performance as a reason for anything, I judge their competence based on their response to the question "And how are you measuring and monitoring it?"

ravenstine 8y ago
> Teams spending weeks exchanging SQL DB for No SQL DB because of unsolvable performance problem. When hitting the same problem with NoSQL DB, they find that addition of a simple index is solution in both cases I don't get the idea that one has to pick either SQL or NoSQL, but a lot of people seem to think this way. Why not use both? The SQL portion can more or less be treated as a rich index of relationships, and the NoSQL portion can handle performance when it makes more sense for data to be embedded in parent documents.

qaq 8y ago

The problem with ORMs is they have to cater to the lowest common feature-set of the supported RDBMS.

guipsp 8y ago
This is not the case for pretty much any modern ORM
- qaq 8y ago
  Sure it is show me the ORM that will support 2p commit, window functions, CTEs, JSON operators in PG (just a random set of features)
  - YorickPeterse 8y ago
    http://sequel.jeremyevans.net/ http://sequel.jeremyevans.net/
  - bpicolo 8y ago
    SQLAlchemy supports all of the above - in general it's really terrific about being extensible enough to support the corner cases.
    - qaq 8y ago
      It does look very nice and does support all of the above. I stand corrected (not a Python dev. :))

mukundasai111 8y ago

I figure you can have a touch of segment An, a touch of segment B. My home city is Mumbai (in fact, the rural city of Thane, north of Mumbai) a standout amongst the most thick expansive urban areas on the planet. Open transportation in Mumbai, as far as recurrence, limit, dependability, and cost is now superior to anything any American city (I'm including NYC that). It could be better; for example the city is served by standard check prepares however the metro railroad (whatever measure a tram is) that goes underground is still under development. In spite of all that, and regardless of the way that the tallest working in the city is a unimportant 88 stories[1] the foundation is stressing under the weight each day. All the steady employments (government, tech, managing an account) are moved in only maybe a couple parts of the city (South Mumbai and the Andheri-Bandra-Dadar triangle, perhaps Powai; my geology may be somewhat obsolete in light of the fact that I haven't lived there for a long time). What's more, land in those spots is cosmically expensive[2] (here's a non-extravagance 3-bed condo for USD 1.3m in South Mumbai). That implies everybody whose isn't living with guardians who purchased decades back in those spots has 3-hour round trek drives each day (I was one of those individuals). Building taller wouldn't densify the city since rich individuals will probably purchase that new lodging and they have littler families by and large. Enhancing open transportation won't change laws of separation and movement - like I said it's as of now very great. It would be better for individuals in North Mumbai if there were more occupations closer to them so they wouldn't need to drive so far consistently. Rural, auto driven, sprawl isn't perfect for city development, however nor is the grouping of occupations in a single place. http://sobhalake-garden.in/price.html http://sobhalake-garden.in/price.html

unilynx 8y ago

Pretty cool to read. We built a (currently proprietary) CMS with our own scripting language, and instead of going the ORM way, we merged basic SQL into the language itself. We did that mostly to eliminate sending raw strings to databases (and all the injection risks and complexity that comes with it) but it does allow a few extra optimisations because the compiler can look at both the query and the language using it. So if I do our equivalent of the ORM API Misuse case: IF(RecordExists(SELECT * FROM schema.variants WHERE track_inventory = 0)) { ... } (RecordExists is a function that only checks whether the query returned something, and has been marked that way) the compiler will already reduce this to: IF(RecordExists(SELECT FROM schema.variants WHERE track_inventory = 0 LIMIT 1)) { ... } And likewise a function that selects all columns from a database and returns only one field, has the select reduced to only selecting that one column. The drawback, of course, is that any SQL features of the underlying database not exposed by the scripting language, are unreachable unless you fallback to sending raw query strings again.

psadauskas 8y ago
Of course it depends on the exact circumstances, but your query would probably be faster as `SELECT COUNT(*)` than the `LIMIT 1` you optimize to now. In fact, that’s one of the specific optimizations mentioned in the article.
- adamzochowski 8y ago
  count(*) ends up counting all records, reading one first row is much much faster than finding and counting all. https://wiki.postgresql.org/wiki/Slow_Counting https://wiki.postgresql.org/wiki/Slow_Counting
- unilynx 8y ago
  That depends a lot on the implementation of the database and the communication protocol. In our case, that made no difference

lukaseder 8y ago

> “compared to other popular ORM frameworks such as Django and Hibernate, Rails has 2x more applications on GitHub with 400 or more stars than Django and Hibernate combined.” As if the whole world was hosted on GitHub...

acutesoftware 8y ago

I need a very good reason before using any external library in an attempt to keep the total code base as clean as possible. It is just too easy to be rushed and bring in a heap of code, so I prefer to use SQL instead of ORM's. All access to the database is done in a single module and they are wrapped in functions like below def get_table_as_list(user_id, cols, tbl, where_clause, params_as_list, conn_str, order_by="1", maxrows='2000'): """ This should be the ONLY place that selects from the database """ db = get_db_conn(conn_str) cur = db.cursor() where_clause += ' AND user_id = %s' sql = "SELECT " + cols + " FROM " + tbl + " WHERE " + where_clause + " ORDER BY " + order_by + " LIMIT " + maxrows params_as_list.append(str(user_id)) cur.execute(sql, params_as_list) res = list(cur.fetchall()) cur.close() db.close() return res The database is designed and built first, then in the application the definitions are done like below all_tables = [ {'tbl':'as_note', 'cols':['id','title','pinned', 'important','content','folder'], 'col_types':['id','Text','Checkbox','Checkbox', 'Note','Text'], }, {'tbl':'as_task', 'cols':['id','Title','Pinned', 'Important','Notes','folder','Done'], 'col_types':['id','Text','Checkbox','Checkbox','Note','Text','Checkbox'], }] So far it is working well, and it is very simple to add new tables to the schema and have them working in the application.

blattimwind 8y ago
It's nice to see that people are still able to write 2002-era PHP in Python today.
tasuki 8y ago
What exactly is the problem you're attempting to solve with this? Bonus questions: What about maintenance or admin queries which aren't tied to a specific user_id? What about sql injection?
- acutesoftware 8y ago
  > What exactly is the problem you're attempting to solve with this? Keeping all database access in one place to avoid having selects around the codebase. > What about maintenance or admin queries which aren't tied to a specific user_id? This is the web interface for users, all admin stuff is done elsewhere > What about sql injection? The selects are passed as parametised queries, so the where clause would be 'title = %s AND folder = %s'

Rafert 8y ago

Interestingly their code checker is just a bunch of regular expressions: https://github.com/hyperloop-rails/static-checker https://github.com/hyperloop-rails/static-checker - I would've expected custom Rubocop rules. Rubocop already knows about `where.first? => find_by` for exmple: https://github.com/rubocop-hq/rubocop/blob/master/lib/rubocop/cop/rails/find_by.rb https://github.com/rubocop-hq/rubocop/blob/master/lib/ruboco...

Balf 8y ago
They suggested naïve sequential string replacing for html templating, so I would absolutely expect a bunch of regular expressions.

thermodynthrway 8y ago

It's a bit high level to mention this, but it doesn't have the one query problem I see constantly. Paging using offset and limit. I swear every app I've worked on uses it somewhere. And it's horrifically inefficient 90% of the time.

vorpalhex 8y ago
Can you elaborate on the issues with that? I've used that pattern and haven't found any major woes (yet), but also don't usually paginate complicated queries.
- bpicolo 8y ago
  In postgres and mysql, at least, the database has to re-scan data every time you run a limit/offset query query. It gets progressively slower as your offset increases. The efficient way to handle it is to set a lower limit on the pkid (or other atomically increasing row) of the last record fetched, and fetch in ascending order e.g. SELECT * from my_table where id > (last_row_id_seen) ORDER BY id asc limit 20; Then you have an indexed jump to the correct rows to return. It's pretty straightforward to abstract a batched database iterator with this pattern for use application-wide (usually can be done in ~20-50 loc), but no ORM that I'm aware of supports this pattern natively. Do note that, by extension, it's impossible to efficiently batch iterate over tables that don't have a unique, orderable key in postgres and mysql.
  - kraftman 8y ago
    What if you need to order by something other than id?
    - thermodynthrway 8y ago
      Then, generally, give the user a bunch of filtering options and limit page depth to less than ~1000 entries
- thermodynthrway 8y ago

projectramo 8y ago

The real joy of this article was the following: Some academics (Yang, Subramanian, Lu, Yan, Cheung) were able to produce massive improvements in about a dozen large, mature, battle tested open source projects using just a few lines of code. This should give hope to all those tepidly trying to get into open source. Just go and take a look at the dozens of open source projects in Django or whatever and you could improve the performance by keeping an eye on the ORM. Better yet you might find another thing that makes them even better with ease. Of course, I should add, I think the really clever thing the academics did is to come up with this random link clicking program to time the worst load times of the projects. That whole setup was gold.

Cthulhu_ 8y ago
What's intimidating to me is that to make a change like that, you first need to get the code, get it and the tests running, and actually understand what the code does - and whether your fix has the equivalent result or is subtly broken. They didn't just change a single line in an application, they wrote a huge benchmark suite and dug through miles of code to find issues like this. I've no clue how much time they spent on it, it must've been months.
- projectramo 8y ago
  You think so? Maybe but the ORM lines should be pretty insulated. If you return the same results and only speed them up you should be okay. To take an example, replacing any? with exists? doesn't seem to require testing everywhere in the project. I imagine that the average fix had lines like that. I also suspect that the fixes were pareto distributed -- that a few fixes brought most of the benefit.
- mklepaczewski 8y ago
  This is not necessarily true. You would be surprised how many absolutely trivial performance issues can be found in almost every project. Sometimes it's really just about moving computation of a constant value out of a 'for' loop.
  - 8y ago

brightball 8y ago

I experienced fixing most of thes first hand. I think 90% of the issues come from developers who haven’t worked directly with SQL enough to understand what the ORM is doing. The experience of building apps with hand coded SQL, while not good for a project, is an excellent teacher.

ahallock 8y ago

The problem with ORMs is that they make DB calls seem cheap, and when your application is young and you don't have a lot of rows, everything works wonderfully. Then all the N+1 problems start adding up and the SELECT COUNT(*) performance issues. Since the ORM code is baked into the models, your code is littered with these calls -- in the service layer, views, background workers. That's why I really like Promises/Futures in Javascript. You know exactly when you're executing a DB operation and you have to think about the implications more. It's not just a simple object accessor.

krainboltgreene 8y ago

There are some really glaring issues in this article. For example, the author suggests that each -> update is worse than update_all, and while the performance may be better it's comparing apples to oranges! update_all, in ActiveRecord, bypasses business logic, bypasses updating the updated_at, bypasses validation and more. It's not the same thing at all. There is no difference between where.first and find_by, that is just a bikeshed. Not every map(column) can be turned into pluck(column) if there are business logic wrappers around that column.

mendelk 8y ago

Having worked many years both with ORMs and with "old-school" devs slinging raw SQL strings and everything in between, I've come to the conclusion that "ORMs don't kill [performance], people do". :)

pjungwir 8y ago

> Inefficient Rendering (IR). This one was a surprise to me He's talking about the common Rails pattern of rendering a small Haml/ERB partial over & over in a loop. I've noticed big perf hits from this before, but never completely understood why. Rendering the exact same result directly in the loop body, without calling a second partial, gives a big speedup. There is an extensive discussion here: https://softwareengineering.stackexchange.com/questions/157108/rails-does-using-partials-slow-views-rendering https://softwareengineering.stackexchange.com/questions/1571... I would love to have some better understanding around this, although it sounds like no one has a clear idea of the cause. In development, I've noticed that Rails re-reads the partial file off disk every iteration of the loop. I don't know if that happens in production too, but if so it would explain a lot.

NightlyDev 8y ago

How the hell can these pages have common load times of ~500ms? This must be the worst case pages, right? Hundreds of milliseconds is very slow if network latency is out of the picture.

vladmihalcea 8y ago

Most ORM-related problems are related to lack of knowledge of how the tool works. However, if you understand how databases work, how to tune the driver and how to get the ORM tool to generate the same queries you'd otherwise write yourself, then you are fine. For more details, check out these [14 High-Performance Persistence Tips](https://vladmihalcea.com/14-high-performance-java-persistence-tips/ https://vladmihalcea.com/14-high-performance-java-persistenc...).

syastrov 8y ago

I’ve experienced a lot of n+1 queries problems as causes for bad performance. Often times this was a result of wrapping the ORM in abstraction layers (for business logic and fears of being “locked in” to the ORM). We rewrote that part of the application using a different ORM (in Python). Making use of a tool that could help find these problems automatically helped greatly and we didn’t have a single performance problem when we went into production the rewritten service. This module can detect the n+1 queries problem automatically in Python ORMs: https://github.com/jmcarp/nplusone https://github.com/jmcarp/nplusone Looking forward to seeing more automated tools like this in the Python/Django world.

itsderek23 8y ago
Scout also detects these for Django, ordering by the most performing N+1s: http://blog.scoutapp.com/articles/2018/04/30/finding-and-fixing-n-1s-in-django-apps http://blog.scoutapp.com/articles/2018/04/30/finding-and-fix...
- sut-123 8y ago
  And bullet is used for Rails to detect N + 1 queries. https://github.com/flyerhzm/bullet https://github.com/flyerhzm/bullet

rmc 8y ago

One of the example Rails applications they use is the code which powers the OpenStreetMap website. They populated their install by randomly filling in fields on the website. Which doesn't include any map editing! For OSM they suggest changing how the diary feature operates, which is a tiny, almost irrelevant part of the OSM website software stack. The OSM database has millions of geographic objects, and they talk about the diary system on the website. > For example, when we profile the latest version of Openstreetmap, a collaborative editable map system, we find that a lot of time is spent on generating a location_name string for every diary based on the diary’s longitude, latitude, and language properties stored in the diary_entry table The paper claims to have filed bug reports, and has URLs. But those links don't exist. Paper: https://hyperloop-rails.github.io/220-HowNotStructure.pdf https://hyperloop-rails.github.io/220-HowNotStructure.pdf openstreetmap-website: https://github.com/openstreetmap/openstreetmap-website/ https://github.com/openstreetmap/openstreetmap-website/ Claimed Issues submitted: https://github.com/hyperloop-rails/issues-summary https://github.com/hyperloop-rails/issues-summary

lelelelemon 8y ago
Thanks for looking into this - I am one of the authors of the study. We have updated the submitted issues link to https://github.com/hyperloop-rails/study-replication/tree/master/issues-summary https://github.com/hyperloop-rails/study-replication/tree/ma.... Let us know if you have any further questions.

deleted 8y ago

[deleted]