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