Re: [sqlite] Memory usage when increasing SQLITE_MAX_VARIABLE_NUMBER to 32768

On Tuesday, 14 January, 2020 16:04, Simon Slavin wrote: >On 14 Jan 2020, at 10:56pm, Alexandre Doubov >wrote: >> 1) Does the act of bumping this limit up have an effect on memory at >all (assuming that no more than 999 arguments are passed into >statements)? >Section 9:

Re: [sqlite] Memory usage when increasing SQLITE_MAX_VARIABLE_NUMBER to 32768

On 14 Jan 2020, at 10:56pm, Alexandre Doubov wrote: > 1) Does the act of bumping this limit up have an effect on memory at all > (assuming that no more than 999 arguments are passed into statements)? Section 9: If I read this correctly, memory is reserved

[sqlite] Memory usage when increasing SQLITE_MAX_VARIABLE_NUMBER to 32768

Hello, I have a few questions with regards to memory impact when bumping the default SQLITE_MAX_VARIABLE_NUMBER limit from 999 to 32768. For reference, this is the issue that I created that brought me to this mailing list: https://github.com/requery/sqlite-android/issues/124 1) Does the act of

Re: [sqlite] Feature request: more robust handling of invalid UTF-16 data

On 1/14/20, Richard Hipp wrote: > I'm having trouble reproducing this. I went back to version 3.30.1 and I was able to reproduce it. So I bisected and found the following: https://sqlite.org/src/timeline?c=51027f08c0478f1b -- D. Richard Hipp d...@sqlite.org

Re: [sqlite] Feature request: more robust handling of invalid UTF-16 data

