Re: [sqlite] random rows

I’m not entirely sure your solution will have an even distribution. It depends a lot on how many times random() is called (once per row vs once per sort operation), and how the sort algorithm works. I might do this instead: SELECT * FROM (SELECT random(), t.* FROM t) ORDER BY 1 LIMIT 200;

Re: [sqlite] random rows

hmm... i think: begin transaction; query 'select count(*) from tab' to get total number of records, name it N randomly select 200 integers from the range 0 to N-1 for each number query 'select * from tab limit 1 offset ?' with '?' bound to selected number end transaction; 2018-06-01 2:12

Re: [sqlite] Size of the SQLite library

On 31/05/18 10:15, Richard Hipp wrote: > Size is still important. But having useful features is important too. > I'm continuing to work to find the right balance between these > competing goals. A pattern used in other projects is to have standard downloads, as well as custom ones. With the

[sqlite] random rows

I need to get some random rows from a large(ish) table. The following seems to be the most straight forward - but not the fastest. SELECT * FROM table ORDER BY random() limit 200 Is there a faster/better approach? cheers, Torsten ___ sqlite-users

Re: [sqlite] Create Index after Data Populated

On 31 May 2018, at 8:59pm, Andrew Stewart wrote: >I have a database that is populated with data (and more being > added) and I have realized that I need an extra index to speed up access. > How long should it take to update the Index. There are about 1billion rows > in the

Re: [sqlite] Size of the SQLite library

>On Thursday, 31 May, 2018 10:19, Dominique Devienne said: >Given where the conversation is going, let me point out that many do >not care one bit about the lib's size :) >I'd much rather have an SQLite with tons of features, than forego >those in the name saving a few bytes, to save a few

[sqlite] Create Index after Data Populated

Hi, I have a database that is populated with data (and more being added) and I have realized that I need an extra index to speed up access. How long should it take to update the Index. There are about 1billion rows in the database Thanks, Andrew Stewart Software Designer

Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

SQL overall is phenomenally ambiguous about group-by: 1) select a0 + 1 as a0 from a group by a0 has a different behaviour in different SQL implementations, depending on whether the group-by prefers the underlying column or the projection alias. 2) As you rightly point out, group by integer

Re: [sqlite] Size of the SQLite library

I totally agree with that. On most systems it is much more important to have a feature-rich library than a very small one. Any application where a few bytes more or less matter should be written in pure assembler anyway. - Original Message - From: Dominique Devienne To: General

Re: [sqlite] Size of the SQLite library

On 5/31/18, Simon Slavin wrote: > > Did you know that less than half of SQLite installations are on desktop > computers ? My guess is that mobile phones are now the biggest category of > devices. They run off battery power. They have firmware on chips. The > more chips they have to keep

Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

At first I  wasn't sure what you meant by "column identifier". It's what the documentation calls "column-alias". But this also qualifies as an "other expression" doesn't it? A column alias can appear pretty much anywhere any expression can, except in the same column list where it is defined.

Re: [sqlite] SQLite is a LoC Preferred Format for datasets

Scott Robinson wrote: > What version of SQLite are you using for this? I just did the > following and do not see the string quoted values you are describing: I'm sorry, the example given was just the current one I was using to test the fix. In general I found the application was quoting all

Re: [sqlite] Size of the SQLite library

On 31 May 2018, at 5:18pm, Dominique Devienne wrote: > On Thu, May 31, 2018 at 3:44 PM Richard Hipp wrote: > >> For many years, we have boasted that the size of the SQLite library is >> "less than half a megabyte". > > Given where the conversation is going, let me point out that many do not >

Re: [sqlite] Size of the SQLite library

On May 31, 2018 12:18:51 PM EDT, Dominique Devienne wrote: >On Thu, May 31, 2018 at 3:44 PM Richard Hipp wrote: > >> For many years, we have boasted that the size of the SQLite library >is >> "less than half a megabyte". >> > >Given where the conversation is going, let me point out that many do

Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

Ponder the following select fragment SELECT name, year * 100 + month as period, day + hour / 24.0 from some_table Then you can have ORDER BY 1,2,3 -- integer output column numbers which is equivalent to ORDER BY name, period, day + hour / 24.0 -- output column identifier x2 + "any other

Re: [sqlite] Size of the SQLite library

On Thu, May 31, 2018 at 3:44 PM Richard Hipp wrote: > For many years, we have boasted that the size of the SQLite library is > "less than half a megabyte". > Given where the conversation is going, let me point out that many do not care one bit about the lib's size :) I'd much rather have an

Re: [sqlite] Size of the SQLite library

On Thu, May 31, 2018 at 11:38 AM, Richard Hipp wrote: > [...] > By using multiple SQLITE_OMIT compile-time options to leave out > features, I can get the size down to 308,189 bytes using gcc-7 -Os > -m32. @Richard can you elaborate some more on how you make this kind of a build? I wouldn't mind

Re: [sqlite] Size of the SQLite library

On 2018/05/31 5:17 PM, ven...@intouchmi.com wrote: I have to agree with Bob! We have considered SQLITE for our project. Going over 500Kbytes puts it just beyond the size of our Flash - the current Firmware. I stand corrected! It seems the embedded systems with still an extremely limited

Re: [sqlite] Size of the SQLite library

> > See https://sqlite.org/tmp/size-20180531.jpg for the library size > trend over 5 years. Maybe your graph should have three lines. 1. Minimum SQLite with all off 2. Default SQLite 3. Maximum SQLite with all on Sincerely Christian -- Read our blog about news on our plugi

Re: [sqlite] This is driving me nuts

