Re: [sqlite] Crazy performance difference between Mac and Windows

2008-09-23 Thread Gavin Kistner
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

2008-09-23 Thread Gavin Kistner
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

2008-09-23 Thread Gavin Kistner
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

2008-09-23 Thread Gavin Kistner
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

2008-09-22 Thread Robert Simpson
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

2008-09-22 Thread Roger Binns
-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

2008-09-22 Thread P Kishor
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

2008-09-22 Thread Gavin Kistner
(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