Sails.js Huge performance difference between blueprint route and native SQL query

1.7k views Asked by At

I am using sails version 0.10.5 for a particular project. We are using postgresql as the underlying database which is a few GBs in size. Recently I started noticing a problem. There is a postgres table whose model definition is as follows:

module.exports = {

  attributes: {

    user: {
      model: "user",
      required: true
    },

    organization: {
      model: "organization",
      required: true
    },

    questionaire: {
      model: "questionaire",
      required: true
    },

    mailinglist: {
      model: "mailinglist",
      defaultsTo: null
    },

    name: {
      type: "string",
      required: true
    },

    broadcast: {
      type: "boolean",
      defaultsTo: false
    },

    link: {
      type: "string",
      defaultsTo: null
    },

    count: {
      type: "integer",
      defaultsTo: 0
    }


  }
};

And when I use the controller's blueprint route with URL like:

GET /api/survey?broadcast=false&organization=2

It was taking over 30 seconds to return the result. I have both the columns indexed in postgres and also there is a composite index using both these columns also. Also, when I run the query in postgres, it returns the result in milliseconds. So, I was confused as to why is it taking so long via blueprint route.

So, I modified the route by overriding it in the controller:

  find: function (req, res){
    var packet = req.params.all();
    var myQuery = "select * from survey where 1=1 ";
    Object.keys(packet).forEach(function (key){
      myQuery += " and "+ key + " = " + packet[key] + " "
    })
    Survey.query(myQuery, function (err, result){
      if(err){
        return res.json(500, err);
      }
      else{
        return res.json(200, result.rows)        
      }
    })
  },

and then I could get extremely fast performance this way. So my question is, should I avoid using Waterline's methods whenever performance is a requirement or is there anything I did wrong in my model definition or anywhere else ?

0

There are 0 answers