3 ms·
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 a
by 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 agoNo, 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 agoDepends 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.
- aidos 8y agoDepends on the ORM. SQLAchemy will let you write any query via the ORM
- siscia 8y agoSorry, I believe I wasn't clear. Definitely writing plain SQL does not solve performance issues. However, it does force the developer to draw a clear line distinguishing where he is accessing the database and where he is working with in memory data structures.
- hungerstrike 8y agoSQL is way more expressive than most ORMs, so solving problems is easier in general.
- pier25 8y agoSure, writing SQL won't solve design problems, but some ORMs need multiple queries to deliver data with relationships, joins, etc, that could be done in a single SQL query.
- olavk 8y agoWhat ORM's does not support joins?
- spraak 8y agoWaterline (for Sails.js) is horrible. Last I looked there's no way to do a kind of `deepPopulate` (requiring an inner join)
- mikermcneil 8y agoSorry you feel that way! The rest of the team and I work hard on it as often as we can. But deep populate is not a feature we’ll be adding— Sails and Waterline are opinionated, and I don’t feel that the performance penalty and potential issues are worth it— instead, we recommend using a native SQL query when you need to do a custom join. Built in support for WHERE subqueries, on the other hand, is on our roadmap. Currently working to finish core MSSQL support first though. Hope that helps!
- Terr_ 8y agoI imagine all of the non-toy ones would support joins for getting raw tabular data or for restricting what single kind of item (e.g. "Customer") you're pulling out. Where it might get trickier is when you have something like a Customer-Region-Vendor join, and you actually want it to go ahead and create objects for the all of the Customers and Regions and Vendors whose data was pulled out of that one select.
- ht85 8y agoIt is actually not that bad if instead of a single query, you do one query per table. It even offsets some of the burden to the app, which is generally easier to scale than the database. The idea is that you query the "root" table, loop over the results and build an array of IDs, then do additional queries to the other tables with a "where whatever_id IN (...)".