Here's a trivial clojure script to invoke this behavior. It uses sql korma
to access the db, but the code should be obvious even if you don't know
this library.
The game here is to create a table, put some rows of varbinary data in it,
and then copy to another table.
(require 'korma.db)
(use 'korma.core)
(def file "testdb;LOG=1;CACHE_SIZE=65536")
(def score-size 400)
(def rows 1000)
(defn- scores-table-name [name]
(format "CREATE TABLE IF NOT EXISTS `%s` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`expScores` VARBINARY(%d) NOT NULL)" name score-size))
(def mydb
(korma.db/create-db
{:classname "org.h2.Driver"
:subprotocol "h2"
:subname file}))
(def one-score (byte-array (map byte (repeat 400 1)))) ; 400 bytes of 0x01
(korma.db/with-db
mydb
(do
(exec-raw (scores-table-name "scores"))
(time (doseq [_ (range rows)]
(exec-raw ["insert into scores (expScores) values (?)" [one-score]])))
(exec-raw (scores-table-name "scores2"))
(time (exec-raw "insert into scores2 select * from scores"))))
Some results, below. The value of "rows" (the row count) is returned on the
last line of each case.
> rm testdb*; lein exec -p < testscript
Sep 13, 2013 1:06:38 PM com.mchange.v2.log.MLog <clinit>
INFO: MLog clients using java 1.4+ standard logging.
"Elapsed time: 11522.797 msecs"
"Elapsed time: 347517.094 msecs"
(100000)
> rm testdb*; lein exec -p < testscript
Sep 13, 2013 1:13:13 PM com.mchange.v2.log.MLog <clinit>
INFO: MLog clients using java 1.4+ standard logging.
"Elapsed time: 2008.599 msecs"
"Elapsed time: 879.88 msecs"
(10000)
> rm testdb*; lein exec -p < testscript
Sep 13, 2013 1:13:35 PM com.mchange.v2.log.MLog <clinit>
INFO: MLog clients using java 1.4+ standard logging.
"Elapsed time: 329.684 msecs"
"Elapsed time: 195.243 msecs"
(1000)
So, between 10k rows and 100k rows the performance of the "insert into ..
select" goes from being around half the 1st table load time, to being
around 30x the 1st table load time.
On Friday, September 13, 2013 11:43:11 AM UTC-7, Brian Craft wrote:
>
> I could put together a test case in clojure. Do you want that?
>
> I'm not sure what it will tell you, since it's not the loader code that is
> slow.
>
> On Friday, September 13, 2013 10:08:39 AM UTC-7, Noel Grandin wrote:
>>
>> If you can post the test-case code, I can look at it on monday.
>>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
