SQLite3: LIKE-Operator is faster than datetime functions

Recently I saw some code where an archive table was queried for specific time spans. For example, sum up all records for a specific month or year. The WHERE-clause looked like this:

SELECT ... FROM archiv WHERE `date` LIKE '2012-12-% %:%:%'

My first intention was to refactor this with the documented sqlite datetime functions but wanted to test how much faster it is. So I wrote two small Ruby scripts and benchmarked it with time ruby bench.rb (Tested on my Macbook Pro).

# bench.rb with LIKE
require 'sqlite3'

db  = SQLite3::Database.new '/Users/ben/Downloads/solar.sqlite'

20.times do
  # day span
  db.execute("SELECT (MAX(`E-Total`) - MIN(`E-Total`)) FROM archiv WHERE `date` LIKE '2012-12-30 %:%:%'")
  # month span
  db.execute("SELECT (MAX(`E-Total`) - MIN(`E-Total`)) FROM archiv WHERE `date` LIKE '2012-12-% %:%:%'")
  # year span
  db.execute("SELECT (MAX(`E-Total`) - MIN(`E-Total`)) FROM archiv WHERE `date` LIKE '2012-%-% %:%:%'")
end
# bench.rb with strftime
require 'sqlite3'

db  = SQLite3::Database.new '/Users/ben/Downloads/solar.sqlite'

20.times do
  # day span
  db.execute("SELECT (MAX(`E-Total`) - MIN(`E-Total`)) FROM archiv WHERE strftime('%Y-%m-%d', `date`) = '2012-12-30'")
  # month span
  db.execute("SELECT (MAX(`E-Total`) - MIN(`E-Total`)) FROM archiv WHERE strftime('%Y-%m', `date`) = '2012-12'")
  # year span
  db.execute("SELECT (MAX(`E-Total`) - MIN(`E-Total`)) FROM archiv WHERE strftime('%Y', `date`) = '2012'")
end

I ran all benchmarks three times and calculated the average.

     | LIKE     | strftime
-----+----------+-----------
real | 0m9.865s | 0m16.130s
user | 0m7.145s | 0m12.828s
sys  | 0m2.691s |  0m3.246s

Surprisingly the LIKE-Operator is much faster then the strftime function. I don't know why it is, but I will keep the code as it is.

Related Posts