Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

On Sat, Feb 12, 2011 at 7:11 PM, Jim Wilcoxson wrote: > > > Unless I'm missing something, SQLite has to update the first page of the > database on every commit, to update the change counter. Assuming you are > using rotating media, that record can only be updated 120 times per

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

On Sun, Feb 13, 2011 at 12:27 AM, Marcus Grimm wrote: > So my brain isn't that lasy, we count 3 syncs :-) > > When you turn journaling to off you will most likely > see less syncs, probably 2 in your case. So that is all > in line.. > > Marcus > > Marcus, you're

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

On February 12, 2011, Black, Michael (IS) wrote: > Are you wrapping your data dump into a "BEGIN" "COMMIT"? Yup. > Also...you mention using a select...I assume you are testing first to see > if you need to update or insert? The select is not used when dumping data. The in memory data structure

Re: [sqlite] SQLite version 3.7.6 testing

>Your goals make a lot of sense. However I think you can do my second >suggestion. Compile with STAT2 code included by default, but make the >analyze command only build stat1 by default. > >This will result in no change in default behaviour, but means that anyone >wanting to use stat2 can

Re: [sqlite] SQLite version 3.7.6 testing

On Sat, Feb 12, 2011 at 1:21 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 02/12/2011 07:27 AM, Richard Hipp wrote: > > ... if SQLite is compiled using SQLITE_ENABLE_STAT2 ... > > Is there any reason this is not turned on by default? > > A

Re: [sqlite] SQLite version 3.7.6 testing

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/12/2011 02:13 PM, Richard Hipp wrote: >> But SQLITE_ENABLE_STAT2 breaks that guarantee. With STAT2, the ANALYZE >> command stores a histogram of left-most column of each index in order to >> help it make decisions about whether or not to use

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

Hi, it took me 6 hours to find the source location which behaves inconsistent. Please find attached a fix for this bug. Bye. -- Reinhard Nißl -Ursprüngliche Nachricht- Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] Im Auftrag von Nißl Reinhard Gesendet:

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

>> >> Interesting, I did a test on a 7200 file and the best I could do was 50 >> commits per second (a simple base/table with only id, journalling off >> and >> no >> extra code since the tool I use has "a repeated query" option with >> accurate >> timing). You mentioned 3 syncs per commit, but I

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

> On Sat, Feb 12, 2011 at 9:48 PM, Marcus Grimm > wrote: > >> > I should've realized it wasn't running this fast but the small 5000 >> record >> > size got me. >> > Test it yourself. >> > I do have a 7200RPM drive. My 261.4 numer is still 2+X your >> theoretical. >> >> I

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

> OK...I added your trigger example as option 8. And I had pasted the wrong > version in my last email. My timings were correct. Your example also did > sql_exec instead of using prepare so it will run slower. Yes, but that should be marginal. When I send my code the trigger version wasn't

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/12/2011 11:54 AM, Simon Slavin wrote: > Compensating for this behaviour is a big part of what SQLite does in > journaling. SQLite and other journalling mechanisms depend on an operating system call fsync that flushes their relevant data to

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

On Sat, Feb 12, 2011 at 9:48 PM, Marcus Grimm wrote: > > I should've realized it wasn't running this fast but the small 5000 > record > > size got me. > > Test it yourself. > > I do have a 7200RPM drive. My 261.4 numer is still 2+X your theoretical. > > I don't want to

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

OK...I added your trigger example as option 8. And I had pasted the wrong version in my last email. My timings were correct. Your example also did sql_exec instead of using prepare so it will run slower. I also made this compilable on Unix too. On Unix my timing matches the run time and

Re: [sqlite] SQLite version 3.7.6 testing

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/12/2011 11:40 AM, Simon Slavin wrote: > Does it make the ANALYZE command take significantly longer ? I don't think that matters. If someone runs ANALYZE it is because they are experiencing performance issues using default (non-analyzed) query

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

On 12 Feb 2011, at 4:11pm, Jim Wilcoxson wrote: > I don't understand how you can do 360K commits per second if your system is > actually doing "to the platter" writes on every commit. Can someone clue me > in? My field of expertise, I'm afraid. The answer is "Hard disks lie.". Almost all

