Re: [sqlite] Crazy performance difference between Mac and Windows
On Sep 23, 2008, at 12:06 AM, Robert Simpson wrote: >> >> -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 >> >> 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. > > 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. You seem to be right. Wrapping the batches of inserts in a transaction took the Mac from 22s to 6s...and Windows from 11m to 20s! With journaling enabled and no other cache changes on XP. (Though I'm keen to dive further into the Windows-specific suggestions Roger Binns posted and see what more they do.) Thanks for posting the catalyst that made me finally figure out how to do transactions with the ORM library. :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crazy performance difference between Mac and Windows
On Sep 23, 2008, at 12:06 AM, Robert Simpson wrote: >> >> -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 >> >> 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. > > 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. You seem to be right. Wrapping the batches of inserts in a transaction took the Mac from 22s to 6s...and Windows from 11m to 20s! With journaling enabled and no other cache changes on XP. (Though I'm keen to dive further into the Windows-specific suggestions Roger Binns posted and see what more they do.) Thanks for posting the catalyst that made me finally figure out how to do transactions with the ORM library. :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crazy performance difference between Mac and Windows
On Sep 22, 2008, at 11:17 PM, Roger Binns wrote: > Gavin Kistner wrote: >> 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. > > See points zero and five at > http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuningWindows Ah, that's excellent, thank you! I'll be sure to try a few of those tomorrow and see how they fare. Point five is not the issue (I'm using .db) but good to be aware of. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crazy performance difference between Mac and Windows
On Sep 22, 2008, at 11:03 PM, P Kishor wrote: > 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 Partially ignorance about that, and partially because I want to use the ORMs involved with the DB to ensure that all native Ruby types will be preserved in a way that lets them come back as proper native values. (e.g. booleans and Time values deserialzed by the ORM that originally wrote them and then re-serialized by the new ORM) >> 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. Sure, could be faster. For example, I just wrapped the batch inserts for every table in a transaction and now it's 6s instead of 22. But while good to be aware of silly things I'm doing that might speed up the overall result, the fact that it's 30x slower on Windows is what interested me. But thank you for your comments. >> 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. >> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crazy performance difference between Mac and Windows
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
Re: [sqlite] Crazy performance difference between Mac and Windows
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Gavin Kistner wrote: > 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. See points zero and five at http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuningWindows Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFI2Hv0mOOfHg372QQRAsnxAKDVR2TTViQDPNAFfNHMpBor6X7E0QCgucgc QCKvGv9EuxrBotxHFLBhwNE= =1col -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crazy performance difference between Mac and Windows
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
[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
