Re: [sqlite] Different behaviour of auxiliary data between 3.7.17 and 3.8.0

Yes, you are right. Thanks for the investigation. On Sun, Feb 9, 2014 at 11:54 PM, Richard Hipp wrote: > This behavior change is in response to ticket > http://www.sqlite.org/src/info/406d3b2ef9 - a diff across several check-ins > that makes this change can be seen here: > > >

Re: [sqlite] Free Page Data usage

Hi, I tried the same database I had and used a 2KB page size. It works much faster and also the pages are reused immediattly to the extent of 95%. If only the number of pages could be increased somehow. Does anyone think its practical to make the pageNo from a 32 bit int to a 64 bit Unsigned

Re: [sqlite] FW: Need Help with Golf Handicap Calculation

>I thought LIMIT did the same thing as TOP. >With both LIMIT (or TOP) and ORDER BY, my assumption (and my >experience) is that the result is sorted, and then the first NN are >returned. Correct. SELECT TOP ... is equivalent to SELECT ... LIMIT The rows returned are the first rows of the

Re: [sqlite] Memory-mapped I/O [was: SQLite 3.7.17 preview - 2x faster?]

On Sun, Feb 9, 2014 at 7:19 PM, Drake Wilson wrote: > Quoth "James K. Lowden" , on 2014-02-09 > 17:49:15 -0500: > > That's true, although it's not quite fair to say the filesystem cache > > isn't "coherent". It's just not the Linux implementation.

Re: [sqlite] FW: Need Help with Golf Handicap Calculation

I thought LIMIT did the same thing as TOP. With both LIMIT (or TOP) and ORDER BY, my assumption (and my experience) is that the result is sorted, and then the first NN are returned. tenholde -Original Message- From: sqlite-users-boun...@sqlite.org

Re: [sqlite] Memory-mapped I/O [was: SQLite 3.7.17 preview - 2x faster?]

Quoth "James K. Lowden" , on 2014-02-09 17:49:15 -0500: > That's true, although it's not quite fair to say the filesystem cache > isn't "coherent". It's just not the Linux implementation. FYI, the term "coherent" I interpret as being used in the specific technical

Re: [sqlite] FW: Need Help with Golf Handicap Calculation

