Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

[David de Regt]

For the partition in question, the starting offset is 156,860,678,144 which 
divides evenly into 128k (131072).  So, doesn't look like the issue.  Also 
divides nicely into several further powers of 2 if the block size were smaller.

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Roger Binns [rog...@rogerbinns.com]
Sent: Friday, November 30, 2012 2:00 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 30/11/12 13:31, David de Regt wrote:
> Only possible agent is MSE,

MSE is the best behaved.  Norton and similar are especially bad.

> ... and process monitor doesn't show it eating IO

Sadly that rules out easy fixes :-)

> I tried changing block size to the native block size and it only sped
> up by ~5%.

Although that helps, I was talking about alignment.  This can happen at a
minor level - eg a small block size could be 4kb but the partition starts
at 63kb.  That means each filesystem block maps onto parts of two
different SSD/HDD blocks.  For SSDs there are also major block sizes
(erase block) which typically tended to be 128kb.  Again a misalignment
could cause a lot of extra work to be done.

Depending on how Windows got partitioned - the older the partitioning the
more likely this is to happen.  It won't shouldn't using a current Windows
7/8 on a fresh machine today.

Run msinfo32 and then Components > Storage > Disks to find the relevant
partition and its starting offset/alignment.

(This is unlikely to be your problem, but if present does result in the
kind of performance degradation you are seeing.)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlC5LIYACgkQmOOfHg372QQTsgCg1kUbwbwBnJOcenMHnFULGZe5
PqcAn30XBAT6extxig8Md7MI6XEtoHbi
=xYNE
-END PGP SIGNATURE-
___
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] Windows (slow) vs. iOS/OSX (fast) Performance

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 30/11/12 13:31, David de Regt wrote:
> Only possible agent is MSE,

MSE is the best behaved.  Norton and similar are especially bad.

> ... and process monitor doesn't show it eating IO

Sadly that rules out easy fixes :-)

> I tried changing block size to the native block size and it only sped
> up by ~5%.

Although that helps, I was talking about alignment.  This can happen at a
minor level - eg a small block size could be 4kb but the partition starts
at 63kb.  That means each filesystem block maps onto parts of two
different SSD/HDD blocks.  For SSDs there are also major block sizes
(erase block) which typically tended to be 128kb.  Again a misalignment
could cause a lot of extra work to be done.

Depending on how Windows got partitioned - the older the partitioning the
more likely this is to happen.  It won't shouldn't using a current Windows
7/8 on a fresh machine today.

Run msinfo32 and then Components > Storage > Disks to find the relevant
partition and its starting offset/alignment.

(This is unlikely to be your problem, but if present does result in the
kind of performance degradation you are seeing.)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlC5LIYACgkQmOOfHg372QQTsgCg1kUbwbwBnJOcenMHnFULGZe5
PqcAn30XBAT6extxig8Md7MI6XEtoHbi
=xYNE
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

[David de Regt]

Only possible agent is MSE, and process monitor doesn't show it eating IO, 
likely since it's not a watched extension.  I saw the article about the 
extensions a while ago, so we decided to use .s3db for our database extension.

I tried changing block size to the native block size and it only sped up by ~5%.

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Roger Binns [rog...@rogerbinns.com]
Sent: Friday, November 30, 2012 1:14 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 30/11/12 09:41, David de Regt wrote:
> Is there something ridiculous about the windows file system performance
> that hoses sqlite's open/read/write/close transaction cycle?

There are multiple possible confounding factors.  One is that you could
have tag alongs running - virus scanners, backup agents, content indexers
etc.  I strongly recommend running Sysinternal's Process Monitor which
will show file activity and point fingers.

Another is that Windows monitors files with certain extensions as part of
system restore.  If you happened to pick one of the extensions for your
database you'll find it a lot slower:

  http://msdn.microsoft.com/en-us/library/aa378870.aspx

There are some other tradeoffs in the NTFS implementation, such as how all
directory information is stored in a single large "file" (MFT).

It is also possible that the blocks of the filesystem don't align with the
blocks of the SSD which will cause the SSD performance to be a lot slower.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlC5IaoACgkQmOOfHg372QQjRgCdG4HCUkm9K/fRqIESJDfusiKG
WGQAni80PNqPHynWWYZxil1QRZmUEdZE
=nGIY
-END PGP SIGNATURE-
___
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] Windows (slow) vs. iOS/OSX (fast) Performance

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 30/11/12 09:41, David de Regt wrote:
> Is there something ridiculous about the windows file system performance
> that hoses sqlite's open/read/write/close transaction cycle?

There are multiple possible confounding factors.  One is that you could
have tag alongs running - virus scanners, backup agents, content indexers
etc.  I strongly recommend running Sysinternal's Process Monitor which
will show file activity and point fingers.

