Re: [sqlite] Buffered stderr on Windows (Take 2)

On Fri, Jan 23, 2015 at 5:18 PM, Guilhem Malichier wrote: > I've been experiencing an issue with SQLite's CLI tool on Windows 7, when > used through a script or spawned process (not when used directly in the > console). > If i'm not mistaken, that was fixed just last week:

Re: [sqlite] TEMP tables with the same name as of main tables

On Jan 23, 2015, at 9:35 AM, Aldo Buratti wrote: > I had a bad programming experience with temporary tables and after some > googling I found this old post > > [sqlite] How to select from a temp table with same name as a main table. > dochsm Tue, 18 Jun 2013

Re: [sqlite] sqlite3 very slow even after creating without rowid

Hi, > I also did another experiment. I created this table and did a vaccum and then > the select count(*) in sqlite3 was around 2 mins. > > When I create an index manually after the table is loaded (imported from > csv), select count(*) in sqlite3 was within 30 to 40 secs. >In the second

[sqlite] Buffered stderr on Windows (Take 2)

[I got an unexpected _"message's content type was not explicitly allowed"_ bounce message after my first email, so I'm re-sending it in plain text. I apologize in advance for the subsequent lack of formatting, and am sorry for the double post if the first one actually reached you.] Hi, I've

[sqlite] sqlite3 very slow even after creating without rowid

Hello, My Process.csv is around 27G. I've gzipped it and put at ftp://navinps:sqlit...@h2.usa.hp.com as process.csv.gz There is only 1 file there. md5sum process.csv.gz e77a322744a26d4c8a1ad4d61a84ee72 process.csv.gz [root@centosnavin sqlite-autoconf-3080801]# cat sqlite3commands.txt

Re: [sqlite] sqlite3 very slow even after creating without rowid

Repost: Since it didn't get into the archives or in the mailing list. Sorry about that. Quoted and replied to simon after [Repost End] Hello, [Repost Begin] My Process.csv is around 27G. I've gzipped it and put at ftp://navinps:sqlit...@h2.usa.hp.com as process.csv.gz There is only 1 file

Re: [sqlite] sqlite3 performance on select count very slow for 16 GB file

If you have a table where rows are inserted but never deleted, and you have a rowid column, you can use this: select seq from sqlite_sequence where name = 'tablename' This will return instantly, without scanning any rows or indexes, and is much faster than max(rowid) for huge tables. If no rows

Re: [sqlite] Streaming group by in union of already sorted tables

Thank you very much, Richard. I'm sure this enhancement, modelled in a more general way, e.g., like an isSorted flag on the subquery to be used by the outer query, can be a great enhancement for many other types of queries employing nesting. I think it will help both in time and in space

Re: [sqlite] Having problems with Entity Framework code first db creation

This sounds like a problem with the connection string being passed to SQLiteConnection. Your attached package didn't come through, so could you send the connection string you're using? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]

Re: [sqlite] Streaming group by in union of already sorted tables

