Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

On Dec 9, 2013, at 8:01 PM, Warren Young wrote: >> I remember reading an essay by a user of controlled substances > > Your next reading assignment is a book[3] on a functional programming > language, So… do you make your functions wear a purity ring? To keep them, hmmm,

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

On 12/7/2013 12:53, James K. Lowden wrote: On Thu, 05 Dec 2013 17:52:47 -0700 Warren Young wrote: To prove my point, I decided to divide the SQLite commands[1] into those that modify the DB and those that do not: Oh, let me help you out here: these aren't functions. I

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

On 12/7/2013 12:15, James K. Lowden wrote: On Wed, 04 Dec 2013 12:04:07 -0700 Warren Young wrote: Determinism is a property of a function; there is no such thing as a function that is sometimes deterministic and sometimes not. databases are about as far from

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

On Thu, 05 Dec 2013 17:52:47 -0700 Warren Young wrote: > To prove my point, I decided to divide the SQLite commands[1] into > those that modify the DB and those that do not: > > Has side effects Limited side effects No side effects > ~~

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

On Wed, 04 Dec 2013 12:04:07 -0700 Warren Young wrote: > > Determinism is a property of a function; there is no such > > thing as a function that is sometimes deterministic and sometimes > > not. > > databases are about as far from side-effect-free as you can get. I'm not

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

On 12/5/2013 14:45, Klaas V wrote: Warren wrote 4 dec 2013: | There are tiny corners of the programming world (FP) where this is not the case, but then you get into | questions of purity, and databases are about as far from side-effect-free as you can get. That's a wee bit exaggerated,

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

Warren wrote 4 dec 2013: | There are tiny corners of the programming world (FP) where this is not the case, but then you get into | questions of purity, and databases are about as far from side-effect-free as you can get. That's a wee bit exaggerated, but who am I to start a discussion;

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

On 12/3/2013 17:29, James K. Lowden wrote: Determinism is a property of a function; there is no such thing as a function that is sometimes deterministic and sometimes not. Unless you're new to this computing thing, you must have noticed that software developers almost never mean the same

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

On Tue, 3 Dec 2013 11:29:03 -0800 Scott Hess wrote: > Probably we should add a new API that allows the application > > to state auxiliary properties about application-defined functions > > (such as whether or not it is "constant", whether or not it can > > return NULL, whether

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

On 27-11-2013 03:55, James K. Lowden wrote: On Mon, 25 Nov 2013 19:44:15 +0100 Luuk wrote: On 25-11-2013 13:41, Simon Slavin wrote: I'm wondering whether there's an argument that it should be evaluated just once for a transaction. I'm still thinking about this question,

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

On Mon, 25 Nov 2013 19:44:15 +0100 Luuk wrote: > On 25-11-2013 13:41, Simon Slavin wrote: > > I'm wondering whether there's an argument that it should be > > evaluated just once for a transaction. > > I'm still thinking about this question, > > i can think of some benefits if

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

On 25-11-2013 13:41, Simon Slavin wrote: I'm wondering whether there's an argument that it should be evaluated just once for a transaction. I'm still thinking about this question, i can think of some benefits if its evaluated just once per transaction. but i hope anyone has some examples

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

If a function is deterministic for then it can be executed once and its result can be memoized for the rest of the statement (or transaction even). If a function is idempotent for a statement then it can be executed once per-statement, and its result(s) can be memoized and reused for the life of

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

On 25 Nov 2013, at 11:58am, Luuk wrote: > On 24-11-2013 19:36, Petite Abeille wrote: >> >> On Nov 24, 2013, at 7:10 PM, Valentin Davydov wrote: >> >>> Wait a second... and you'll get different value of datetime('now'). In this >>> sense datetime() is

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

On 24-11-2013 19:36, Petite Abeille wrote: On Nov 24, 2013, at 7:10 PM, Valentin Davydov wrote: Wait a second... and you'll get different value of datetime('now'). In this sense datetime() is as deterministic as random(): it may give the same result next invocation or

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

On Sun, 24 Nov 2013 16:53:01 +0200 RSmith wrote: > Similarly if one was to add a function which returns a date-dependant > value, such as 'dayOfWeek(x)' and mark it as deterministic for inside > a single query, that would make sense, even though it would be very >

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

On Sun, 24 Nov 2013 09:17:20 -0500 Doug Currie wrote: > in computer science we have referential transparency > > http://en.wikipedia.org/wiki/Referential_transparency_ > (computer_science) > > and pure functions > > http://en.wikipedia.org/wiki/Pure_function

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

On Nov 24, 2013, at 7:10 PM, Valentin Davydov wrote: > Wait a second... and you'll get different value of datetime('now'). In this > sense datetime() is as deterministic as random(): it may give the same result > next invocation or may not, dependng on various

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

On Sat, Nov 23, 2013 at 06:18:29AM -0500, Richard Hipp wrote: > On Sat, Nov 23, 2013 at 5:26 AM, Pepijn Van Eeckhoudt < > pep...@vaneeckhoudt.net> wrote: > > > Is datetime special in thuis context or will constant expression hoisting > > like this happen for any function? > > SQLite must know

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

On Sun, Nov 24, 2013 at 4:30 PM, Petite Abeille wrote: > > On Nov 24, 2013, at 3:17 PM, Doug Currie wrote: > > > There is value in compatibility, but those adjectives are awful. > > FWIW, DETERMINISTIC is what Oracle uses: > > >

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

Ugh, my last thought was not well-formed - apologies. When I said: "...can add a function to replace an SQL function to improve it many times for the specific purpose". This would of course hardly matter if the SQL (or SQLite specifically) function was already deterministic (read: cached). My

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

Agreed - also some functions might not be intrinsically deterministic, but it may well be so for the duration of a query. There may need to be some thinking on this. I refer back to a discussion earlier (and subsequent SQLite adaption) which made a date-time reference deterministic within a

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

On Nov 24, 2013, at 3:17 PM, Doug Currie wrote: > There is value in compatibility, but those adjectives are awful. FWIW, DETERMINISTIC is what Oracle uses: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/function.htm#LNPLS1183 I would personally stick to that if

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

On Nov 24, 2013, at 6:47 AM, Alek Paunov wrote: > > BTW, I see the term "deterministic" in the SQL99 BNFs: > … > but different in PostgreSQL ("immutable", "stable", etc): There is value in compatibility, but those adjectives are awful. In computer science we have

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

On 23.11.2013 13:18, Richard Hipp wrote: SQLite must know that the function always gives the same output given the same inputs. No every function works that way. Counterexamples include random() and last_insert_rowid(). But most built-in functions are factorable in the same way that

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

On Sat, Nov 23, 2013 at 5:26 AM, Pepijn Van Eeckhoudt < pep...@vaneeckhoudt.net> wrote: > Is datetime special in thuis context or will constant expression hoisting > like this happen for any function? > SQLite must know that the function always gives the same output given the same inputs. No

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

Is datetime special in thuis context or will constant expression hoisting like this happen for any function? Pepijn > Op 22-nov.-2013 om 15:35 heeft Richard Hipp het volgende > geschreven: > > The www.sqlite.org server logs are stored in an SQLite database (of > course). We