Another is that Windows monitors files with certain extensions as part of
system restore.  If you happened to pick one of the extensions for your
database you'll find it a lot slower:

  http://msdn.microsoft.com/en-us/library/aa378870.aspx

There are some other tradeoffs in the NTFS implementation, such as how all
directory information is stored in a single large "file" (MFT).

It is also possible that the blocks of the filesystem don't align with the
blocks of the SSD which will cause the SSD performance to be a lot slower.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlC5IaoACgkQmOOfHg372QQjRgCdG4HCUkm9K/fRqIESJDfusiKG
WGQAni80PNqPHynWWYZxil1QRZmUEdZE
=nGIY
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

[Simon Slavin]

On 30 Nov 2012, at 6:42pm, David de Regt  wrote:

> Windows 7/64 SP1.  Latest OSX (10.8).

Well, 10.8 is the more recent design and had been worked on by speed-freaks 
equipped with Apple's new MacBooks which come with SSDs.  But as you point out 
one shouldn't be ten times as fast as the other.

> I have, however, discovered WAL mode today, and that gave about an 8x speed 
> improvement (2500ms before, ~330ms after).  I'm still reading up on WAL to 
> see if it's fully safe through atomic transactions for power loss, but it 
> appears to be.  The funny thing, though, is that iOS sped up by almost the 
> same order of magnitude under WAL mode (280ms -> ~40ms, but was running into 
> timer resolution issues that low).  So, while this brings SQLite back into 
> the realm of acceptable performance under Windows for our usage (assuming I 
> can use WAL), which is great news for me, it's still very strange that it's 
> 8x slower than Apple-based...

WAL mode is fine.  Arguably more trustworthy than the old journaling style.  I 
would definitely consider WAL acceptable, not only on Windows but also on OS X. 
 The speed up for iOS is expected: WAL makes far better use of the way iDevice 
hardware is designed.

> TRUNCATE mode alone gave an almost 50% improvement on windows too, 
> hilariously enough (2500ms->1300ms), and almost zero benefit on iOS 
> (280ms->260ms).

Yep.  Designing from the ground up for solid state has its advantages.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

[David de Regt]

Windows 7/64 SP1.  Latest OSX (10.8).

The performance differential is definitely IO-related.  When I switch to 
wrapping everything in a transaction, the differential drops to ~1.5x (windows 
= 110ms, ios = 70ms, on a giant set of inserts).  So, it's something to do with 
the IO subsystem.  Michael Black has duplicated the speed issue with a simple 
test app he wrote doing my same basic inserts.

I have, however, discovered WAL mode today, and that gave about an 8x speed 
improvement (2500ms before, ~330ms after).  I'm still reading up on WAL to see 
if it's fully safe through atomic transactions for power loss, but it appears 
to be.  The funny thing, though, is that iOS sped up by almost the same order 
of magnitude under WAL mode (280ms -> ~40ms, but was running into timer 
resolution issues that low).  So, while this brings SQLite back into the realm 
of acceptable performance under Windows for our usage (assuming I can use WAL), 
which is great news for me, it's still very strange that it's 8x slower than 
Apple-based...

TRUNCATE mode alone gave an almost 50% improvement on windows too, hilariously 
enough (2500ms->1300ms), and almost zero benefit on iOS (280ms->260ms).

-David

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Friday, November 30, 2012 10:33 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

On 30 Nov 2012, at 5:41pm, David de Regt  wrote:

> Basic query set:
> CREATE TABLE test (col1 int, col2 text);
> [loop 500 times]: INSERT INTO TEST (col1,col2) VALUES (4,'test4')

I read with interest the figures you produced so far, though I have no 
explanation.  Can I ask which versions of Windows and OS X you're running ?

The most recent version of OS X (10.8 == Mountain Lion) is extremely efficient 
when addressing SSDs.  Not only does it implement TRIM but two levels of 
storage drivers have been rewritten to remove optimization assumptions which 
used to be valid with spinning drives but actually slow things down with SSDs.  
Although this mostly involves just removing extra code which no longer helps, 
this has made 10.8 extremely fast with SSDs which the OS correctly identifies 
as SSDs.

However, some work has gone into doing the same thing with Windows 7 and 
Windows 8.  But I know far less about low-level behaviour of Windows and don't 
know if the same things have been done.

iOS speed on a recent iDevice (iPad 2+, iPhone 4+) should be within a close 
order of magnitude to Mac speeds, which is what you're finding.  I see nothing 
unexpected in your iOS figures.

The figures you supplied are ... well, your word 'ridiculous' is as good as 
any.  Windows shouldn't be a tenth the speed of OS X.  No matter how much I 
despise Microsoft it's really not that bad.  Someone would have spotted 
something.  I'm wondering whether Windows is correctly enforcing 
in-order-writing whereas the other OSen aren't.  I predict that Linux times 
would be closer to OS X times than Windows times.

