> So my next question is then, given that I have some data coming in, in > a random order, and I want an index. What can I do in order for the > performance to be better?
Create index only after all data is populated. BTW, this kind of questions will be answered best on a sqlite-users list. sqlite-dev is for internal issues related to SQLite development not to development using SQLite. Pavel On Thu, Jun 9, 2011 at 3:39 PM, Anders Rune Jensen <and...@iola.dk> wrote: > On Fri, Jun 10, 2011 at 12:05 AM, Michael Stephenson > <domehead...@gmail.com> wrote: >> Hi Anders, > > Hello Michael > >> I tried your test app. At first it crashed, until I increased the size of >> your temp buffer. >> >> I ran it with a high-performance timer around the commits, and they >> consistently took around 150 ms. I saw no degradation in performance as the >> app progressed. >> >> As an experiment, I added the following two lines right after the open >> (turning on WAL mode), and the average commit was then down to around 35 ms: >> >> sql = "pragma journal_mode = WAL;"; >> sqlite3_exec(db, sql, NULL, NULL, NULL); >> >> Is it possible you are experiencing a hardware I/O issue? > > Wow, that's quite a difference with WAL. > > I just realized that the different between the C# and the C version is > in the C version the numbers are inserted in order, and in the C# > version they are inserted randomly. I tried changing the C# version to > insert them in order and it worked fine as well. > > So my next question is then, given that I have some data coming in, in > a random order, and I want an index. What can I do in order for the > performance to be better? > > Thanks! > >> -----Original Message----- >> From: sqlite-dev-boun...@sqlite.org [mailto:sqlite-dev-boun...@sqlite.org] >> On Behalf Of Anders Rune Jensen >> Sent: Thursday, June 09, 2011 2:48 PM >> To: sqlite-...@sqlite.org >> Subject: [sqlite-dev] Index problem on Windows >> >> Hello >> >> Let me first start by saying that I have been a very happy user of sqlite >> for about 10 years now. I have used it in many different projects and have >> had absolutely no problems with it so far. >> >> I have run into a problem where the time to do a commit starts taking longer >> and longer. We are talking on the orders of 250ms for a table with ~ 20k >> lines and a disc size of around 2-3mb. I have tracked the performance >> problem down to something to do with indexing. It's almost as it is creating >> the index for every commit. The commit consists of >> 100 INSERTS. I have made a as small program as I could where I can reproduce >> the problem and have tried running this on Linux as well. >> Where the problem doesn't seem to occur. The problem exists with both WAL >> and truncate journaling mode. The problem doesn't seem to exist when I use a >> memory database instead of a file. I have tried both versionj 3.6.23.1 and >> 3.7.6.3. >> >> On Windows where I'm experiencing the problem I run sqlite in a C# program. >> I have checked the implementation of transaction support in the >> System.Date.Sqlite wrapper and it does absolutely nothing else than simply >> to a COMMIT. Sadly I don't have a C compiler for Windows so I can't check it >> when not running the wrapper, but it should be the same. >> >> Attached is a C# program that will demonstrate the problem and a C program. >> >> Thanks! >> >> -- >> Anders Rune Jensen >> http://www.iola.dk >> >> _______________________________________________ >> sqlite-dev mailing list >> sqlite-...@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev >> > > > > -- > Anders Rune Jensen > http://www.iola.dk > _______________________________________________ > sqlite-dev mailing list > sqlite-...@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
