luadbi和luasql两种lua duckdb驱动的性能对比

利用自定义函数输出各种类型数据的luadbi驱动ducktpch.lua

DBI = require "DBI"

dbd, err = DBI.Connect( 'DuckDB', 'tpch_duckdb', 'dbuser', 'password' )
assert(dbd, err)

dbd:autocommit(true)


statement = assert(dbd:prepare( "SELECT * from lineitem" ))
statement:execute()
tabcol={}
tabval={}
i=1
for row in statement:rows(true) do

if (i==1)
then
 for key, value in pairs(row) do tabcol[i]=key i=i+1 end
 headerstring=table.concat(tabcol, ",")
 print(headerstring)
end


for key, value in pairs(tabcol)do tabval[key]=row[tabcol[key]] end
rowstring=table.concat(tabval, ",")
print(rowstring)
end

利用duckdb C API函数duckdb_value_varchar输出各种类型数据的字符串的luasql驱动ducktpch2.lua

driver = require"luasql.duckdb"

env = assert (driver.duckdb())

con = assert (env:connect("tpch_duckdb"))

cur = assert (con:execute"SELECT * from lineitem")

row = cur:fetch ({}, "a")	-- the rows will be indexed by field names

tabcol={}
i=1
for key, value in pairs(row) do tabcol[i]=key i=i+1 end
headerstring=table.concat(tabcol, ",")
print(headerstring)

tabval={}

while row do
for key, value in pairs(tabcol)do tabval[key]=row[tabcol[key]] end
rowstring=table.concat(tabval, ",")
print(rowstring)
  row = cur:fetch (row, "a")	-- reusing the table of results
end

执行结果

root@66d4e20ec1d7:/par/luadbi# lua ../ducktpch.lua
l_linestatus,l_linenumber,l_comment,l_tax,l_commitdate,l_extendedprice,l_shipdate,l_suppkey,l_partkey,l_receiptdate,l_shipmode,l_returnflag,l_discount,l_shipinstruct,l_quantity,l_orderkey
O,1,to beans x-ray carefull,0.02,1996-02-12,24386.67,1996-03-13,785,15519,1996-03-22,TRUCK,N,0.04,DELIVER IN PERSON,17.00,1

root@66d4e20ec1d7:/par/luadbi# time lua ../ducktpch.lua >/dev/null

real	0m7.979s
user	0m7.756s
sys	0m0.352s


root@66d4e20ec1d7:/par/luadbi# time lua ../ducktpch2.lua >/dev/null

real	0m9.301s
user	0m8.424s
sys	0m1.140s

root@66d4e20ec1d7:/par/luadbi# time lua ../ducktpch.lua >/tmp/1.csv 

real	0m9.137s
user	0m8.196s
sys	0m1.096s
root@66d4e20ec1d7:/par/luadbi# time lua ../ducktpch2.lua > /tmp/2.csv

real	0m9.905s
user	0m8.636s
sys	0m1.296s

ls -l /tmp/1.csv  /tmp/2.csv
-rw-r--r-- 1 root root 75524898 Nov  3 14:59 /tmp/1.csv
-rw-r--r-- 1 root root 75448328 Nov  3 15:00 /tmp/2.csv

两种驱动没有明显差异。
如果把print到控制台再重定向改为直接对文件写操作，则性能提升，不过这与duckdb驱动无关。

-- 以附加的方式打开只写文件
file = io.open("testlua.csv", "a")

-- 设置默认输出文件为 test.lua
io.output(file)
io.write(headerstring)
io.write("\n")

while row do
 for key, value in pairs(tabcol)do tabval[key]=row[tabcol[key]] end
local rowstring=table.concat(tabval, ",")
-- print(rowstring)
io.write(rowstring)
io.write("\n")
  row = cur:fetch (row, "a")	-- reusing the table of results
end
-- 关闭打开的文件
io.close(file)

排除print慢的操作，都用直接写文件来对比，还是平局。

root@66d4e20ec1d7:/par/luadbi# time lua ../ducktpchf.lua 

real	0m8.632s
user	0m8.096s
sys	0m0.136s


root@66d4e20ec1d7:/par/luadbi# time lua ../ducktpch2f.lua 

real	0m7.259s
user	0m7.068s
sys	0m0.348s

完整直接写文件脚本如下
luadbi ducktpchf.lua

DBI = require "DBI"

dbd, err = DBI.Connect( 'DuckDB', 'tpch_duckdb', 'dbuser', 'password' )
assert(dbd, err)

dbd:autocommit(true)


statement = assert(dbd:prepare( "SELECT * from lineitem" ))
statement:execute()
tabcol={}
tabval={}
i=1

-- 以附加的方式打开只写文件
file = io.open("testlua.csv", "w")

-- 设置默认输出文件为 test.lua
io.output(file)


for row in statement:rows(true) do

if (i==1)
then
 for key, value in pairs(row) do tabcol[i]=key i=i+1 end
 headerstring=table.concat(tabcol, ",")
 print(headerstring)
 io.write(headerstring)
io.write("\n")
end


for key, value in pairs(tabcol)do tabval[key]=row[tabcol[key]] end
rowstring=table.concat(tabval, ",")
-- print(rowstring)
io.write(rowstring)
io.write("\n")
end
-- 关闭打开的文件
io.close(file)

luadbi ducktpch2f.lua

driver = require"luasql.duckdb"

env = assert (driver.duckdb())

con = assert (env:connect("tpch_duckdb"))

cur = assert (con:execute"SELECT * from lineitem")

local row = cur:fetch ({}, "n")	-- the rows will be indexed by field number

local tabcol={}
i=1
for key, value in pairs(row) do tabcol[i]=key i=i+1 end
local headerstring=table.concat(tabcol, ",")
print(headerstring)

local tabval={}

-- 以附加的方式打开只写文件
file = io.open("testlua.csv", "w")

-- 设置默认输出文件为 test.lua
io.output(file)
io.write(headerstring)
io.write("\n")
while row do
 for key, value in pairs(tabcol)do tabval[key]=row[tabcol[key]] end
local rowstring=table.concat(tabval, ",")
-- print(rowstring)
io.write(rowstring)
io.write("\n")
  row = cur:fetch (row, "n")	-- reusing the table of results
end
-- 关闭打开的文件
io.close(file)