Re: [sqlite] SQLite version 3.7.6 testing

On 12 Feb 2011, at 6:21pm, Roger Binns wrote: > On 02/12/2011 07:27 AM, Richard Hipp wrote: >> ... if SQLite is compiled using SQLITE_ENABLE_STAT2 ... > > Is there any reason this is not turned on by default? Does it make the ANALYZE command take significantly longer ? Simon.

Re: [sqlite] Questions about table optimization

On 12 Feb 2011, at 1:25pm, Gabriele Favrin wrote: > First of all a BIG thanks to the dev team of SQLite, it's an amazing > library, it helped me to enter in the world of SQL and I'm using it from > PHP on some small-medium sites. Thanks also to who takes time to explain > thing to people who

Re: [sqlite] Bi-directional unique

On Sat, 12 Feb 2011 19:14:51 +, Simon Slavin wrote: > >On 12 Feb 2011, at 11:43am, Kees Nuyt wrote: > >> There are just three cases: >> - i < j >> - i > j >> - i and j are the same. >> >> If j < i just swap the values. > > and given what real-world situation the data

Re: [sqlite] Bi-directional unique

On 12 Feb 2011, at 11:43am, Kees Nuyt wrote: > There are just three cases: > - i < j > - i > j > - i and j are the same. > > If j < i just swap the values. and given what real-world situation the data reflects, the third case can't happen. Simon.

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

> I fixed a couple of bugs in my program...I had converted from clock() to > the more appropriate gettimeofday and forgot to remove the CLOCKS_PER_SEC > factor (what's a few order of magnitude between friends :-). Plus I added > a 3rd argument so you can in-memory, index, and WAL mode too (or

Re: [sqlite] SQLite version 3.7.6 testing

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/12/2011 07:27 AM, Richard Hipp wrote: > ... if SQLite is compiled using SQLITE_ENABLE_STAT2 ... Is there any reason this is not turned on by default? If it is turned on and then the database is used by an earlier version of SQLite will there

Re: [sqlite] completion of sql words

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/11/2011 02:30 PM, Simon Slavin wrote: > Although you can download a command-line tool for sqlite3 from the sqlite3 > web site, it's provided just for convenience and many people don't use it (or > even know about it). On Unix platforms that

Re: [sqlite] SQLite version 3.7.6 testing

Please mark snapshots by pre-3.7.6 tag in fossil repository. How about compression in FTS extension? Is this production ready now? 12.02.2011 18:30 пользователь "Richard Hipp" написал: > The scheduled release of SQLite version 3.7.6 is mid-April. We are still > two months away.

Re: [sqlite] EXT :Re: UPDATE/INSERTing 1-2k rows slower than expected

> We already determined that triggers kill you on inserts so you can't use > triggers (other than as another example of what NOT to do for speed). that's why I added that in your test code... :-) Surprisingly I'm not able to reproduce a dramatic slowdown using my simple trigger test. It does

Re: [sqlite] EXT :Re: UPDATE/INSERTing 1-2k rows slower than expected

We already determined that triggers kill you on inserts so you can't use triggers (other than as another example of what NOT to do for speed). Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From:

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

