[sqlite] Ambiguous column name when using multiple JOIN USING clauses and WHERE clause

After being open for more than 2 years, this ticket (http://www.sqlite.org/src/tktview/3338b3fa19ac4abee6c475126a2e6d9d61f26ab1) was closed by Dr. Hipp with the comment: "The column name is ambiguous. Does it mean a.a or b.a? The result is the same either way, but I don't expect the parser to

Re: [sqlite] importing CSV data on command-line?

On Fri, Oct 14, 2011 at 7:37 AM, Simon Davies wrote: > On 14 October 2011 15:17, James Hartley wrote: > > When attempting to import CSV data, the shell balks... > > and look at what the separator is set to in the .show output... > I have got to

Re: [sqlite] Slow JOIN on ROWID

On Fri, Oct 14, 2011 at 12:46 PM, Fabian wrote: > 2011/10/14 Simon Slavin > > > > So, OP, try issuing these commands > > > > DROP TABLE sqlite_stat1; > > DROP TABLE sqlite_stat2; > > > > then see whether anything improves. > > > > > I already tried

Re: [sqlite] Slow JOIN on ROWID

On 10/14/2011 11:23 PM, Simon Slavin wrote: On 14 Oct 2011, at 5:12pm, Fabian wrote: Is this the expected output? If so, ANALYZE was to blame. The query containing 'ORDER BY rowid DESC' is still slower than the one which doesn't specify any order, but the results are closer to eachother now.

Re: [sqlite] Slow JOIN on ROWID

2011/10/14 Simon Slavin > > So that should never happen, right ? ANALYZE is meant to make things > faster, not slower. So is that an actual fixable bug or is it one of those > extremely unlikely situations that is hard to fix ? > > So, OP, try issuing these commands > >

Re: [sqlite] Slow JOIN on ROWID

On 14 Oct 2011, at 5:12pm, Fabian wrote: > Is this the expected output? If so, ANALYZE was to blame. The query > containing 'ORDER BY rowid DESC' is still slower than the one which doesn't > specify any order, but the results are closer to eachother now. So that should never happen, right ?

Re: [sqlite] How to use SQLite as a data source in Excel (tables and pivottables)

Hi Frank, You can take a look at my start-up file for excel 2000/2003: http://source.declera.com/excel/personal.xls (I am also attaching contained VBA module db.bas) With started personal.xls [1], one can open empty sheet named "sqlite", enter the path to sqlite database file in cell A1, and

Re: [sqlite] Slow JOIN on ROWID

> > Do you have an sqlite_stat1 table in the database (created by > running ANALYZE)? What is the output of the shell command > How about the contents of the "sqlite_stat1" table? What does > the shell command ".dump sqlite_stat1" show? > > This is the output with a fresh database, where ANALYZE

Re: [sqlite] Slow JOIN on ROWID

2011/10/14 Dan Kennedy > > Your EXPLAIN output shows that it is doing a linear scan of > table1. Which is different from what I get here with the same > schema and query. When I run them here, both queries (with and > without the "ORDER BY rowid") use the same query plan.

Re: [sqlite] Slow JOIN on ROWID

On 10/14/2011 10:13 PM, Fabian wrote: 2011/10/14 Dan Kennedy Good question. Can you enter the following commands into the shell tool and post the complete output (no "QUERY PLAN" this time): .version .schema .explain EXPLAIN SELECT * FROM table1 WHERE data=10

Re: [sqlite] Slow JOIN on ROWID

2011/10/14 Dan Kennedy > > Good question. Can you enter the following commands into > the shell tool and post the complete output (no "QUERY PLAN" > this time): > > .version > .schema > .explain > > EXPLAIN SELECT * FROM table1 WHERE data=10 ORDER BY rowid LIMIT 250

Re: [sqlite] Slow JOIN on ROWID

On 10/14/2011 09:34 PM, Fabian wrote: 2011/10/14 Dan Kennedy If SQLite cannot use an index to for an ORDER BY in a SELECT query and has to do an external sort, the EXPLAIN QUERY PLAN output will have something like this in it: 0|0|0|USE TEMP B-TREE FOR ORDER BY

Re: [sqlite] Slow JOIN on ROWID

On 14 Oct 2011, at 2:59pm, Dan Kennedy wrote: > On 10/14/2011 07:40 PM, Simon Slavin wrote: >> >> I'm sorry, I completely missed the 'data = 10' earlier. If you have an >> index on the data column then that's the index SQLite would used for that >> query. Once it has picked that index it no

Re: [sqlite] importing CSV data on command-line?

On 14 October 2011 15:17, James Hartley wrote: > When attempting to import CSV data, the shell balks at the command-line but > is otherwise fine when all is done interactively.  I suspect user error, but > I just don't see it. Any insight shared would be greatly appreciated.

Re: [sqlite] Slow JOIN on ROWID

2011/10/14 Dan Kennedy > > If SQLite cannot use an index to for an ORDER BY in a SELECT > query and has to do an external sort, the EXPLAIN QUERY PLAN > output will have something like this in it: > > 0|0|0|USE TEMP B-TREE FOR ORDER BY > > Since my EXPLAIN does not show

[sqlite] importing CSV data on command-line?

When attempting to import CSV data, the shell balks at the command-line but is otherwise fine when all is done interactively. I suspect user error, but I just don't see it. Any insight shared would be greatly appreciated. Thanks. $ ls create_table.sql src.csv $ cat create_table.sql src.csv

Re: [sqlite] Slow JOIN on ROWID

On 10/14/2011 07:40 PM, Simon Slavin wrote: On 14 Oct 2011, at 1:36pm, David Bicking wrote: On 10/14/2011 06:39 AM, Fabian wrote: When I execute: SELECT * FROM table WHERE data = 10 LIMIT 250 OFFSET 50 It's very fast, but it's get much slower (10 times) when I add an ORDER BY clause,

Re: [sqlite] Slow JOIN on ROWID

On Oct 14, 2011, at 3:37 PM, Fabian wrote: > 2011/10/14 Petite Abeille > >> >> Hurray! Now you must have the finest query ever to grace the intraweb! A >> true work of beauty :)) >> >> > Here in Belgium we have a saying: Who doesnt honor the petite, is not worth >