On Sat, 8 Feb 2014 19:47:44 + Ed Tenholder wrote: > Query: SELECT MAX(ScoreDate),AVG((Score-Rating)*(113.0/Slope))*.96 > FROM (SELECT * FROM (SELECT * FROM (SELECT > ScoreDate,Score,Rating,Slope FROM Scores WHERE Player="Joe Smith" > ORDER BY ScoreDate ASC LIMIT 3)

Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

Op 9 feb 2014, om 20:50 heeft big stone het volgende geschreven: Hi, You're right,' recommands' is not in the code, but was not run anymore either. The "x" table cte definition should be removed, as the final request use only "q". Anyway, after all this algorithmic surgery, Postgresql

Re: [sqlite] Memory-mapped I/O [was: SQLite 3.7.17 preview - 2x faster?]

On Sun, Feb 9, 2014 at 5:49 PM, James K. Lowden wrote: > > I know portability is important to the SQLite project. Is this > particular issue a matter of manpower, know-how, or policy? > > The mmap functionality in SQLite is completely portable to OpenBSD now, since as

Re: [sqlite] Different behaviour of auxiliary data between 3.7.17 and 3.8.0

This behavior change is in response to ticket http://www.sqlite.org/src/info/406d3b2ef9 - a diff across several check-ins that makes this change can be seen here: http://www.sqlite.org/src/vdiff?from=b1b0de29fdf7de83=62465ecba7431e1d=1=25 Note that the behavior changes brings the implementation

Re: [sqlite] Memory-mapped I/O [was: SQLite 3.7.17 preview - 2x faster?]

On Sat, 8 Feb 2014 08:26:43 -0500 Richard Hipp wrote: > > > The memory-mapped I/O is only enabled for windows, linux, mac > > > OS-X, and solaris. We have found that it does not work on > > > OpenBSD, for reasons we have not yet been able to uncove; but as > > > a precaution,

Re: [sqlite] Different behaviour of auxiliary data between 3.7.17 and 3.8.0

Here you are: #include #include #include "sqlite3.h" static void reuseAuxDataCountFunc( sqlite3_context *ctx, int argc, sqlite3_value **argv ){ int *reuseAuxDataCount; int value; (void)argc; /* Unused parameter */ reuseAuxDataCount = (int*)sqlite3_get_auxdata(ctx, 0); if

Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

Hi, You're right,' recommands' is not in the code, but was not run anymore either. The "x" table cte definition should be removed, as the final request use only "q". Anyway, after all this algorithmic surgery, Postgresql was slower with the 'optimization' algorithm than with the brute force.

Re: [sqlite] Different behaviour of auxiliary data between 3.7.17 and 3.8.0

Can you provide an example program that omits the glib.h dependency? On Sun, Feb 9, 2014 at 10:50 AM, gwenn wrote: > Hello, > I am not sure but it seems there is a regression between versions > 3.7.17 and 3.8.0. > It's impacting custom/user declared function and auxiliary

Re: [sqlite] LIKE operator and collations

On 9 Feb 2014, at 6:05pm, Constantine Yannakopoulos wrote: > - You are assuming that 'z' is the higher order character that can appear > in a value. This is not the case; for instance greek characters have higher > order than 'z'. This can be fixed (only for latin/greek)

Re: [sqlite] LIKE operator and collations

Thank you all for your replies, @RSmith: My question is about the _specific_ case: SELECT * FROM ATable WHERE AColumn LIKE 'FixedSearchPart' || 'SomeWildCard' where the right side of the LIKE operator is a constant that has a fixed part at the beginning and a wildcard after that. The

Re: [sqlite] LIKE operator and collations

On 9 Feb 2014, at 4:21pm, Yuriy Kaminskiy wrote: > Unfortunately, builtin LIKE ignores collation, and if you override LIKE (as in > ICU extension), sqlite won't use index for optimization. However, this particular use of LIKE ... > SELECT * FROM ATable WHERE AColumn LIKE

Re: [sqlite] LIKE operator and collations

Constantine Yannakopoulos wrote: > I have a case where the user needs to perform a search in a text column of > a table with many rows. Typically the user enters the first n matching > characters as a search string and the application issues a SELECT statement > that uses the LIKE operator with

[sqlite] Different behaviour of auxiliary data between 3.7.17 and 3.8.0

Hello, I am not sure but it seems there is a regression between versions 3.7.17 and 3.8.0. It's impacting custom/user declared function and auxiliary data. sqlite-amalgamation-3071700 gwen$ gcc -I/usr/local/Cellar/glib/2.38.2/include/glib-2.0 -I/usr/local/Cellar/glib/2.38.2/lib/glib-2.0/include

Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

Op 9 feb 2014, om 13:19 heeft big stone het volgende geschreven: Congratulations, *E.Pasma* ! You indeed won the "brute force sudoku speed" competition for sqlite 3.8.3 by a factor of x2.5 on my PC ! D. Richard Hipp is still tuning the CTE implementation, so competition may be less

Re: [sqlite] LIKE operator and collations

On 2014/02/09 13:18, Constantine Yannakopoulos wrote: Hello all, I have a case where the user needs to perform a search in a text column of a table with many rows. Typically the user enters the first n matching characters as a search string and the application issues a SELECT statement that

Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

Congratulations, *E.Pasma* ! You indeed won the "brute force sudoku speed" competition for sqlite 3.8.3 by a factor of x2.5 on my PC ! D. Richard Hipp is still tuning the CTE implementation, so competition may be less slow with 3.8.4. To get clother to python speed

Re: [sqlite] LIKE operator and collations

On Sun, Feb 9, 2014 at 1:25 PM, Simon Slavin wrote: > store two text columns in the table. The first is the text as entered. > The second is your text reduced to its simplified searchable form, > probably all LATIN characters, perhaps using some sort of soundex. Search >

Re: [sqlite] LIKE operator and collations

On 9 Feb 2014, at 11:18am, Constantine Yannakopoulos wrote: > So, a full table scan seems inevitable. I can't answer the specific question you asked, but I have a suggestion for your program: store two text columns in the table. The first is the text as entered. The

[sqlite] LIKE operator and collations

Hello all, I have a case where the user needs to perform a search in a text column of a table with many rows. Typically the user enters the first n matching characters as a search string and the application issues a SELECT statement that uses the LIKE operator with the search string: SELECT *

Re: [sqlite] Free Page Data usage

On 9 Feb 2014, at 10:45am, RSmith wrote: > On 2014/02/09 12:06, Raheel Gupta wrote: >> Sir, I have only one auto increment primary key. >> Since the new rows will always have a higher number will the pages >> which have some free space by deleting rows with lower numbered

Re: [sqlite] Free Page Data usage

On 2014/02/09 12:06, Raheel Gupta wrote: Hi, Sir, I have only one auto increment primary key. Since the new rows will always have a higher number will the pages which have some free space by deleting rows with lower numbered keys never be reused ? e.g. If row with ROWID "1" was deleted and

Re: [sqlite] Free Page Data usage

Hi, Sir, I have only one auto increment primary key. Since the new rows will always have a higher number will the pages which have some free space by deleting rows with lower numbered keys never be reused ? e.g. If row with ROWID "1" was deleted and freed, will it not be used to store the NEW row