I fixed a couple of bugs in my program...I had converted from clock() to the more appropriate gettimeofday and forgot to remove the CLOCKS_PER_SEC factor (what's a few order of magnitude between friends :-). Plus I added a 3rd argument so you can in-memory, index, and WAL mode too (or combine

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

> On Sat, Feb 12, 2011 at 9:10 AM, Black, Michael (IS) > > D:\SQLite>batch 5000 1 >> 360766.6 inserts per sec >> > > Unless I'm missing something, SQLite has to update the first page of the > database on every commit, to update the change counter. Assuming you are > using

Re: [sqlite] Questions about table optimization

Gabriele Favrin wrote: > The board is moderated, so any new message should be approved from admin. > I use the columns pub to determine messages that can be shown and new to > determine new messages (which by default have pub set to 0). This is > because changing a message

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

On Sat, Feb 12, 2011 at 9:10 AM, Black, Michael (IS) wrote: > Here's a little benchmark program I wrote to test a super-simple > one-integer insert to test sql speed and commit interval behavior. > > Running it on my machine (Windows XP64 8-core 3Ghz gcc (tdm64-1) 4.5.1)

[sqlite] SQLite version 3.7.6 testing

The scheduled release of SQLite version 3.7.6 is mid-April. We are still two months away. However, version 3.7.6 will contain a lot of pent-up changes and so your advance testing of this release will be appreciated. The latest pre-3.7.6 code can be found here:

Re: [sqlite] wal and shm files

>> Just wondering, are the wal and shm files suppose to stick around after the >> process exits? > > They should disappear when you call sqlite3_close() for the last handle. And if your process exits without calling sqlite3_close() then those files are supposed to stick around. Pavel

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

Are you wrapping your data dump into a "BEGIN" "COMMIT"? Also...you mention using a select...I assume you are testing first to see if you need to update or insert? You may want to look at the REPLACE clause and see if you can use that. Here's a little benchmark program I wrote to test a

Re: [sqlite] Speed up count(distinct col)

BareFeetWare-2 wrote: > > What is your source for the data? If I have that, I can test my schema > properly, rather than sitting here entering a pile of dummy data. I looked > at IMDB, but they only seem to have a paid license download. > I am grabbing the data from the each movie imdb

[sqlite] Questions about table optimization

Hi all, I'm new on this list. First of all a BIG thanks to the dev team of SQLite, it's an amazing library, it helped me to enter in the world of SQL and I'm using it from PHP on some small-medium sites. Thanks also to who takes time to explain thing to people who aren't really expert on db

Re: [sqlite] Hello.. I want to translate SQlite.org into Korean language version.

You should give your site the right charset. When I visit your page I get ¾Èµå·ÎÀ̵å¿Í iOS¿ë ¸ð¹ÙÀÏ ¾ÖÇø®ÄÉÀÌ¼Ç ¼Ò°³ everywhere. Also nice idea. Hope it works well for you and you get more people into using SQLite :) > Hello. > My name is Seungjin Kim. > I'm from Korea and my job is teaching

[sqlite] compile warning in tclsql 3.7.5

paul@paul-suse:~/build/TRUNK/external/build/sqlite-tea-3070500> make gcc -DPACKAGE_NAME=\"sqlite\" -DPACKAGE_TARNAME=\"sqlite\" -DPACKAGE_VERSION=\"3.7.5\" -DPACKAGE_STRING=\"sqlite\ 3.7.5\" -DPACKAGE_BUGREPORT=\"\" -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1 -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1

[sqlite] Hello.. I want to translate SQlite.org into Korean language version.

Hello. My name is Seungjin Kim. I'm from Korea and my job is teaching english for elementary students. My hobby is php programming because I was before php programmer. These day I programming on android and iOS. So I'm using SQLite now. I appreciate SQLite database. It can hleped my programs. If

Re: [sqlite] Bi-directional unique

On Wed, 9 Feb 2011 18:12:32 +, "Black, Michael (IS)" wrote: >I have a need to create a unique bi-directional relationship. >You can think of it as pairings of people who eat dinner together. > >create table t(i int, j int); > >insert into t(1,2); >insert into t(2,1);

Re: [sqlite] wal and shm files

On 12 Feb 2011, at 04:46 AM, Sam Carleton wrote: > Just wondering, are the wal and shm files suppose to stick around after the > process exits? They should disappear when you call sqlite3_close() for the last handle. Simon -- Sent while away from my computer.

Re: [sqlite] Feature suggestion for the Tcl interface : ability to use a list variable in combination with IN

Hi, A very nice extension - I'll look into that one for my integer-only lists, for sure. Thank you! /Fredrik 2011/2/10 Alexey Pechnikov : > See > http://sqlite.mobigroup.ru/wiki?name=ext_intarray_tcl > > 09.02.2011 17:49 пользователь "Fredrik Karlsson"

Re: [sqlite] Feature suggestion for the Tcl interface : ability to use a list variable in combination with IN

On Wed, Feb 9, 2011 at 6:15 PM, Eric Smith wrote: > Fredrik Karlsson wrote: > >> package require sqlite3 >> sqlite3 db :memory: >> db eval {create table a (id INTEGER);} >> db eval {insert into a values (1);} >> db eval {insert into a values (2);} >> db eval {select * from a