Re: [sqlite] Slow JOIN on ROWID

2011/10/14 Petite Abeille > > Hurray! Now you must have the finest query ever to grace the intraweb! A > true work of beauty :)) > > Here in Belgium we have a saying: Who doesnt honor the petite, is not worth the big ;) ___

Re: [sqlite] Slow JOIN on ROWID

On Oct 14, 2011, at 2:49 PM, Fabian wrote: > That explains everything! Hurray! Now you must have the finest query ever to grace the intraweb! A true work of beauty :)) ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Slow JOIN on ROWID

On 14 Oct 2011, at 1:49pm, Fabian wrote: > 2011/10/14 Simon Slavin > >> If you have an index on the data column then that's the index SQLite would >> used for that query. Once it has picked that index it no longer has access >> to the rowid index. >> >> CREATE INDEX tdr

Re: [sqlite] Slow JOIN on ROWID

2011/10/14 Simon Slavin > > If you have an index on the data column then that's the index SQLite would > used for that query. Once it has picked that index it no longer has access > to the rowid index. > > CREATE INDEX tdr ON table (data, rowid) > > Thanks! That explains

Re: [sqlite] Slow JOIN on ROWID

2011/10/14 Petite Abeille > > Much? Really? I get the broadly same execution time for either variant: > > 0|0|0|SCAN TABLE mail_header USING INTEGER PRIMARY KEY (~2192503 rows) > > 0|0|0|SCAN TABLE mail_header (~2192503 rows) > > I get SELECT mail_header.rowid FROM

Re: [sqlite] Slow JOIN on ROWID

