Re: SQLite wrappers
Hi
Just got back from hols in Ballamory.
I used eusqlite as it stands atm, and created this. Note sqlite_get_table is a
wrapper for sqlite_exec, and as yet I haven't needed anything more complex than
what the library provides, but looking at the benchmarks, it may be that sqlite
prepared statements may be quite a bit faster than throwing sql statements at the
engine one at a time - will look into this.
Note you can use begin / end / commit transactions with what is there already,
and as it stands the eusqlite libs are very usable.
Also I'm not sure how much eu, or my code, is contributing to the slowdown (any
contributions from the speed meisters out there gratefullt accepted!)
--a close 'equivalent' to the c program at
--
http://anchor.homelinux.org/SQLiteTuning#head-0dd2e45aa0ac71a09ca7860dc4bc435f5e8bdbc5
-- sqlite_get_table is a wrapper for sql_exec, and we can't prepare statements
(yet)
-- this is quite a bit slower than the compiled c versions
without warning
include eusqlite3.ew
include get.e
object VOID
VOID = {}
------------------------------------------------------------------------
function eusql_begin(integer db)
------------------------------------------------------------------------
sequence sqlite_data
sqlite_data = sqlite_get_table(db, "BEGIN") --shorthand for begin
transaction
if sqlite_last_err_no != SQLITE_OK then
printf(1, "couldn't begin transaction: %s\n", {sqlite_last_err_desc})
return 0
end if
return 1
end function
------------------------------------------------------------------------
function eusql_commit(integer db)
------------------------------------------------------------------------
sequence sqlite_data
sqlite_data = sqlite_get_table(db, "END") --shorthand for end transaction,
and it also commits the transaction
if sqlite_last_err_no != SQLITE_OK then
printf(1, "couldn't comit transaction: %s\n", {sqlite_last_err_desc})
return 0
end if
return 1
end function
------------------------------------------------------------------------
function elapsed(atom start_time, atom end_time)
------------------------------------------------------------------------
return end_time - start_time
end function
------------------------------------------------------------------------
--main
------------------------------------------------------------------------
--variables
integer db, N, xact_size, n_this_xact
sequence cmd, temp_seq, sql_data, sql_cmd
atom START, FINISH, x, y, z
cmd = command_line()
if length(cmd) < 3 then
printf(1,"\nUsage: %s <N> <X>\n\n", {cmd[2] })
puts(1,"\tInsert <N> rows into a table of an SQLite database\n")
puts(1,"\tusing transaction sizes of <X>.\n")
puts(1,"\tThe table has four columns of numeric data:\n")
puts(1,"\t field_1 integer (primary key)\n")
puts(1,"\t field_2 float\n")
puts(1,"\t field_3 float\n")
puts(1,"\t field_4 float\n")
puts(1,"\tThe integer field will have values 1..<N> while the\n")
puts(1,"\tdouble precision values are random on [-50.0, 50.0]\n")
? 1/0
end if
--interpret command line
temp_seq = value(cmd[3])
N = temp_seq[2]
temp_seq = value(cmd[4])
xact_size = temp_seq[2]
--database shouldnt exist before start, so
if platform() = LINUX then
system("rm bench.db", 0)
else
system("del bench.db", 0)
end if
puts(1, "Running.......\n")
START = time()
--we do this a slightly different way to the C example,
--but the end result is (hopefully) the same, or very similar.
--sqlite_get_table is a wrapper for sqlit3_exec, the returns sre the same
db = sqlite_open("bench.db", 0)
sql_data = sqlite_get_table(db, "PRAGMA synchronous = OFF;")
VOID = eusql_begin(db)
sql_data = sqlite_get_table(db, "create table table_name(field_1 integer primary
key, " &
"field_2 float, " &
"field_3 float, " &
"field_4 float)")
--we can't prepare statements - yet, maybe one day if it ever looks like it may
be useful
--so we will have to create the statements one at a time (sorry)
n_this_xact = 0
for i = 0 to N do
x = 50 - (rand(300) / 3.141592654)
y = 50 - (rand(300) / 3.141592654)
z = 50 - (rand(300) / 3.141592654)
sql_cmd = sprintf("insert into table_name (field_2, field_3, field_4)
values (%f, %f, %f)", {x, y, z})
--note, field_1 autoincrements
sql_data = sqlite_get_table(db, sql_cmd)
if sqlite_last_err_no != SQLITE_OK then
printf(1, "Error : %s\n", {sqlite_last_err_desc})
VOID = wait_key()
abort(0)
end if
n_this_xact += 1
if n_this_xact >= xact_size then
n_this_xact = 0
VOID = eusql_commit(db)
VOID = eusql_begin(db)
end if
end for
VOID = eusql_commit(db)
sqlite_close(db)
FINISH = time()
printf(1," %d inserts to %s in %.3f s = %.2f inserts/s\n", {N, "bench.db",
elapsed(START, FINISH), N/elapsed(START, FINISH) })
VOID = wait_key()
--Sample Results from c program
--Pentium4 3.0 GHz, 1 GB RAM, IDE disk drive, ext3, RHEL v3, Linux kernel
2.4.21:
--
--./sqlite_insert 100000 20000
--100000 inserts to bench.db in 0.829 s = 120626.53 inserts/s
--Opteron 2.4Ghz, 4GB RAM, Seagate 10k SCSI disk w/ XFS noatime, 2.4.27 Kernel:
--
--ponte [/local/scratch/kcroft] ./sqlite_insert 200000 60000
--200000 inserts to a.db in 0.646 s = 309836.37 inserts/s
--last edited 2005-05-20 16:56:18 by adsl-69-234-50-233
--Sample results, from eu program
--exw sqlite_insert.exw 100000 20000
--100000 inserts to bench.db in 13.230 s = 7558.58 inserts/s
--Dell inspiron 2200 1.3Ghz 256Mb ram win xp
--200000 inserts to bench.db in 32.660 s = 6123.70 inserts/s
--Dell 2.4Ghz, 512Mb
-- 100000 inserts to bench.db in 6.718 s = 14885.38 inserts/s
Chris
http://members.aol.com/chriscrylex/euphoria.htm
http://uboard.proboards32.com/
http://members.aol.com/chriscrylex/EUSQLite/eusql.html
|
Not Categorized, Please Help
|
|