On 1/23/15, Emmanouil Karvounis wrote: > We have two tables that are already sorted on a combination of > two fields (which are their primary keys) and we want to union them and > apply group by on both the aforementioned fields, like so: > > select c1, c2, myagg(*) from ( >

Re: [sqlite] System.Data.SQLite - Exception Calling SQLiteModule.DeclareTable

In my specific case, I'm using virtual tables to hook up a non-sql data source to SQLite. Wanted to 'quote' column names to avoid issues with a column colliding with an sql keyword rather than avoiding use of strange characters. MikeN -Original Message- From:

Re: [sqlite] Streaming group by in union of already sorted tables

> > Sorry, but SQLite does not understand how the subquery (inside the > brackets) is going to be used by the main query. It hqs to complete the > subquery first and only then can it inspect the main query to find out how > to optimize it. This is not a bug, there just isn't enough flexibility

Re: [sqlite] Streaming group by in union of already sorted tables

On 23 Jan 2015, at 4:59pm, Emmanouil Karvounis wrote: > tableA and tableB have both primary key on (c1, c2) > > explain query plan > select c1, c2, count(*) from ( > select c1, c2 from tableA > union all > select c1, c2 from tableB > ) > group by c1,c2 > > 2|0|0|SCAN TABLE

Re: [sqlite] Streaming group by in union of already sorted tables

To explain that with an example: tableA (1,2) (2,3) (2,4) (3,5) tableB (1,2) (2,3) (4,6) Get a pointer on tableA and one on tableB. (1,2) and (1,2) form a group, run count(*) and output 2. Advance both pointers. (2,3) and (2,3) form a group, run count(*) and output 2. Advance both

Re: [sqlite] Streaming group by in union of already sorted tables

Dear Simon, Thank you for your answer and I'm sorry if I have used inappropriate wording and confused you. The issue is actually very simple: tableA and tableB have both primary key on (c1, c2) explain query plan select c1, c2, count(*) from ( select c1, c2 from tableA union all select c1, c2

Re: [sqlite] Streaming group by in union of already sorted tables

Thank you Clemens. In the general case, myagg() doesn't have the appropriate property to distribute over tableA and tableB seperately. Let me be clear, we are not looking for alternatives to get our query running more efficiently, we sincerely believe that we have found a defect in the sqlite

Re: [sqlite] Streaming group by in union of already sorted tables

Emmanouil Karvounis wrote: > In short, we have two tables that are already sorted on a combination of > two fields (which are their primary keys) and we want to union them and > apply group by on both the aforementioned fields, like so: > > select c1, c2, myagg(*) from ( > select * from tableA >

Re: [sqlite] Streaming group by in union of already sorted tables

On 23 Jan 2015, at 4:15pm, Emmanouil Karvounis wrote: > In short, we have two tables that are already sorted on a combination of > two fields There is no such thing as a 'sorted table' in SQL. Each table is a set of rows and the rows have no order. If you want to make it

[sqlite] Streaming group by in union of already sorted tables

Greetings, We are having an issue with the sqlite query plan for one of our queries, which seems to be sub-optimal both in time and in space complexity. In short, we have two tables that are already sorted on a combination of two fields (which are their primary keys) and we want to union them

Re: [sqlite] Having problems with Entity Framework code first db creation

On 2015/01/23 16:51, Walter Williams wrote: I'm trying to use a code first model ///... (snipped) then in the signature... "Do, or do not. There is no try." Thank you for the chuckle. As to the actual question, when you say "when I try

Re: [sqlite] sqlite3 very slow even after creating without rowid

On 23 Jan 2015, at 3:16pm, Parakkal, Navin S (Software Engineer) wrote: > I also did another experiment. I created this table and did a vaccum and then > the select count(*) in sqlite3 was around 2 mins. > > When I create an index manually after the table is loaded

[sqlite] TEMP tables with the same name as of main tables

I had a bad programming experience with temporary tables and after some googling I found this old post [sqlite] How to select from a temp table with same name as a main table. dochsm Tue, 18 Jun 2013 05:39:04 -0700 that illustrated exactly the same troubles. In short, if you have a table

Re: [sqlite] Core dump with shared cache enabled

On 22 Jan 2015 at 21:34, Daniel Roberts wrote: > Attached is a core file (sorry I don’t have symbols) as well as a copy of > the script I was running. Attachments are not allowed here. -- Cheers -- Tim ___ sqlite-users

[sqlite] Having problems with Entity Framework code first db creation

I'm trying to use a code first model using the System.Data.SQlite NuGet (v1.0.94.1) package and Entity Framework. I'm using VS 2013. I have defined my objects, but when I try to create a new database file using them, I get an error "Unable to complete operation. The supplied SqlConnection does

Re: [sqlite] Report a warning bug about Lemon parser

On 1/22/15, Tang Tianyong wrote: > Hi, yy_destructor function can not suppress warning about unused > %extra_argument variable. Sure it can. Just add code to one of your destructors that references the %extra_argument variable. It doesn't have to actually do anything with the

[sqlite] FW: Core dump with shared cache enabled

Hello, While doing some profiling in python, I ran into a crash in sqlite3. The workflow was pretty simple – one thread doing writes, another doing reads (of course, these are python threads, so it’s only “sort-of” concurrent). This workflow worked fine with the shared cache disabled, but when

[sqlite] Report a warning bug about Lemon parser

Hi, yy_destructor function can not suppress warning about unused %extra_argument variable. My yy_destructor function that Lemon generated like this: ``` static void yy_destructor( yyParser *yypParser,/* The parser */ YYCODETYPE yymajor, /* Type code for object to destroy */

Re: [sqlite] Huge WAL log

Thanks for the documentation update. >From my point of view I would invite more details related to the term "large transaction". Specifically the role of indexes is important. (They are often overlooked, added by an admin after the development is over etc.) > Defenses against this failure mode

Re: [sqlite] System.Data.SQLite - Exception Calling SQLiteModule.DeclareTable

In the 8.3 days, I routinely gave directories an underscore as a delimiter for version information, prior to my actually using a version control package. So "game" would be the main thing, and if I wanted to test, "game_1" became the new WIP folder. If I liked what I did, I'd move "game" to

Re: [sqlite] Compiling error on Cygwin on Windows 8.1: 3.8.7.4 and 3.8.8

2015-01-19 11:01 GMT+01:00 Frank Ho : > I compiled the SQLite on the Cygwin 1.7.33 running on a Windows 8.1, here's > the error: > > .libs/sqlite3.o: In function `sqlite3ThreadProc': > ../sqlite-autoconf-3080800/sqlite3.c:22471: undefined reference to > `_endthreadex' >

Re: [sqlite] Huge WAL log

On Thu, Jan 22, 2015 at 6:49 PM, Richard Hipp wrote: > Let me know if that helps. Note that I have only quickly read over my > writing so there is a high probability of typos, which I will be happy > to correct when brought to my attention. Thanks for the new doc. Very