Re: [sqlite] Hints for the query planner

How about: maybe(COLUMN LIKE '%pattern%',.95) or (as percent using integer value in 0..100) maybe(COLUMN LIKE '%pattern%',95) with a default value of (possibly) 50% (or .5) for the optional second arg? -Original Message- From: Richard Hipp Sent: Tuesday, September 10, 2013 10:26 PM

Re: [sqlite] Question about aggregate functions used multiple times

James Powell wrote: > SELECT MAX(X) AS MaxX, MAX(X)/MIN(X) AS RatioX > > does the MAX(X) get calculated twice, or does SQLite identify that it > is the same number and do it only once? At least in version 3.8.0.2, it gets calculated only once. Please note that SQLite can optimize MIN/MAX

Re: [sqlite] Hints for the query planner

(6) maybe(EXPR) -Ursprüngliche Nachricht- Von: Richard Hipp [mailto:d...@sqlite.org] Gesendet: Dienstag, 10. September 2013 21:27 An: General Discussion of SQLite Database Betreff: [sqlite] Hints for the query planner There is a survey question at the bottom of this message. But first

Re: [sqlite] Hints for the query planner

I also think it should not be directly in the SQL. I like the not-really-a-comment syntax. Another option might be a few PRAGMAs, something like PRAGMA hint("table1.col1 IN (1,2,5)", 0.05); PRAGMA hint("table1.col2 LIKE '%bach%'". 0.4); these would add the hints to an internal table. When

[sqlite] problem with sqlite cache

Hello,I am asking you for ideas to resolve my problem. I create WinForm application in Visual Studio 2003. This app accessing the sqlite database by sqlite3.c and sqlite.h source code. Everything goes well, but after some changes (I dont know what I change) when I read from or write to database

Re: [sqlite] Hints for the query planner

> -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf > Of Richard Hipp > Sent: Tuesday, September 10, 2013 2:27 PM > To: General Discussion of SQLite Database > Subject: [sqlite] Hints for the query planner > > There is a

Re: [sqlite] Hints for the query planner

I suggest a verb to express what the function is actually doing, namely to reduce its argument in rank or degree for the query planner: DEGRADE 1. to reduce in worth, character, etc; disgrace; 2. to reduce in rank, status, or degree; remove from office; 3. to reduce in strength, quality,

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

On 10 sep. 2013, at 21:24, "E.Pasma" wrote: > My suppositions that the time was spent in the execute step and that this has > been fixed in the new release appeared both wrong. Thus I may be wrong again > but I think to have an explanation now. > It is as Simon guesses

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

On 10 sep. 2013, at 21:24, "E.Pasma" > wrote: My suppositions that the time was spent in the execute step and that this has been fixed in the new release appeared both wrong. Thus I may be wrong again but I think to have an explanation now. It is

Re: [sqlite] Hints for the query planner

On 11.09.2013 16:07, Ryan Johnson wrote: Perhaps you meant "demote" rather than "degrade" ? That would be a better fit (an external action that does not necessarily make the object worse or less useful), and less vague, but it still carries a negative connotation. "demote" sounds fine to me,

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

To get rid of the question of WHERE exactly the time is consumed, we did some profiling on the application that run the query (using the 1 tables test DB). As a result you will find an overview of time consumed per function (shown as percentage of the total time) at this link:

Re: [sqlite] Hints for the query planner

On Tue, Sep 10, 2013 at 3:26 PM, Richard Hipp wrote: > SURVEY QUESTION: > > The question for today is what to call this magic hint function: > > (1) unlikely(EXPR) > (2) selective(EXPR) > (3) seldom(EXPR) > (4) seldom_true(EXPR) > (5) usually_not_true(EXPR) (6)

Re: [sqlite] Hints for the query planner

On Wed, Sep 11, 2013 at 5:36 PM, Kevin Benson wrote: > On Tue, Sep 10, 2013 at 3:26 PM, Richard Hipp wrote: > > SURVEY QUESTION: > > (1) unlikely(EXPR) > > (2) selective(EXPR) > > (3) seldom(EXPR) > > (4) seldom_true(EXPR) > > (5)

Re: [sqlite] Hints for the query planner

On Tue, 10 Sep 2013 15:26:51 -0400, Richard Hipp wrote: >SURVEY QUESTION: > >The question for today is what to call this magic hint function: > >(1) unlikely(EXPR) >(2) selective(EXPR) >(3) seldom(EXPR) >(4) seldom_true(EXPR) >(5) usually_not_true(EXPR) > >Please feel free

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

