Speed Test: Database vs. Programmatic Sorting

Background

I want to get 100,000 rows from my database in a specific order. Here's the model:

var event = {  
    id: 1,
    time_start: 2015-05-15 23:31:27.547+00,
    time_stop: 2015-05-15 23:45:27.522+00,
};

I know that databases quickly order pre-indexed values, for example, auto-incrementing ids and foreign keys. What if I want to sort by 'time_start'? Would I be better off sorting programically in JavaScript?

Node.js' Helpers

This story is as much about database performance vs. programatic sorting as it is about how to objectively measure quickness. Everyone can talk about theoreticals and time complexity, but what about comparing milliseconds to milliseconds?

We all know console.log, but what about console.time? This function has been included in Node.js since the beginning (v.0.1.104). It's super easy to use:

console.time( 'test' ); // name the start  
for( var i = 0; i < 1000; i++ ) {  
    continue;
    // do something
}
console.timeEnd( 'test' ); // tell the console which timer to report on  

Here's the output:

Duration of .049ms printed in console to complete test.

Ready, Set, Go

Here's the code for database (PostgreSQL through Sequelize) ordering:

var getData = function() {  
  console.time( 'database' );
  return event.findAll( { order: 'time_start' } )
  .then(function() {
    console.timeEnd( 'database' );
  });
};

getData();  

Here's the code to get unordered rows, then order programmatically:

var getData = function() {  
  console.time( 'order' );
  return models.charge_event.findAll()
  .then(function( events ) {
    events.sort(function( a, b ) {
      return a.time_start - b.time_start;
    });
    console.timeEnd( 'order' );
  });
};

getData();  

Results

The database sorting is quicker, no matter how many times I run the tests. Could we use a better algorithm than the native Array.sort or complicate things with join tables? That's another post.

Moral of the story: don't be afraid to lean on your database's ORDER BY command like I was.

comments powered by Disqus