Simon.
___
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] Windows (slow) vs. iOS/OSX (fast) Performance

[Teg]

Hello David,

In /control panel/system/device manager/disk
drives/properties/Policies you can disable the windows "write cache
buffer flush" to the drive. I wonder if this would make a difference?

My machine is on a UPS so, I always disable it. I tend to disable it
on laptops too. I know it's probably not a real solution but, I wonder
if the lack of write buffering is why you see what you see.

C

Friday, November 30, 2012, 12:50:30 PM, you wrote:

DdR> Nope, I ran the tests both in Parallels and rebooting directly
DdR> into boot camp (basically native windows), and had essentially
DdR> identical performance (+/- 2%, within noise level differences). 
DdR> It also echoes the performance difference I'd been seeing on the
DdR> database side just watching the real app run on iOS and on my
DdR> other non-Apple native windows box.  Interesting little find,
DdR> nonetheless, thanks for that. :)

DdR> To Alex: Unfortunately, Windows is a core platform for us.  We
DdR> can't really just tell them to buzz off, so it's either figure
DdR> out how to improve SQLite performance or switch DB engines, at least on 
that platform...

DdR> -David
DdR> 
DdR> From: sqlite-users-boun...@sqlite.org
DdR> [sqlite-users-boun...@sqlite.org] on behalf of Black, Michael (IS) 
[michael.bla...@ngc.com]
DdR> Sent: Friday, November 30, 2012 9:46 AM
DdR> To: General Discussion of SQLite Database
DdR> Subject: Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

DdR> Could this be your problem?
DdR> 
http://mattgadient.com/2011/02/18/mac-os-x-slow-for-10-15-minutes-after-boot-the-fix/

DdR> Michael D. Black
DdR> Senior Scientist
DdR> Advanced Analytics Directorate
DdR> Advanced GEOINT Solutions Operating Unit
DdR> Northrop Grumman Information Systems

DdR> 
DdR> From: sqlite-users-boun...@sqlite.org
DdR> [sqlite-users-boun...@sqlite.org] on behalf of David de Regt 
[dav...@mylollc.com]
DdR> Sent: Friday, November 30, 2012 11:41 AM
DdR> To: General Discussion of SQLite Database
DdR> Subject: EXT :[sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

DdR> Hey all.  I've been struggling with a basic perf issue running
DdR> the same code on Windows vs. iOS and OSX.

DdR> Basic query set:
DdR> CREATE TABLE test (col1 int, col2 text);
DdR> [loop 500 times]: INSERT INTO TEST (col1,col2) VALUES (4,'test4')

DdR> I'm coding this using the default C amalgamation release and
DdR> using prepare/etc. on all platforms in the exact same way (same
DdR> very simple DB-access class I made).  I realize that using a
DdR> transaction around this would vastly improve perf, but given the
DdR> atomic nature of the app that this test is simulating, it won't
DdR> work to wrap it into transactions, so my goal is to improve the
DdR> atomic performance.  These are all being run on the same Macbook
DdR> Pro, with an SSD, running Windows via boot camp, OSX natively, and iOS via 
the iOS simulator:

DdR> With defaults (pragma sync = on, default journal_mode):
DdR> Windows: 2500ms
DdR> iOS: 300ms
DdR> OSX: 280ms

DdR> With pragma sync = off, journal_mode = memory:
DdR> Windows: 62ms
DdR> iOS: 25ms
DdR> OSX: 25ms

DdR> Turning off sync doesn't make me feel warm and fuzzy about our
DdR> lost-power scenario, so with sync on, it seems like something
DdR> must be fishy for it to be ~8-9x slower than the other platforms.
DdR> Is there something ridiculous about the windows file system
DdR> performance that hoses sqlite's open/read/write/close transaction
DdR> cycle?  Is there anything I can do, or just accept it and move
DdR> on?  With how that scales up, we may need to move to something
DdR> like using embedded MySQL or LocalDB on Windows to get the same
DdR> performance as we see with SQLite on other platforms, which seems quite 
ridiculous.

DdR> Thanks!
DdR> -David
DdR> ___
DdR> sqlite-users mailing list
DdR> sqlite-users@sqlite.org
DdR> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
DdR> ___
DdR> sqlite-users mailing list
DdR> sqlite-users@sqlite.org
DdR> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


DdR> ___
DdR> sqlite-users mailing list
DdR> sqlite-users@sqlite.org
DdR> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users




-- 
Best regards,
 Tegmailto:t...@djii.com

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

[Simon Slavin]

On 30 Nov 2012, at 5:41pm, David de Regt  wrote:

> Basic query set:
> CREATE TABLE test (col1 int, col2 text);
> [loop 500 times]: INSERT INTO TEST (col1,col2) VALUES (4,'test4')

I read with interest the figures you produced so far, though I have no 
explanation.  Can I ask which versions of Windows and OS X you're running ?

The most recent version of OS X (10.8 == Mountain Lion) is extremely efficient 
when addressing SSDs.  Not only does it implement TRIM but two levels of 
storage drivers have been rewritten to remove optimization assumptions which 
used to be valid with spinning drives but actually slow things down with SSDs.  
Although this mostly involves just removing extra code which no longer helps, 
this has made 10.8 extremely fast with SSDs which the OS correctly identifies 
as SSDs.

However, some work has gone into doing the same thing with Windows 7 and 
Windows 8.  But I know far less about low-level behaviour of Windows and don't 
know if the same things have been done.

iOS speed on a recent iDevice (iPad 2+, iPhone 4+) should be within a close 
order of magnitude to Mac speeds, which is what you're finding.  I see nothing 
unexpected in your iOS figures.

The figures you supplied are ... well, your word 'ridiculous' is as good as 
any.  Windows shouldn't be a tenth the speed of OS X.  No matter how much I 
despise Microsoft it's really not that bad.  Someone would have spotted 
something.  I'm wondering whether Windows is correctly enforcing 
in-order-writing whereas the other OSen aren't.  I predict that Linux times 
would be closer to OS X times than Windows times.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

[Black, Michael (IS)]

If you'd care to share your code I can test it on XP-64 and Windows 7 to see if 
I can duplicate your problem.



Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of David de Regt [dav...@mylollc.com]
Sent: Friday, November 30, 2012 11:50 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

Nope, I ran the tests both in Parallels and rebooting directly into boot camp 
(basically native windows), and had essentially identical performance (+/- 2%, 
within noise level differences).  It also echoes the performance difference I'd 
been seeing on the database side just watching the real app run on iOS and on 
my other non-Apple native windows box.  Interesting little find, nonetheless, 
thanks for that. :)

