Hello!
On Sunday 11 October 2009 22:52:29 Jay A. Kreibich wrote:
> A bit to my surprise, the difference is even more significant using
> prepared statements in a C program. For a half-million selects over a
> similar table in a :memory: database, there is a 20% speed-up by
> wrapping all the selects in a transaction (vs the 10% you're seeing).
> It's averaging about 4.3 seconds in auto-commit mode, and 3.4 seconds
> in an explicit transaction.
The tcl programm doing copy of the selected rows. May be your C programm
doesn't copy the selected data?
The modified tcl test script can show how transaction increase speed of data
extracting. In this case the test programm perform copy of data too but the
data doesn't extracting from database table when we use the "select NULL"
construction.
The performance increased of ~10% when we extract table data and of
~3% again.
====================================
$ ./test.tcl
insert transaction 500000 rows
21233766 microseconds per iteration
select 500000 rows
28164019 microseconds per iteration
select without extract 500000 rows
26379441 microseconds per iteration
select transaction 500000 rows
25749923 microseconds per iteration
select transaction without extract 500000 rows
25644248 microseconds per iteration
====================================
$ cat ./test.tcl
#!/usr/bin/tclsh8.5
package require sqlite3
sqlite3 db :memory:
set limit 500000
db eval {create table test(id int primary key, value text)}
puts "insert transaction $limit rows"
puts [time {
db transaction {
for {set i 0} {$i<$limit} {incr i} {
set value "value $i"
db eval {insert into test (value) values ($value)}
}
}
}]
puts "\nselect $limit rows"
puts [time {
for {set i 0} {$i<$limit} {incr i} {
set rowid [expr round(rand()*$limit)]
db onecolumn {select value from test where id=$rowid}
}
}]
puts "\nselect without extract $limit rows"
puts [time {
for {set i 0} {$i<$limit} {incr i} {
set rowid [expr round(rand()*$limit)]
db onecolumn {select NULL from test where id=$rowid}
}
}]
puts "\nselect transaction $limit rows"
puts [time {
db transaction {
for {set i 0} {$i<$limit} {incr i} {
set rowid [expr round(rand()*$limit)]
db onecolumn {select value from test where id=$rowid}
}
}
}]
puts "\nselect transaction without extract $limit rows"
puts [time {
db transaction {
for {set i 0} {$i<$limit} {incr i} {
set rowid [expr round(rand()*$limit)]
db onecolumn {select NULL from test where id=$rowid}
}
}
}]
====================================
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
