SQLite ffi library benchmark with LuaJit implementation.

sqlitemark.lua

local sql = require "ffi/ljsqlite3"

-- Global Variables
local singledb = "single.sqlite"
local manydb = "multi.sqlite"
local elapsed = 0
local times = {}
local values = 1000000
local tables = 1000
local x, y, z = {}, {}, {}
assert(values%tables == 0)
local pertable = values/tables

-- Utility Functions
function done(tag)
  local finish = os.clock() - elapsed
  print(string.format(" Done! -> %.3f [s]", finish))
  times[tag] = finish
end

function start(msg)
  io.write(msg.."...\t")
  elapsed = os.clock()
end

function check_size( filename )
  file = assert(io.open(filename,"r"))
  filesize = file:seek("end")
  file:close()
  print(string.format("Size (%s): %.3f MB",filename,filesize/1024^2))
end

-- Random generation
start("Generate")
for i=1, values do
  x[i], y[i], z[i] = math.random(), math.random(), math.random()
end
done("g")

-- Benchmark
print("Benchmarking SQLite with "..values.." rows in a single table.")

-- Create a database and vertex table
start("Create")
local conn = sql.open(singledb)
conn:exec[[
CREATE TABLE Vertex(
  id INTEGER PRIMARY KEY, 
  x REAL NOT NULL, 
  y REAL NOT NULL, 
  z REAL NOT NULL);
]]
done("cs")

-- Prepare statement to insert values and wrap it in a transaction
start("Insert")
conn:exec("BEGIN")
local stmt = conn:prepare("INSERT INTO Vertex VALUES(NULL,?,?,?)")
for i=1,values do
  stmt:reset():bind(x[i],y[i],z[i]):step()
end
conn:exec("COMMIT")
done("is")

-- Select values from database and store them in memory array
start("Select")
local s = conn:exec("SELECT * FROM Vertex")
done("ss")

-- Assert that every value written was read correctly
start("Asert")
for i=1,values do
  assert(x[i] == s[2][i])
  assert(y[i] == s[3][i])
  assert(z[i] == s[4][i])
end
done("as")

-- Garbage Collection
s = nil
collectgarbage("collect")

-- Check database size, close database and delete file
check_size(singledb)
conn:close()
os.remove(singledb)

-- Benchmark
print("Benchmarking SQLite with "..values.." rows in "..tables.." tables.")

-- Create a database and n vertex tables
start("Create")
local conn = sql.open(manydb)
conn:exec("BEGIN")
for n=1,tables do
  conn:exec("CREATE TABLE Vertex"..n.." (id INTEGER PRIMARY KEY, x REAL NOT NULL, y REAL NOT NULL, z REAL NOT NULL);")
end
conn:exec("COMMIT")
done("cm")

-- Prepare statement to insert values in tables and wrap it in a transaction
start("Insert")
conn:exec("BEGIN")
for n=1,tables do
  local stmt = conn:prepare("INSERT INTO Vertex"..n.." VALUES(NULL,?,?,?)")
  local offset = pertable * (n-1)
  for i=1,pertable do
    stmt:reset():bind(x[i+offset],y[i+offset],z[i+offset]):step()
  end
end
conn:exec("COMMIT")
done("im")

-- Select values from database and store them in memory array
start("Select")
local m = {}
for n=1,tables do
  m[n] = conn:exec("SELECT * FROM Vertex"..n)
end
done("sm")

-- Assert that every value written was read correctly
start("Asert")
for n=1,tables do
local offset = pertable * (n-1)
  for i=1,pertable do
    assert(x[i+offset] == m[n][2][i])
    assert(y[i+offset] == m[n][3][i])
    assert(z[i+offset] == m[n][4][i])
  end
end
done("am")

-- Garbage Collection
m = nil
collectgarbage("collect")

-- Check database size, close database and delete file
check_size(manydb)
conn:close()
os.remove(manydb)