instagram

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.

Selbstkochender Topf EveryCook

EveryCook

Es gibt jetzt einen Topf, der einfache Sachen wie Suppe o.ä. kochen kann. Der Prototyp wurde auf der gerade stattfindenden 29C3 vorgestellt.

SPIEGEL Online Artikel

Website von EveryCook

Quelle des Bildes: http://everycook.org/cms/images/startpage/121115_everycook_prinzip.png

MySQL zu SQLite3 konvertieren

Was das tolle Sequel gem alles kann!

gem install sequel
sequel mysql://user:password@host/database -C sqlite://db.sqlite

Gefunden hier.

instagram