Looks to me like you've forgotten the biggest performance factor of all ... starting a transaction before you begin the loop and committing it afterwards.
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gavin Kistner Sent: Monday, September 22, 2008 9:45 PM To: General Discussion of SQLite Database Subject: [sqlite] Crazy performance difference between Mac and Windows (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.) 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. 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