To Alex: Unfortunately, Windows is a core platform for us.  We can't really 
just tell them to buzz off, so it's either figure out how to improve SQLite 
performance or switch DB engines, at least on that platform...

-David

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Black, Michael (IS) [michael.bla...@ngc.com]
Sent: Friday, November 30, 2012 9:46 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

Could this be your problem?
http://mattgadient.com/2011/02/18/mac-os-x-slow-for-10-15-minutes-after-boot-the-fix/

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of David de Regt [dav...@mylollc.com]
Sent: Friday, November 30, 2012 11:41 AM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

Hey all.  I've been struggling with a basic perf issue running the same code on 
Windows vs. iOS and OSX.

Basic query set:
CREATE TABLE test (col1 int, col2 text);
[loop 500 times]: INSERT INTO TEST (col1,col2) VALUES (4,'test4')

I'm coding this using the default C amalgamation release and using prepare/etc. 
on all platforms in the exact same way (same very simple DB-access class I 
made).  I realize that using a transaction around this would vastly improve 
perf, but given the atomic nature of the app that this test is simulating, it 
won't work to wrap it into transactions, so my goal is to improve the atomic 
performance.  These are all being run on the same Macbook Pro, with an SSD, 
running Windows via boot camp, OSX natively, and iOS via the iOS simulator:

With defaults (pragma sync = on, default journal_mode):
Windows: 2500ms
iOS: 300ms
OSX: 280ms

With pragma sync = off, journal_mode = memory:
Windows: 62ms
iOS: 25ms
OSX: 25ms

Turning off sync doesn't make me feel warm and fuzzy about our lost-power 
scenario, so with sync on, it seems like something must be fishy for it to be 
~8-9x slower than the other platforms.  Is there something ridiculous about the 
windows file system performance that hoses sqlite's open/read/write/close 
transaction cycle?  Is there anything I can do, or just accept it and move on?  
With how that scales up, we may need to move to something like using embedded 
MySQL or LocalDB on Windows to get the same performance as we see with SQLite 
on other platforms, which seems quite ridiculous.

Thanks!
-David
___
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


___
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] Windows (slow) vs. iOS/OSX (fast) Performance

[David de Regt]

Nope, I ran the tests both in Parallels and rebooting directly into boot camp 
(basically native windows), and had essentially identical performance (+/- 2%, 
within noise level differences).  It also echoes the performance difference I'd 
been seeing on the database side just watching the real app run on iOS and on 
my other non-Apple native windows box.  Interesting little find, nonetheless, 
thanks for that. :)

To Alex: Unfortunately, Windows is a core platform for us.  We can't really 
just tell them to buzz off, so it's either figure out how to improve SQLite 
performance or switch DB engines, at least on that platform...

-David

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Black, Michael (IS) [michael.bla...@ngc.com]
Sent: Friday, November 30, 2012 9:46 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

