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 <dav...@mylollc.com> 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