Harmen de Jong - CoachR Group B.V. wrote: > http://www.coachrdevelopment.com/share/callstack_tree.html > > This shows most time is spend on sqlite3CodeRowTriggerDirect. I'd guess the actual culprit is the loop in getRowTrigger (which does not show up because it is inlined): /* It may be that

Re: [sqlite] Hints for the query planner

On Sep 10, 2013, at 6:23 PM, Scott Robison wrote: > I think I prefer something along the lines of "unlikely" or "likely". The > problem with a term like "selective" (at least in my brain) is that it > doesn't imply (for the single argument version) in what way it is

[sqlite] Clarification of overloading built-in funcs

Hi, all, i'm looking for a clarification on what is certainly a bit of pedantry on my part: http://www.sqlite.org/c3ref/create_function.html specifies that we can overload built-in funcs with UDFs: "Built-in functions may be overloaded by new application-defined functions." Does "overload"

Re: [sqlite] Clarification of overloading built-in funcs

If you call sqlite3_create_function_v2() with a function name that is the name of a built-in function, then the built-in function goes away and is replaced by your application-defined function. The original built-in function is no longer accessible. *Any* built-in function can be overloaded in

Re: [sqlite] Hints for the query planner

probability(EXPR, value) - Would force the user to set their best guess. I would think that the users guess would be more accurate than a general guess? What's considered unlikely for the users data, 0.05? 0.20? I would prefer if the mechanism could be handled with pragmas (or something)

[sqlite] Open SQLite database from string

Hi! My name Vlad, i need open SQLite database fro string... How can i do it? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS4 + INSERT OR REPLACE = Not replacing but adding item

Thanks Clemens, I actually removed fts4 now and replaced it with an index on the table. This way I can keep my old setup. Thanks for the help -- View this message in context: http://sqlite.1065341.n5.nabble.com/FTS4-INSERT-OR-REPLACE-Not-replacing-but-adding-item-tp71147p71195.html Sent from

Re: [sqlite] Open SQLite database from string

On 11 Sep 2013, at 2:45pm, apocello2008 wrote: > Hi! My name Vlad, i need open SQLite database fro string... How can i do it? What operating system ? What programming language ? What development environment ? Simon. ___

[sqlite] virtual table end of insert

In a virtual table, an insert of multiple rows calls xUpdate multiple times. How can I tell when the entire insert is complete? e.g., INSERT INTO myvirtualtable (token, rank) VALUES (..,..), (..,..),(..,..) will call xUpdate 3 times. But I would like to know when the whole thing is done so that

Re: [sqlite] virtual table end of insert

On Wed, Sep 11, 2013 at 8:16 PM, E. Timothy Uy wrote: > In a virtual table, an insert of multiple rows calls xUpdate multiple > times. How can I tell when the entire insert is complete? > I think the xRelease method of the virtual table object gets called when the statement

Re: [sqlite] virtual table end of insert

On 12 Sep 2013, at 1:16am, E. Timothy Uy wrote: > In a virtual table, an insert of multiple rows calls xUpdate multiple > times. How can I tell when the entire insert is complete? > > e.g., > INSERT INTO myvirtualtable (token, rank) VALUES (..,..), (..,..),(..,..) > > will

Re: [sqlite] virtual table end of insert

On 12 Sep 2013, at 1:27am, Simon Slavin wrote: > I suspect that in your place I would explicitly execute BEGIN and COMMIT, and > use xCommit to tell that the update was finished. Sorry, in case it's not obvious, ignore what I wrote and do whatever Dr Hipp wrote. He

Re: [sqlite] Clarification of overloading built-in funcs

Richard, I think the confusion is between OVERRIDE and OVERLOAD, and in what cases defining a function is an complete override of the function (and all its pre-existing overloaded implementations), and in what cases it is merely an OVERLOAD of the function name. And of course whether it is

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

On Tue, 10 Sep 2013 12:58:21 + Harmen de Jong - CoachR Group B.V. wrote: > I think the way I wrote our timings were not that clear, since they > are definately exponentially. The numbers from my previous post refer > to the multiplier between the test cases. Just to make

Re: [sqlite] Insert statement

On Mon, 9 Sep 2013 02:17:00 + "Joseph L. Casale" wrote: > > If I understand the question, and there is no key other than the > > auto-incrementing integer, there might not be a good way. It > > sounds like the database's design may have painted you into a > >