Hi, nope. The defragmentation job runs every Wednsday night and the fragmentation is very low.
PS: I'm putting together a small program to replay the queries I got from sqlite trace. If that will behave the same way as the original, I'll post it here. -- Gruesse, Jakub -----Original Message----- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Scott Robison Sent: Dienstag, 25. August 2015 18:55 To: General Discussion of SQLite Database Subject: Re: [sqlite] Performance problems on windows On Aug 25, 2015 5:21 AM, "Jakub Zakrzewski" <jzakrzewski at e2e.ch> wrote: > > Hi, > > The slowdown is confirmed by one of our customers. He uses Win 2008 Server x64. I'm testing on Win7 x64. This might be obvious in which case my apologies for bringing it up but: are these systems demonstrating slowness perhaps heavily fragmented? Either the database is fragmented, other non-SQLite files required by the system, or especially the master file table? I've seen truly abysmal performance on such systems myself, depending on IO patterns. > > I'm sure that you're testing each release carefully. We do too. And we still have weird bugs ;) > > Thanks for interest. If I can provide any useful information, just > tell me, what you need. I cannot reveal the source code but some profiling results or SQL statements are not a secret. > > -- > Gruesse, > Jakub > > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto: sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Richard Hipp > Sent: Dienstag, 25. August 2015 03:19 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Performance problems on windows > > I don't have any clues. > > While most of our work happens on Linux, we do measure performance on Windows from one release to the next (see for example item 23-b on the most recent release check-list > https://www.sqlite.org/checklists/private/3081100/index#c23) and it > gets better from one release to the next. So I don't know why you are seeing a slowdown. > > How do you measure? > > On 8/24/15, Jakub Zakrzewski <jzakrzewski at e2e.ch> wrote: > > Hi All, > > > > I finally got a chance to upgrade SQLite for our product from > > ancient 3.7.16.2. Initial tests on Linux were very promising - > > ranging from 33% to even 300% (for one degenerated case) speed > > improvement. So far so good. > > Problems begun when I have tested it on Windows. Depending on test > > case the new version is up to 0.28x slower! In the course of > > investigation I have managed to improve the performance by adding > > few missing indexes but this has influenced both old and new > > versions so the relative performance with > > 3.8.11.1 is still like one third worse. > > > > I have tried doing it step-by-step: 3.7.16.2 -> 3.7.17.0 -> 3.8.0.0 > > and the results are quite surprising: > > 3.7.16.2 -> 3.7.17.0 : ~16% faster > > 3.7.17.0 -> 3.8.0.0 : ~26% slower > > 3.7.16.2 -> 3.8.0.0 : ~15% slower > > 3.7.16.2 -> 3.8.11.1 : ~28% slower > > > > We use SQLite as backend ("persistent storage") to an implementation > > of a state machine. The queries are rather simple and each of them > > is too fast to measure (SQLite performance timer only has resolution > > of > > milliseconds) in the profiler it looks like the actual filesystem > > functions were taking more time in new versions but that tells me nothing really. > > > > Is there something I can tweak? > > The page size is set to 4K (NTFS file system), synchonous is OFF, > > journal_mode=truncated; > > > > With 10K objects all versions seem to perform equally: test takes > > ~45s. For 100K objects it's already [m]:[s] > > 3.7.16.2 : ~10:55 > > 3.7.17.0 : ~09:30 > > 3.8.0.0 : ~12:46 > > 3.8.11.1 : ~15:08 > > > > I'm out of ideas here. Can someone help me with further investigation? > > > > -- > > Gruesse, > > Jakub > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > -- > D. Richard Hipp > drh at sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
