warning: I know nothing about Ruby.
On 9/22/08, Gavin Kistner <[EMAIL PROTECTED]> wrote:
> (Right off the bat, let me say that I'm not sure if the problem here
> is Sequel, sqlite3-ruby, or
> sqlite. Just in case...)
>
> I have a Ruby script to migrate data from an old sqlite DB to a new
> schema. It's quite simple, selecting rows from db 1 and creating
> records in db 2. (A rough representation of the script is at the end
> of this post.)
I am assuming you have good reason to not just ATTACH the old db to
the new db and INSERT INTO new_table SELECT * FROM old_db.old_table
Seems tedious to use Ruby to do this.
>
> This script transforms a 2MB sqlite DB with about 5,000 rows into a
> 1.8MB sqlite DB with about the same number of rows. (A few fields and
> tables get dropped along the way.)
>
> On my mac laptop at home (2.3GHz Core 2 Duo, 2GB RAM, 5400 RPM drive)
> this script runs in 22 seconds. In 'better battery life' mode.
All that said, 22 seconds for a 5000 row db on that machine (same as
my laptop) seems rather slow to me.
>
> On my XP desktop at work (2GHz Dual Pentium, 2GB RAM, 7000 RPM drive)
> this same script on the same DB runs in 11 minutes. 30x slower.
>
> It's the same version of Ruby (1.8.6 p111), same version of sqlite3
> (3.6.2), sqlite3-ruby (1.2.3), and same version of Sequel (2.5.0). I
> know that the One-Click Installer of Ruby I'm using on XP isn't as
> fast as some other builds, but 30x slower seems crazy.
>
> If I turn off DB journaling on SQLite on Windows, I can get it down
> from 11 minutes to 4 minutes. Only
> 12x slower than the Mac. (But then, the Mac also runs faster without
> journaling.)
>
> The only funky thing that I can point to is that the script uses two
> different ORMs (sqlite3-ruby on db 1 and Sequel on db 2). I don't have
> a really good reason for this, it's just how it happened to have been
> written. If
> this slowdown ever becomes a big issue I could try Sequel for both and
> see if that helps in any way.
>
> Mostly I'm sharing this as a curiosity, though I'm quite interested if
> anyone has a suggestion on why
> this might be so much slower on a roughly equivalent machine differing
> only in OS.
>
> Here's (roughly) what the script looks like:
>
> require 'rubygems'
> require 'sqlite3'
> require 'sequel'
>
> olddb = SQLite3::Database.new( OLD_FILE )
> olddb.results_as_hash = true
>
> newdb = Sequel.sqlite( NEW_FILE )
> newdb << IO.read( NEW_SCHEMA )
>
> # Do the following sort of thing for about 10 different tables
> new_table = newdb[ :users ]
> olddb.execute "SELECT * FROM users" do |user|
> new_table << {
> :id => user['id'].to_i,
> :name => user['name'],
> :active => user['active']=='yes',
> :email => user['email']
> }
> end
>
> (As you might expect, this results in a SELECT from one DB followed by
> N independent un-transactioned INSERTs run on the other DB.)
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Puneet Kishor
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