Sqlite will use different strategies for ASC and desc ordering and result set sizes. Perhaps one is creating a temp btree to order the results. I think explain query plan might help show exactly what sqlite is contributing to the memory consumption without the need for as much speculation. Not

Re: [sqlite] Size of the SQLite library

On 5/31/18, ven...@intouchmi.com wrote: > > We have considered SQLITE for our project. Going over 500Kbytes puts it > just beyond the size of our Flash - the current Firmware. By using multiple SQLITE_OMIT compile-time options to leave out features, I can get the size down to 308,189 bytes

Re: [sqlite] This is driving me nuts

Yes, I think you’re right. I suppose maybe it recognises the desc sequence from page access. From: sqlite-users on behalf of Andy Ling Sent: Thursday, May 31, 2018 3:57:27 PM To: 'SQLite mailing list' Subject: Re: [sqlite] This is driving me nuts > While

Re: [sqlite] Size of the SQLite library

I have to agree with Bob! We have considered SQLITE for our project. Going over 500Kbytes puts it just beyond the size of our Flash - the current Firmware. Vance On 2018-05-31 11:04, Bob Friesenhahn wrote: > On Thu, 31 May 2018, R Smith wrote: > >> Nice idea, but to be honest, I can't

Re: [sqlite] Size of the SQLite library

On Thu, 31 May 2018, R Smith wrote: Nice idea, but to be honest, I can't remember when last someone cared about "Kilobytes", and I mean embedded people, not big OSes. I work on embedded projects and we do definitely worry about "kilobytes". This is even though our embedded projects have

Re: [sqlite] This is driving me nuts

> While that makes sense Clemens it again fails to explain why it does no > caching when desc is stepped through first. I am at least satisfied that it’s > not > something I should dwell on or, more the point, it isn’t down to a bug in my > code which is why I got embroiled in it in the first

Re: [sqlite] Size of the SQLite library

megabyte" might be a more accurate description of SQLite in practice. But the default configuration compiled with -Os is a good metric for comparison. See https://sqlite.org/tmp/size-20180531.jpg for the library size trend over 5 years. The measurements in the graph were done with gcc 5.4.0 and -O

Re: [sqlite] This is driving me nuts

While that makes sense Clemens it again fails to explain why it does no caching when desc is stepped through first. I am at least satisfied that it’s not something I should dwell on or, more the point, it isn’t down to a bug in my code which is why I got embroiled in it in the first place.

[sqlite] Size of the SQLite library

f SQLite in practice. But the default configuration compiled with -Os is a good metric for comparison. See https://sqlite.org/tmp/size-20180531.jpg for the library size trend over 5 years. The measurements in the graph were done with gcc 5.4.0 and -Os on ubuntu. As you can see, we have held th

Re: [sqlite] This is driving me nuts

On 31 May 2018, at 2:06pm, Clemens Ladisch wrote: > Windows has several different caching strategies. Also please note that the caching strategies change between different versions of Windows, sometimes even on what looks like a trivial update. Also, that drivers can request different

Re: [sqlite] This is driving me nuts

x wrote: > Why would window’s increase it’s cache size during step ascending then > reduce it during step descending in such a consistent manner? Windows has several different caching strategies. says: |

Re: [sqlite] This is driving me nuts

>While SQLite reads more and more rows from your database, Windows caches more >and more pages from the file, explaining the physical available memory >diminishes, which does not mean SQLite, nor your program 'use' it: the OS does. Olivier, your post makes perfect sense but the above begs the

Re: [sqlite] This is driving me nuts

> Le 31 mai 2018 à 12:31, x a écrit : > >std::cout << "Ascending" << std::endl; >for (int i=0; sqlite3_step(asc)==SQLITE_ROW; i++) >if (i%Gap==0) std::cout << FreeMBs() << std::endl; >std::cout << FreeMBs() << std::endl; > >std::cout << std::endl

Re: [sqlite] This is driving me nuts

> Le 31 mai 2018 à 13:34, x a écrit : > > Clemens, when I change to ullAvailVirtual the figure stays pretty much the > same throughout the programme as you suggested. I still don’t understand the > ullAvailPhys numbers though. Why would window’s increase it’s cache size > during step

Re: [sqlite] This is driving me nuts

Clemens, when I change to ullAvailVirtual the figure stays pretty much the same throughout the programme as you suggested. I still don’t understand the ullAvailPhys numbers though. Why would window’s increase it’s cache size during step ascending then reduce it during step descending in such a

Re: [sqlite] How does _exec() do a transaction ?

On 30 May 2018, at 2:24pm, Simon Slavin wrote: > [something] Thanks to the mods, but I realised I'd posted this from a wrong address and resubmitted it from the right address. Already asked and answered. Thanks, everyone. Simon. ___ sqlite-users

Re: [sqlite] This is driving me nuts

x wrote: > I’ve written the console app below to illustrate. It prints out the amount of > RAM available every 20 million steps. > GlobalMemoryStatusEx(); > return status.ullAvailPhys / (1024 * 1024); This is "physical memory currently available", which is affected by other

Re: [sqlite] This is driving me nuts

> I’ve just discovered the thread in the original app decreases the > available memory by around 4 GB. Are they really that expensive? After others pointed out threads weren’t that expensive I concluded there was a bug in my code. On checking though I couldn’t find anything wrong yet the

Re: [sqlite] Congratulations on 18 years

On Wed, May 30, 2018 at 6:16 PM jungle Boogie wrote: > On 30 May 2018 at 03:27, Christian Schmitz > wrote: > > Congratulations to the SQLite team. > > > > As far as I see, the first checkin was 2000-05-29, which was over 18 > years ago. > > Way to go! What a truly awesome project this has been!