We are a fan of sail.js but one thing which remained a mystery to us was whether to use the sails.js waterline ORM and not. During our development phase, we had encountered various problems such as processing latency, poor performance etc as our database grew. We analyzed the performance of raw query and waterline orm query, the difference in the performance found was considerably high but maintaining raw query was also bit tricky and difficult. To reach a solution to this problem our blogger Arshad Kazmi had a small conversation with sails.js founder Mr. Mike McNeil on waterline orm of sails.js. The details are below:
Note: we were using sails.js v0.12
For reference: A (Arshad), M (Micke McNeil)
A: Are there any alternative to waterline orm which can be used with sail.js?
Roberto Wesley Overdijk has done a lot to make wetland easy to use with Sails.
The biggest gap in waterline right now IMO for SQL databases is “whose” queries (WHERE subqueries). There’s sendNativeQuery() to work around that, or you can also usually use multiple orm calls instead. I usually go with the latter because it keeps things simpler for other members of the team or our customers.
That said, while new v1 aggregation methods help, we don’t have any plans to add officially-supported “group by” in waterline anytime soon- so if you’re doing a bunch of that for a particular action, you might consider writing a native SQL query for use in that action.
On our team, we use the strategy of only making the jump to native SQL when otherwise the # of non-native-queries required jumps up to o(n), where n might be the size of the result set (or some known constant multiple of it). The goal is to make sure n is never tied to the total size of the database, or if it is, that it’s tied to a model whose “total # of records” grows very slowly or in tandem with a growth metric that will allow time and focus for more optimization in the future.
(Eg if you’re building an app for a startup, it’s probably not the worst thing in the world for a particular action to start off having a worst-case-scenario # of queries that are tied to the number of paying customers they have— assuming it saves you time that you can spend making other features which will help the business succeed)
if an action does O(n) queries, where n is tied to the size of the result set in some way, you’re probably best off not using a native query (unless it’s faster for you to write)
if it would have to do more queries than that (Eg tied to the number of rows in the DB rather than the size of the result set), ask yourself (and the business) how big the table in question could get near-term. In that last case, also consider how the table(s) in question will grow over time, and in relationship to the scale of the business and its ability to afford the time and resources to optimize further optimize queries later
A: When I started with Sails I was using waterline but as the traffic increased and our database size grew, our wires got slower with the waterline. So I started using raw queries and found that it was much faster than the waterline
M: So it sounds like you had actions with queries of the second or third variety
A: But continuously using raw queries makes code maintenance a bit tricky, so I wanted to use some orm which will be faster like raw queries. I also face issues with nested joins in the waterline. So I was looking for an orm which can fulfil these criteria and has good support with sails
M: Just bear in mind that these considerations are there with any orm- the differences in performance between ORMs are small variations tied to processing latency. The biggest bottleneck is how the orm is used
A: Exactly my next question was this, whether I am using the orm in the wrong way which is giving me bad performance. Is there any way to check as I get very poor performance with waterline queries.
But there are stuff orms can to do help protect you, and stuff they can do to make it worse.
In sails 0.12, waterline’s .update() fetched all affected records by default. So if you updated everything in your 10 million row table, it would flood RAM and crash the process.
So now it fetches no records by default- unless you use .fetch()
When you say you’re getting poor performance with waterline queries, I assume you’re getting poor performance with a set of queries rather than one— that’s the usual issue (with a few exceptions like the one about update I mentioned above)
Like that’s also why we got rid of .save() — it made it too easy to write one line of code that could kick off tons of queries behind the scenes
I’d suggest isolating slow bits of code, then looking at the underlying queries it’s generating
Taking a wild guess, it’s probably not the individual queries themselves, but the number of queries being run by the block of code.
A: So basically I get issues when I run my code using JMeter with multiple requests at a time and when I use raw queries for the same traffic load, there is always a good amount of execution time difference. I checked that using a simple select query with the same traffic load and there was a difference between the response time of both the results set.
M: You’re going to have that experience with any ORM — it’s a trade-off. The next thing to look at is “why”— until you know that, you wouldn’t be able to predict if switching orms would yield any better performance
A: For the record I am still using sails 0.12
M: Ah ok that could make a big difference. (Suggested us to upgrade to sails.js 1.0)
A: Considering my issues, what you suggest me to use. Should I go with the other orm which you suggested or continue using the raw queries or should I test the queries with multiple orms and check which gives the better results or upgrading to sails 1.0 can improve the performance with waterline?
M: I would do none of the above to start— first I would enable query logging, pop open postman and hit the route, then look qualitatively at what queries are popping out
And regarding 1.0: It’s hard to remember all the various things right now because there are so many, but short answer is holy crap yes
(Mainly because it prevents you from doing slow things now)
(Well I mean some of them anyway— it’s always possible to do slow things)
A: Cool. I will upgrade my sails.js to v1.0 and then start refactoring the queries which are running in the background by orm.
M: Yeah and then you could use await which makes it way easier to tell what is going on all the time, tbh Callback hell has been node and I’s dirty little secret since we first started seeing each other, and I can safely say that all the process crashing and unhandled errors are finally over. Most of the reference pages on next.sailsjs.com are updated to use await now, so I’d say take a look.
A: Thanks for the information. I will give it a try
M: Seriously I’m kind of a Luddite about new stuff. Most new things annoy me. This is one new thing in js that you’ve gotta check out.
This conversation helped us to debug our issue and also helped us to arrive at a solution. If you have any question, please mention in the comment section below.