Could this be your problem?
http://mattgadient.com/2011/02/18/mac-os-x-slow-for-10-15-minutes-after-boot-the-fix/

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of David de Regt [dav...@mylollc.com]
Sent: Friday, November 30, 2012 11:41 AM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

Hey all.  I've been struggling with a basic perf issue running the same code on 
Windows vs. iOS and OSX.

Basic query set:
CREATE TABLE test (col1 int, col2 text);
[loop 500 times]: INSERT INTO TEST (col1,col2) VALUES (4,'test4')

I'm coding this using the default C amalgamation release and using prepare/etc. 
on all platforms in the exact same way (same very simple DB-access class I 
made).  I realize that using a transaction around this would vastly improve 
perf, but given the atomic nature of the app that this test is simulating, it 
won't work to wrap it into transactions, so my goal is to improve the atomic 
performance.  These are all being run on the same Macbook Pro, with an SSD, 
running Windows via boot camp, OSX natively, and iOS via the iOS simulator:

With defaults (pragma sync = on, default journal_mode):
Windows: 2500ms
iOS: 300ms
OSX: 280ms

With pragma sync = off, journal_mode = memory:
Windows: 62ms
iOS: 25ms
OSX: 25ms

Turning off sync doesn't make me feel warm and fuzzy about our lost-power 
scenario, so with sync on, it seems like something must be fishy for it to be 
~8-9x slower than the other platforms.  Is there something ridiculous about the 
windows file system performance that hoses sqlite's open/read/write/close 
transaction cycle?  Is there anything I can do, or just accept it and move on?  
With how that scales up, we may need to move to something like using embedded 
MySQL or LocalDB on Windows to get the same performance as we see with SQLite 
on other platforms, which seems quite ridiculous.

Thanks!
-David
___
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


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

[Black, Michael (IS)]

Could this be your problem?
http://mattgadient.com/2011/02/18/mac-os-x-slow-for-10-15-minutes-after-boot-the-fix/

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of David de Regt [dav...@mylollc.com]
Sent: Friday, November 30, 2012 11:41 AM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

Hey all.  I've been struggling with a basic perf issue running the same code on 
Windows vs. iOS and OSX.

Basic query set:
CREATE TABLE test (col1 int, col2 text);
[loop 500 times]: INSERT INTO TEST (col1,col2) VALUES (4,'test4')

I'm coding this using the default C amalgamation release and using prepare/etc. 
on all platforms in the exact same way (same very simple DB-access class I 
made).  I realize that using a transaction around this would vastly improve 
perf, but given the atomic nature of the app that this test is simulating, it 
won't work to wrap it into transactions, so my goal is to improve the atomic 
performance.  These are all being run on the same Macbook Pro, with an SSD, 
running Windows via boot camp, OSX natively, and iOS via the iOS simulator:

With defaults (pragma sync = on, default journal_mode):
Windows: 2500ms
iOS: 300ms
OSX: 280ms

With pragma sync = off, journal_mode = memory:
Windows: 62ms
iOS: 25ms
OSX: 25ms

Turning off sync doesn't make me feel warm and fuzzy about our lost-power 
scenario, so with sync on, it seems like something must be fishy for it to be 
~8-9x slower than the other platforms.  Is there something ridiculous about the 
windows file system performance that hoses sqlite's open/read/write/close 
transaction cycle?  Is there anything I can do, or just accept it and move on?  
With how that scales up, we may need to move to something like using embedded 
MySQL or LocalDB on Windows to get the same performance as we see with SQLite 
on other platforms, which seems quite ridiculous.

Thanks!
-David
___
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] Windows (slow) vs. iOS/OSX (fast) Performance

[Alex Bowden]

It's not your problem.

If the idiot user wants a slow machine,  that's his choice.

It certainly isn't your job to turn off sync in order to hide how slow Windows 
is.

On 30 Nov 2012, at 17:41, David de Regt  wrote:

> Hey all.  I've been struggling with a basic perf issue running the same code 
> on Windows vs. iOS and OSX.
> 
> Basic query set:
> CREATE TABLE test (col1 int, col2 text);
> [loop 500 times]: INSERT INTO TEST (col1,col2) VALUES (4,'test4')
> 
> I'm coding this using the default C amalgamation release and using 
> prepare/etc. on all platforms in the exact same way (same very simple 
> DB-access class I made).  I realize that using a transaction around this 
> would vastly improve perf, but given the atomic nature of the app that this 
> test is simulating, it won't work to wrap it into transactions, so my goal is 
> to improve the atomic performance.  These are all being run on the same 
> Macbook Pro, with an SSD, running Windows via boot camp, OSX natively, and 
> iOS via the iOS simulator:
> 
> With defaults (pragma sync = on, default journal_mode):
> Windows: 2500ms
> iOS: 300ms
> OSX: 280ms
> 
> With pragma sync = off, journal_mode = memory:
> Windows: 62ms
> iOS: 25ms
> OSX: 25ms
> 
> Turning off sync doesn't make me feel warm and fuzzy about our lost-power 
> scenario, so with sync on, it seems like something must be fishy for it to be 
> ~8-9x slower than the other platforms.  Is there something ridiculous about 
> the windows file system performance that hoses sqlite's open/read/write/close 
> transaction cycle?  Is there anything I can do, or just accept it and move 
> on?  With how that scales up, we may need to move to something like using 
> embedded MySQL or LocalDB on Windows to get the same performance as we see 
> with SQLite on other platforms, which seems quite ridiculous.
> 
> Thanks!
> -David
> ___
> 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

[sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

[David de Regt]

Hey all.  I've been struggling with a basic perf issue running the same code on 
Windows vs. iOS and OSX.

Basic query set:
CREATE TABLE test (col1 int, col2 text);
[loop 500 times]: INSERT INTO TEST (col1,col2) VALUES (4,'test4')

I'm coding this using the default C amalgamation release and using prepare/etc. 
on all platforms in the exact same way (same very simple DB-access class I 
made).  I realize that using a transaction around this would vastly improve 
perf, but given the atomic nature of the app that this test is simulating, it 
won't work to wrap it into transactions, so my goal is to improve the atomic 
performance.  These are all being run on the same Macbook Pro, with an SSD, 
running Windows via boot camp, OSX natively, and iOS via the iOS simulator:

With defaults (pragma sync = on, default journal_mode):
Windows: 2500ms
iOS: 300ms
OSX: 280ms

With pragma sync = off, journal_mode = memory:
Windows: 62ms
iOS: 25ms
OSX: 25ms

Turning off sync doesn't make me feel warm and fuzzy about our lost-power 
scenario, so with sync on, it seems like something must be fishy for it to be 
~8-9x slower than the other platforms.  Is there something ridiculous about the 
windows file system performance that hoses sqlite's open/read/write/close 
transaction cycle?  Is there anything I can do, or just accept it and move on?  
With how that scales up, we may need to move to something like using embedded 
MySQL or LocalDB on Windows to get the same performance as we see with SQLite 
on other platforms, which seems quite ridiculous.

Thanks!
-David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database design preferences

[Duquette, William H (318K)]

On 11/30/12 8:34 AM, "Simon Slavin"  wrote:

>
>On 30 Nov 2012, at 3:50pm, Staffan Tylen  wrote:
>
>> I'm looking for both administrative and technical advice on the pros and
>> cons of either creating one single database table with many columns or
>> creating multiple tables with fewer but related columns to be JOINed
>>when
>> needed. Assume that the data is all related 1-to-1, like name, home
>> address, primary phone, shoe size, favourite politician (NULL
>>accepted!),
>> etc. At a first glance it seems logical to select a single table as it
>> simplifies access to the data but there may be good reasons that I'm not
>> aware of to split the data over multiple tables. I have only limited
>> experience of SQL so any guidelines are appreciated. Thanks in advance.
>
>
>Apart from that, SQLite is pretty efficient at 'wide' tables as long as
>you keep the column count below 30 or so.  There's really not that much
>to worry about and no need to do JOIN in cases where you have a 1-to-1
>relationship.

I use SQLite as a data store for a simulation model.  I have a number of
different kinds of simulation entity, and then many results that are
computed during simulation.  These results are often 1-to-1 with a given
kind of entity, but in practice I create one table for the entity
definitions, and separate tables for each kind of result.  This allows a
nice separation of concerns: I never need to puzzle over where a
particular column's value comes from.  It's either a scenario input, or
from the particular model with which the table is associated.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database design preferences

[Simon Slavin]

On 30 Nov 2012, at 3:50pm, Staffan Tylen  wrote:

> I'm looking for both administrative and technical advice on the pros and
> cons of either creating one single database table with many columns or
> creating multiple tables with fewer but related columns to be JOINed when
> needed. Assume that the data is all related 1-to-1, like name, home
> address, primary phone, shoe size, favourite politician (NULL accepted!),
> etc. At a first glance it seems logical to select a single table as it
> simplifies access to the data but there may be good reasons that I'm not
> aware of to split the data over multiple tables. I have only limited
> experience of SQL so any guidelines are appreciated. Thanks in advance.

You should be able to keep the schema of a table in your head at one time.  So 
no tables with 30 or more columns.  Finding a table with numbered columns, 
especially, is usually a sign of poorly designed schema.

There are speed and handling problems with tables with more than one BLOB 
column.  It's feasible, under some circumstances, that you might want to spin 
multiple-BLOBs-per-record into a second table, or keep only one BLOB in the 
first table.

Apart from that, SQLite is pretty efficient at 'wide' tables as long as you 
keep the column count below 30 or so.  There's really not that much to worry 
about and no need to do JOIN in cases where you have a 1-to-1 relationship.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database design preferences

[Black, Michael (IS)]

One of my considerations would be whether or not the fields are 1-to-1 to the 
user or are non-related.
In your list for example favorite politician is something non-related to the 
user and you might want to implement either as a search function or a pulldown 
list or a tabulated page.  So normalizing that to it's own table and putting a 
foreign key in your user table makes sense and would make maintenance easier 
(combining duplicate names and such) and GUI entry.

Name is relatively unique so leave it alone
Shoe size is just a byte so not worth normalizing and probably isn't queried 
much.
phone is unique and also not queried much.
address is mostly unique (several people at same address) so you wouldn't save 
much by normalizing.

Why normalize:
1. Query Performance
2. Data loading performance
3. Ease of maintenance
4. When data integrity is less of a concern (such as in read-only databases) 
and query performance is a higher priority 

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Staffan Tylen [staffan.ty...@gmail.com]
Sent: Friday, November 30, 2012 9:50 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Database design preferences

I'm looking for both administrative and technical advice on the pros and
cons of either creating one single database table with many columns or
creating multiple tables with fewer but related columns to be JOINed when
needed. Assume that the data is all related 1-to-1, like name, home
address, primary phone, shoe size, favourite politician (NULL accepted!),
etc. At a first glance it seems logical to select a single table as it
simplifies access to the data but there may be good reasons that I'm not
aware of to split the data over multiple tables. I have only limited
experience of SQL so any guidelines are appreciated. Thanks in advance.
Staffan
___
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

[sqlite] Database design preferences

[Staffan Tylen]

I'm looking for both administrative and technical advice on the pros and
cons of either creating one single database table with many columns or
creating multiple tables with fewer but related columns to be JOINed when
needed. Assume that the data is all related 1-to-1, like name, home
address, primary phone, shoe size, favourite politician (NULL accepted!),
etc. At a first glance it seems logical to select a single table as it
simplifies access to the data but there may be good reasons that I'm not
aware of to split the data over multiple tables. I have only limited
experience of SQL so any guidelines are appreciated. Thanks in advance.
Staffan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign keys needing an index

[Igor Tandetnik]

Simon Slavin  wrote:
> Need someone more familiar with the design philosophy and source code than I 
> am (which is not at all).
> 
> 
> 
> Says you need an index for anything which acts as a parent.  I can understand 
> why under normal circumstances (large table) this
> would be desirable.  But is there anything which would /require/ it ?

It's not the index that's necessary, but the UNIQUE constraint. It just so 
incidentally happens that SQLite uses indexes to enforce such a constraint.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign keys needing an index

[Jay A. Kreibich]

On Fri, Nov 30, 2012 at 02:57:30PM +, Simon Slavin scratched on the wall:
> Need someone more familiar with the design philosophy and source code than I 
> am (which is not at all).
> 
> 
> 
> Says you need an index for anything which acts as a parent.  I can
> understand why under normal circumstances (large table) this would
> be desirable.  But is there anything which would /require/ it ? 

  It isn't just an index, it is a UNIQUE index.  Parent columns must be
  unique, so a UNIQUE constraint (and the implicit index that comes with
  it) or an explicit UNIQUE index is required to enforce the uniqueness
  of the parent columns.
  
  Yes, it also happens to help with performance, but the primary
  motivation is to make sure FK references are unique.
  
> I'm asking this because I'm in a situation where someone may make a
> daughter table with a foreign key relationship and the code will not
> know whether the appropriate index already exists.  Either I can try
> to parse various pieces of information to figure it out, or I can
> have a rule that a new index is always created, and accept that this
> index may sometimes be a duplicate of one which already exists.

  In theory, if the database is designed correctly, an FK will never
  reference something that doesn't already have a UNIQUE constraint on
  it.  One might argue that if you're trying to setup an FK that
  references a column or set of columns that does not have a UNIQUE
  constraint, either the FK is broken or the parent table is broken.

  ...which is not to say a general purpose tool still needs to deal
  with this, as there are plenty of broken database designs out there.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Foreign keys needing an index

[Simon Slavin]

Need someone more familiar with the design philosophy and source code than I am 
(which is not at all).



Says you need an index for anything which acts as a parent.  I can understand 
why under normal circumstances (large table) this would be desirable.  But is 
there anything which would /require/ it ?  I know that the lookup is needed, 
and if there's no suitable index it will be slower, but is the fact that one is 
required an artefact of how SQLite3 works, or a 100% necessary part of foreign 
keys ?

I'm asking this because I'm in a situation where someone may make a daughter 
table with a foreign key relationship and the code will not know whether the 
appropriate index already exists.  Either I can try to parse various pieces of 
information to figure it out, or I can have a rule that a new index is always 
created, and accept that this index may sometimes be a duplicate of one which 
already exists.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Audit trail question...

[Igor Tandetnik]

André Wetzel  wrote:
> I'm quite new to SQLite. I wanted to add an audit trail by using the CREATE 
> TRIGGER statement. My problem is now that I want to
> log the changes together with the current user and timestamp. The current 
> user is a variable or a index to the user table.
> Because the TRIGGER statement is a stored procedure I need to find a way to 
> access this variable. Can I use a custom function or
> variable for this?

Yes, you should be able to use a custom function for this.

> How is this done?

http://sqlite.org/c3ref/create_function.html

-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FW: How to add SQLite SDK/dll to WP 8 project

[suresh P]

Hi, Could you please help me to fix my issue.
 
I am trying to do my first Windows phone 8 app with SQLite.  I installed the 
extension "SQLite for Windows phone". Then I tried to add its SDK references to 
the project. but I didn't find it (see pic2 attached). I restarted Visual 
Studio 2012 and tried. But no luck. 
 
Usually I should see "Windows" option in the left side in reference manager , 
from there, I can check required extensions. But, I am not seeing it. did I 
miss something here? 
 

 
Thanks,
Suresh 

 
 
  ___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Audit trail question...

[André Wetzel]

Hi all,

I'm quite new to SQLite. I wanted to add an audit trail by using the CREATE 
TRIGGER statement. My problem is now that I want to log the changes together 
with the current user and timestamp. The current user is a variable or a index 
to the user table. Because the TRIGGER statement is a stored procedure I need 
to find a way to access this variable. Can I use a custom function or variable 
for this? How is this done? Any other thoughts?

Thanks for your help,

André
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create table returns "database disk image is malformed" when disk is full and "SQLITE_IOCAP_ATOMIC" and "SQLITE_DEFAULT_AUTOVACUUM" is enabled

[Yongil Jang]

In btreeCreateTable, a new page is allocated by allocateBtreePage function
without any failures.
In normal case, this should be failed because of there is no space to write
journal file.
jrnlWrite function called from allocateBtree is passed by writing memory
block(no real file) in atomic write mode.

After finished of allocateBtreePage, ptrmapPut function called to update
the pointer-map and meta-data with the new root-page number.
Finally, this function is failed at sqlite3PagerWrite because of real file
write is called at this time.
But, the newly allocated page number in btree structure(or pager?) didn't
changed to original value.

In my opinion, issue point is updated page number is not returned to
previous value.
the allocateBtreePage function doesn't return disk full error and this
wrong value is remained until next query execution.
ptrmapPut function doesn't clear this page number.

If I turn off "atomic write" option, it works very well. (allocateBtreePage
function will return disk full error and page number will be replaced to
original value)

My question is how to replace page number in btree structure to original
value when failure is occurred after call of allocateBtreePage funciton.
Or... is there any better code for this?

Thank you.
Yongil Jang.



2012/11/26 Yongil Jang 

> Hi, there.
>
> I just found some illegal processing of SQLite.
> As I mentioned in title, "CREATE TABLE" query returns "database disk image
> is malformed" when disk is full and "SQLITE_IOCAP_ATOMIC" and
> "SQLITE_DEFAULT_AUTOVACUUM" is enabled.
> Here is my test scripts.
>
> sudo mkdir /mnt/db
> sudo chmod 777 /mnt/db
> sudo mount -t tmpfs -o size=16K tmpfs /mnt/db
> /home/yi.jang/git/sqlite_source/sqlite3 /mnt/db/test.db
>
> SQLite version 3.7.13 2012-06-11 02:05:22
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> pragma journal_mode=persist;
> persist
> sqlite> pragma page_size=4096;
> sqlite> create table a (id);
> sqlite> create table b (id);
> *Error: database or disk is full*
> sqlite> create table b (id);
> *Error: database disk image is malformed*
> sqlite>
>
> What I attached options to Makefile are as following.
>
> -DSQLITE_ENABLE_ATOMIC_WRITE
> -DSQLITE_DEFAULT_AUTOVACUUM=1
> -DSQLITE_DEFAULT_PAGE_SIZE=4096
>
> sqlite3.c is also changed.
>
> static int unixDeviceCharacteristics(sqlite3_file *id){
>   unixFile *p = (unixFile*)id;
>   if( p->ctrlFlags & UNIXFILE_PSOW ){
> return SQLITE_IOCAP_POWERSAFE_OVERWRITE | SQLITE_IOCAP_ATOMIC;
>   }else{
> return SQLITE_IOCAP_ATOMIC;
>   }
> }
>
> B.R.
> Yongil Jang.
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users