On 14 Oct 2011, at 1:36pm, David Bicking wrote: > On 10/14/2011 06:39 AM, Fabian wrote: >> When I execute: >> >> SELECT * FROM table WHERE data = 10 LIMIT 250 OFFSET 50 >> >> It's very fast, but it's get much slower (10 times) when I add an ORDER BY >> clause, like rowid ASC or rowid DESC.

Re: [sqlite] Slow JOIN on ROWID

On 10/14/2011 06:39 AM, Fabian wrote: Exactly. I still don't have optimal performance in the query (although it's much better now), and it seems to be related to ORDER BY. When I execute: SELECT * FROM table WHERE data = 10 LIMIT 250 OFFSET 50 It's very fast, but it's get much slower

Re: [sqlite] Slow JOIN on ROWID

On Oct 14, 2011, at 12:39 PM, Fabian wrote: > I still don't have optimal performance in the query (although it's much > better now), and it seems to be related to ORDER BY. Yes, order by has a cost. > When I execute: > > SELECT * FROM table WHERE data = 10 LIMIT 250 OFFSET 50 > > It's

Re: [sqlite] How to use SQLite as a data source in Excel (tables and pivottables)

Hi Frank, > But I guess the best for now will be to just accept creating a DSN for each individual database and use the ODBC driver. Looks that is your best option then, yes. You could create DSN's in code via the Windows API. Can't see any great problem with that. RBS On Fri, Oct 14, 2011 at

Re: [sqlite] Slow JOIN on ROWID

On 14 Oct 2011, at 11:39am, Fabian wrote: > I still don't have optimal performance in the query (although it's much > better now), and it seems to be related to ORDER BY. > > When I execute: > > SELECT * FROM table WHERE data = 10 LIMIT 250 OFFSET 50 > > It's very fast, but it's get much

Re: [sqlite] Slow JOIN on ROWID

> > > Exactly. > > I still don't have optimal performance in the query (although it's much better now), and it seems to be related to ORDER BY. When I execute: SELECT * FROM table WHERE data = 10 LIMIT 250 OFFSET 50 It's very fast, but it's get much slower (10 times) when I add an ORDER BY

Re: [sqlite] getting value of last inserted primary key?

On Fri, Oct 14, 2011 at 1:44 AM, Petite Abeille wrote: > > On Oct 14, 2011, at 10:23 AM, James Hartley wrote: > > > Is > > there a way to save the value of last_insert_rowid() as a SQL statement? > > Sure. > > insert > into my_very_own_last_insert_rowid >

Re: [sqlite] DateTimeOffset in SQLite

No solution? :( Joe can you help perhaps? You help me so much with my other problem with the Entity Framework. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] getting value of last inserted primary key?

On Oct 14, 2011, at 10:23 AM, James Hartley wrote: > When dealing with tables with foreign constraints, how can the value of a > recently inserted primary key be propagated to multiple child tables? Is > there a way to save the value of last_insert_rowid() as a SQL statement? Sure. insert

[sqlite] getting value of last inserted primary key?

Okay, here's a few newbie questions. When dealing with tables with foreign constraints, how can the value of a recently inserted primary key be propagated to multiple child tables? Is there a way to save the value of last_insert_rowid() as a SQL statement? Lastly, shouldn't the last INSERT

Re: [sqlite] Can DBI supply a more specific foreign key mismatch error?

On Oct 14, 2011, at 6:28 AM, Jay A. Kreibich wrote: >> What I want to know is if there is any way to get more better >> reporting, such as the column or constraint it is upset about. > > No. As several have said. Well, as this very topic of meaningless error message comes back with a very

Re: [sqlite] Slow JOIN on ROWID

On Oct 13, 2011, at 10:57 PM, Fabian wrote: > Thank you very much! This approach solved the problem. However, in my > situation I need to select a lot more columns than just 'id' from > 'mail_header', Feel free to select all the relevant columns from the inner query. > and when I look at the