On 1/13/20, Dennis Snell wrote: > We have a JSON document like this which we store in a table. > > {“content”: “\ud83c\udd70\ud83c(null)\udd71”,”tags":[]} > > > The JSON is well-formed but the sequence of UTF-16 code points is invalid. > > When sqlite reads this data two types of further

Re: [sqlite] Capturing the changes in columns in a table

On 2020/01/14 7:10 PM, David Raymond wrote: A note and a question on subqueries. On reading: select ... (select b from t where a == new.a and idate < new.idate order by idate desc) as oldv, ... My brain started yelling that that needed a "limit 1" on the subquery so that it would only

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

On 1/14/20, Keith Medcalf wrote: > > I seem to recall something about "expensive" conditions that will be forced > to be run on only as few surviving candidate rows as possible, but my > recollection is vague (they say the memory is the second thing to go -- > strange I can't remember the first).

Re: [sqlite] Capturing the changes in columns in a table

Keith Medcalf, on Tuesday, January 14, 2020 11:49 AM, wrote... > > > On Tuesday, 14 January, 2020 09:03, Jose Isaias Cabrera > wrote: > > >That is an idea I have not thought about, Neal. Thanks. The boss comes > >up with lots of requests, and these have to be done yesterday. I will > >have to

Re: [sqlite] Capturing the changes in columns in a table

A note and a question on subqueries. On reading: select ... (select b from t where a == new.a and idate < new.idate order by idate desc) as oldv, ... My brain started yelling that that needed a "limit 1" on the subquery so that it would only return 1 row. I looked in the SQLite docs though

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

On Tuesday, 14 January, 2020 09:23, Simon Slavin wrote: >Would it be possible to phrase your SELECT as a SELECT with a sub-SELECT >? Have the sub-SELECT figure out which rows you want in which order, >then use a SELECT to apply your UDF to them ? It is guaranteed that the >sub-SELECT is

Re: [sqlite] Capturing the changes in columns in a table

On Tuesday, 14 January, 2020 09:03, Jose Isaias Cabrera wrote: >That is an idea I have not thought about, Neal. Thanks. The boss comes >up with lots of requests, and these have to be done yesterday. I will >have to look into triggers. Have not used them yet. :-( Here is some sample triggers

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

On 14 Jan 2020, at 4:14pm, Jean-Baptiste Gardette wrote: > The reason i asked this is that i have a query in wich one condition > filtering the recordset involves > an UDF and this UDF needs to be processed after all table filters have been > applied You cannot guarantee this. And even if

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

Thank you Dominic and Keith for your replies The reason i asked this is that i have a query in wich one condition filtering the recordset involves an UDF and this UDF needs to be processed after all table filters have been applied Illustration : additionnal table : CREATE TABLE t2 ( a TEXT

Re: [sqlite] Capturing the changes in columns in a table

sub sk79, on Tuesday, January 14, 2020 10:54 AM, wrote... > > > Yes, a dumb of a system is provided daily > > > There are some great solutions already here for offline processing as > stated in your question. However, something you might also want to > consider > (if you have requisite access to

Re: [sqlite] Feature request: more robust handling of invalid UTF-16 data

I want to second that. This leads to the situation that data is accepted by the database but there is no way to read that data back or more precisely I get the wrong (i.e. different) data back. I didn't check the suggested patch, but I don't believe it will work in all cases. I'd rather prefer

Re: [sqlite] Capturing the changes in columns in a table

> Yes, a dumb of a system is provided daily There are some great solutions already here for offline processing as stated in your question. However, something you might also want to consider (if you have requisite access to the system) is to use ‘after insert’ trigger(s) to online capture the

[sqlite] urifuncs.c access violation & crash

With the urifuncs extension enabled, the each of following SQL queries causes an access violation & crash: select sqlite3_filename_database(''); select sqlite3_filename_journal(''); select sqlite3_filename_wal(''); Ralf ___ sqlite-users mailing

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

On Tuesday, 14 January, 2020 06:58, Jean-Baptiste Gardette wrote: >Consider the following exemple : >CREATE TABLE t1 ( >a TEXT PRIMARY KEY, >b INTEGER); >SELECT * >FROM t1 >GROUP BY a >HAVING b > 1; >Will the GROUP BY clause be supressed and HAVING clause be rewritten in >WHERE clause by

Re: [sqlite] Capturing the changes in columns in a table

Keith Medcalf, on Tuesday, January 14, 2020 09:04 AM, wrote... > > > Assuming (a, idate) is indexed and unique, then give the following a > whirl on your larger data. It does the same thing but does not use window > functions to find the prior value -- it does a correlated subquery instead. > I

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

On Tue, Jan 14, 2020 at 2:57 PM Jean-Baptiste Gardette wrote: > SELECT * FROM t1 GROUP BY a HAVING b > 1; > > Will the GROUP BY clause be supressed and HAVING clause be rewritten in WHERE > clause by the optimizer ? My question would be why you wouldn't write it as a WHERE clause in the first

Re: [sqlite] Capturing the changes in columns in a table

Assuming (a, idate) is indexed and unique, then give the following a whirl on your larger data. It does the same thing but does not use window functions to find the prior value -- it does a correlated subquery instead. I would expect that it is slower with real data than the window function

Re: [sqlite] Best way to store key,value pairs

Another thing to consider is that RFC-822/1036 (et seq) headers are not inherently unique and some are repeated, especially since there's software that treats Usenet and Mail headers interchangeably. Also, debugging may require being able to see the exact layout of the headers as received. The

[sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

Hi, Consider the following exemple : CREATE TABLE t1 ( a TEXT PRIMARY KEY, b INTEGER); SELECT * FROM t1 GROUP BY a HAVING b > 1; Will the GROUP BY clause be supressed and HAVING clause be rewritten in WHERE clause by the optimizer ? Jean-Baptiste

Re: [sqlite] Capturing the changes in columns in a table

Jean-Luc Hainaut, on Tuesday, January 14, 2020 07:25 AM, wrote... > > > Another version that doesn't use CTE nor window functions: > > select t1.a as Proj, t2.idate as "On", 'b' as Var, t1.b as oldVal, t2.b > as newVal > from t t1,t t2 > where t1.a = t2.a > andt2.idate = date(t1.idate,'+1

Re: [sqlite] System.Data.SQLite Verson 111

I found the version 111 but it is still not working. I assume the problem is with the latest Visual Studio 2017 version. Earlier versions worked.   I installed Visual Studio 2015 Community. I made a test project.   EF 6.3 I couldn't correctly install because of an msbuild error. No idea what the

Re: [sqlite] Capturing the changes in columns in a table

Keith Medcalf, on Monday, January 13, 2020 08:03 PM, wrote... > > > And this version is several times faster since only the changes are > union'd which minimizes the total number of records processed. > The index should be "create index i on t (a, idate);" Because of the way > indexes work,

Re: [sqlite] Capturing the changes in columns in a table

R Smith, on Monday, January 13, 2020 06:49 PM, wrote... > > > On 2020/01/14 1:11 AM, Jose Isaias Cabrera wrote: > > R Smith, on Monday, January 13, 2020 05:25 PM, wrote...​ > >> > > ​ > > Wow! Thanks for this. I had not thought about your questions. My boss > said, I need to know all the

Re: [sqlite] Capturing the changes in columns in a table

Another version that doesn't use CTE nor window functions: select t1.a as Proj, t2.idate as "On", 'b' as Var, t1.b as oldVal, t2.b as newVal from t t1,t t2 where t1.a = t2.a andt2.idate = date(t1.idate,'+1 day') andt1.b <> t2.b union all select t1.a as Proj, t2.idate as "On",

Re: [sqlite] Best way to store key,value pairs

On Tue, Jan 14, 2020 at 9:35 AM Wout Mertens wrote: > On Mon, Jan 13, 2020 at 10:45 PM James K. Lowden > This is a trade-off between schema simplicity, storage layout and speed of > some operations. I'd > argue that in this particular case, a JSON field is beneficial for > simplicity, speed

Re: [sqlite] Best way to store key,value pairs

On Mon, Jan 13, 2020 at 10:45 PM James K. Lowden wrote: > You're making an efficiency argument here, or maybe > ease-of-implementation assertion. For me, inserting one header row or > 20 is the same coding effort (still need a loop). I think transaction > throughput would be about the same if