Re: [sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

Doh. I meant the linked document on the error log. Silly me. On September 11, 2017 9:41:39 PM EDT, "J. King" wrote: >There's an extra word in the first paragraph of Section 4 of that >document, by the way: > >" The error logger callback has

Re: [sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

There's an extra word in the first paragraph of Section 4 of that document, by the way: " The error logger callback has also proven useful in catching errors occasional errors that the application misses..." On September 11, 2017 11:22:50 AM EDT, Dan Kennedy wrote: >On

Re: [sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

On 09/10/2017 08:30 PM, R Smith wrote: Well yes but the documentation suggests that one could expect a slight degradation. The words "works best with" does not seem to imbue an idea of "give WITHOUT ROWID tables a wide berth when your tables are more than few columns wide", and I don't think

Re: [sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

Well yes but the documentation suggests that one could expect a slight degradation. The words "works best with" does not seem to imbue an idea of "give WITHOUT ROWID tables a wide berth when your tables are more than few columns wide", and I don't think the Devs intended that either. I can

Re: [sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

R Smith wrote: > I am using 151 columns for both tests. The only thing that changes > between the two scripts are the words "WITHOUT ROWID" being added says: | WITHOUT ROWID tables will work correctly ... for tables with a single | INTEGER PRIMARY KEY.

Re: [sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

On Sat, Sep 09, 2017 at 11:26:35PM +0200, R Smith wrote: > I think you are missing something or my explanation was not clear. > When I say "first test" I mean of THIS test suite, not the previous set from > 3 days ago. I meant the opposite. ___

Re: [sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

On 2017/09/09 9:20 PM, Nico Williams wrote: On Sat, Sep 09, 2017 at 05:56:03PM +0200, R Smith wrote: *Findings* pertaining to JOIN, INTERSECT and WHERE IN (...) type queries on tables *WITHOUT Row_ids*: (This is the full test posted below because it is the one that matters most) INTERSECT AND

Re: [sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

On Sat, Sep 09, 2017 at 05:56:03PM +0200, R Smith wrote: > *Findings* pertaining to JOIN, INTERSECT and WHERE IN (...) type queries on > tables *WITHOUT Row_ids*: > (This is the full test posted below because it is the one that matters most) > INTERSECT AND WHERE IN (...) queries posted similar

[sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

Full tests completed with findings ranging from less interesting to exposing a rather significant inefficiency in SQLite. I won't post all the tests because that would take far too much space, in stead I will simply discuss the experiment and findings and post the test script so that anyone

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

ell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of David Raymond >Sent: Thursday, 7 September, 2017 10:31 >To: SQLite mailing list >Subjec

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

rg] On Behalf Of R Smith Sent: Thursday, September 07, 2017 3:51 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] JOIN vs. INTERSECT speed and efficiency On 2017/09/07 6:31 PM, David Raymond wrote: > Although it may not translate as well to the more complex examples, would you >

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On Thu, Sep 07, 2017 at 09:51:07PM +0200, R Smith wrote: > INTERSECT will happily match however many columns you desire (and specify), > there is no need to match full records or single keys specifically. But the two queries on either side of the set operator must have the same number of columns

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On 2017/09/07 6:31 PM, David Raymond wrote: Although it may not translate as well to the more complex examples, would you also consider adding the IN operator to your tests? I found for example that "select v from t1 where v in t2;" did even better than the join or the intersect. Will do.

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

ry more complex/ugly... correct? -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Thursday, September 07, 2017 8:06 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On Wed, Sep 06, 2017 at 07:43:07PM -0600, Keith Medcalf wrote: > Try the same test using 147 columns in each table. > > 1 column is rather trivial. Even a kindergarten kid could do it in no > time using crayons and the wall. > > [...] > > In other words except in very trivial cases (like

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On 2017/09/07 3:43 AM, Keith Medcalf wrote: Try the same test using 147 columns in each table. Exactly the plan for this weekend :) 1 column is rather trivial. Even a kindergarten kid could do it in no time using crayons and the wall. So? That is non-sequitur, I am sure given enough

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

ite-users- >boun...@mailinglists.sqlite.org] On Behalf Of R Smith >Sent: Wednesday, 6 September, 2017 14:58 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] JOIN vs. INTERSECT speed and efficiency > >On 2017/09/06 8:26 PM, Nico Williams wrote: >> On Wed, Sep 06,

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On Wed, Sep 06, 2017 at 11:54:35PM +0200, R Smith wrote: > It's still remarkable that in both tests 5 and 6 I've used the very same PK > setup, yet Test 6 was significantly faster with the added ORDER BY clause. > In tests 1 through 4 I did not use a PK at all, just plain INT data field, > but

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On 2017/09/06 11:17 PM, Nico Williams wrote: If you'll redo this I'd urge you to use WITHOUT ROWIDS. First, that's almost always the right thing to do anyways. Second, it won't perform worse but likely will perform better. Third, write performance definitely should improve with WITHOUT

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On Wed, Sep 06, 2017 at 10:57:41PM +0200, R Smith wrote: > On 2017/09/06 8:26 PM, Nico Williams wrote: > >On Wed, Sep 06, 2017 at 08:05:57PM +0200, R Smith wrote: > >>-- Another interesting thing to note: The INTERSECT test produces ORDERED > >>-- output, which suggests that an ORDER-BY addition

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On 2017/09/06 8:26 PM, Nico Williams wrote: On Wed, Sep 06, 2017 at 08:05:57PM +0200, R Smith wrote: -- Another interesting thing to note: The INTERSECT test produces ORDERED -- output, which suggests that an ORDER-BY addition to the query would -- favour the INTERSECT method. Nothing about

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On Wed, Sep 06, 2017 at 08:05:57PM +0200, R Smith wrote: > -- Another interesting thing to note: The INTERSECT test produces ORDERED > -- output, which suggests that an ORDER-BY addition to the query would > -- favour the INTERSECT method. Nothing about INTERSECT requires it to produce ordered

[sqlite] JOIN vs. INTERSECT speed and efficiency

Hi all, For those interested, after a recent thread from a poster called Joe asking about the most efficient way to find values that coincide from two separate tables, a response from Clemens Ladisch and a further elaboration from myself suggested the following: SELECT v FROM t1 INTERSECT

Re: [sqlite] Join, Union, Subquery or what?

Am 06.09.2017 um 14:32 schrieb R Smith: These suggestions from Clemens will work exactly as you want, but I need to add that it assumes the records all perfectly match between the tables, even flags, ID column etc. This means, if it doesn't work as you expect, you can still use the exact

Re: [sqlite] Join, Union, Subquery or what?

Joe wrote: > my SQLite database has two tables Katalog and ZKatalog with the same > structure. One of the columns is called DDatum. What's the most efficient > way to > > (1) Select records, which are only in Katalog, but not in ZKatalog? SELECT * FROM Katalog EXCEPT SELECT * FROM ZKatalog; >

[sqlite] Join, Union, Subquery or what?

Hi, all, my SQLite database has two tables Katalog and ZKatalog with the same structure. One of the columns  is called DDatum. What's the most efficient way to (1) Select records, which are only in Katalog, but not in ZKatalog? (2) Select records, which are in Katalog and in ZKatalog? (3)

Re: [sqlite] Join of two virtual tables returns incorrect result set in 3.8.4.1

That was a fast turn-around. Thank you for addressing this issue so quickly! -- This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify

Re: [sqlite] Join of two virtual tables returns incorrect result set in 3.8.4.1

Trouble ticket for this problem here: http://www.sqlite.org/src/info/98825a79ce145686392d8074032ae54863aa21a3 On Thu, Apr 3, 2014 at 9:24 AM, Hinrichsen, John wrote: > I am posting my C repro for the virtual table join issue inline, below: > > /* > * This repro is

[sqlite] Join of two virtual tables returns incorrect result set in 3.8.4.1

I am posting my C repro for the virtual table join issue inline, below: /* * This repro is intended to demonstrate a possible bug when joining * two virtual table instances together: only a subset of the expected * rows is returned by sqlite. As far as we can tell, it only happens * when the

Re: [sqlite] Join of two virtual tables returns incorrect result set in 3.8.4.1

Attachments can't appear on this list. You can use a shared file service and post a link, or for smallish amounts of text use something like pastbin.com. On Wed, Apr 2, 2014 at 6:42 PM, Andy Goth wrote: > On 4/2/2014 4:52 PM, Hinrichsen, John wrote: > >> sqlite

Re: [sqlite] Join of two virtual tables returns incorrect result set in 3.8.4.1

On 4/2/2014 4:52 PM, Hinrichsen, John wrote: sqlite 3.8.4.1 can return an incorrect result when joining two virtual tables that are themselves based on underlying sqlite tables. This problem does not happen with sqlite 3.8.3.1 or earlier. Please see the attached repro. Attachment appears to

[sqlite] Join of two virtual tables returns incorrect result set in 3.8.4.1

sqlite 3.8.4.1 can return an incorrect result when joining two virtual tables that are themselves based on underlying sqlite tables. This problem does not happen with sqlite 3.8.3.1 or earlier. Please see the attached repro. -- This message contains confidential information and is intended

[sqlite] JOIN failure in sqlite 3.8.1

Hi, I noticed that some of my views were not working with sqlite 3.8.1. I managed to isolate the problem in the simple test case below. Then I discovered that the 3.8.2 pre-release seems to do it right. I suspect it was fixed along with https://www.sqlite.org/src/tktview?name=c620261b5b but I

Re: [sqlite] Join-source issue when using sub '(' join-source ')'

users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] De la part de Nico Williams Envoyé : lundi 19 mars 2012 16:10 À : General Discussion of SQLite Database Objet : Re: [sqlite] Join-source issue when using sub '(' join-source ')' On Mon, Mar 19, 2012 at 10:02 AM, TAUZIN Mathieu <mtau..

Re: [sqlite] Join-source issue when using sub '(' join-source ')'

On 19/03/2012 12:07 PM, TAUZIN Mathieu wrote: Thanks for your support ! SQL Ansi (and every major DB SqlServer,

Re: [sqlite] Join-source issue when using sub '(' join-source ')'

joins on the right of the joint-type are NOT OK. It seems like a bug to me. Mathieu -Message d'origine- De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] De la part de Jay A. Kreibich Envoyé : lundi 19 mars 2012 16:26 À : General Discussion of SQLite Database O

Re: [sqlite] Join-source issue when using sub '(' join-source ')'

On Mon, Mar 19, 2012 at 12:03:44PM +, TAUZIN Mathieu scratched on the wall: > Hi, > > According to the documentation on SELECT statements > http://www.sqlite.org/lang_select.html > It seems possible to write join chains as A join (B join C). > (using a '(' join-source ')' single-source ) > >

Re: [sqlite] Join-source issue when using sub '(' join-source ')'

On Mon, Mar 19, 2012 at 10:02 AM, TAUZIN Mathieu wrote: > Thanks, > > This syntax works but it is not documented... it looks like a short hand for > a subquery, interesting !. Join sources are like sub-queries. Look at the syntax. A sub-select specified in the join-source

Re: [sqlite] Join-source issue when using sub '(' join-source ')'

 : General Discussion of SQLite Database Objet : Re: [sqlite] Join-source issue when using sub '(' join-source ')' On 19 March 2012 13:05, Pavel Ivanov <paiva...@gmail.com> wrote: >> According to the documentation on SELECT statements >> http://www.sqlite.org/lang_select.html &g

Re: [sqlite] Join-source issue when using sub '(' join-source ')'

On 19 March 2012 13:05, Pavel Ivanov wrote: >> According to the documentation on SELECT statements >> http://www.sqlite.org/lang_select.html >> It seems possible to write join chains as A join (B join C). (using a '(' >> join-source ')' single-source ) > ... >> It seems that

Re: [sqlite] Join-source issue when using sub '(' join-source ')'

On 19 Mar 2012, at 12:51pm, TAUZIN Mathieu wrote: > Thanks for your response but my intent was to give rise to either an bug on > SQLite engine or an error (or maybe lack of precision) in the documentation. SQLite is fine. The documentation is accurate about what SQLite

Re: [sqlite] Join-source issue when using sub '(' join-source ')'

> According to the documentation on SELECT statements > http://www.sqlite.org/lang_select.html > It seems possible to write join chains as A join (B join C). (using a '(' > join-source ')' single-source ) ... > It seems that parsing is ok (no syntax error) but sources in the sub join > can't be

Re: [sqlite] Join-source issue when using sub '(' join-source ')'

. -Message d'origine- De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] De la part de Simon Slavin Envoyé : lundi 19 mars 2012 13:20 À : General Discussion of SQLite Database Objet : Re: [sqlite] Join-source issue when using sub '(' join-source ')' On 19 Mar 2012, at 12

Re: [sqlite] Join-source issue when using sub '(' join-source ')'

On 19 Mar 2012, at 12:03pm, TAUZIN Mathieu wrote: > Or without subjoin... > SELECT Orders.OrderID > FROM Customers > INNER JOIN Orders >ON Customers.CustomerID = Orders.CustomerID > LEFT OUTER JOIN InternationalOrders >ON Orders.OrderID =

[sqlite] Join-source issue when using sub '(' join-source ')'

Hi, According to the documentation on SELECT statements http://www.sqlite.org/lang_select.html It seems possible to write join chains as A join (B join C). (using a '(' join-source ')' single-source ) But on the well known NorthwindEF database this query ... SELECT Orders.OrderID FROM

Re: [sqlite] JOIN on between

On 1/11/2012 3:49 PM, Pawl wrote: select errorapi.*,login.* from errorapi JOIN login on (errorapi.start between login.start and login.ende) This command show only one end record. It is possible to use JOINS? I don't see anything wrong with the query. If it only reports one record, that must

[sqlite] JOIN on between

Hi, I need to join two table according to range of date. Fist table is error log table second is users{operators} login. I need to show complete table with all error with actual user id. CREATE TABLE [login] ( [id] iNTEGER NOT NULL, [start] TIMESTAMP DEFAULT (datetime('now','localtime'))

Re: [sqlite] JOIN vs IN

On Fri, Nov 11, 2011 at 8:06 PM, Petite Abeille wrote: >> No, "exists" in this case will change query plan significantly and >> performance can degrade drastically as a result. > > Why would that be? How would you rewrite the query using exists? The only thing I have in

Re: [sqlite] JOIN vs IN

Fabian wrote: Suppose I have two tables, and I want to have look for a value in the first table, but display the columns from the second table. The most obvious way would be joining them on rowid. But I don't need to SELECT any columns from the first table, and it's a FTS4 table (which always

Re: [sqlite] JOIN vs IN

On Nov 12, 2011, at 1:58 AM, Pavel Ivanov wrote: > No, "exists" in this case will change query plan significantly and > performance can degrade drastically as a result. Why would that be? ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] JOIN vs IN

On Fri, Nov 11, 2011 at 11:58 AM, Petite Abeille wrote: >> It returns the same results, but it doesn't seem much faster. Is there any >> performance difference to be expected from using IN instead of JOIN, or >> does SQLite internally rewrite JOIN queries to something

[sqlite] JOIN vs IN

Suppose I have two tables, and I want to have look for a value in the first table, but display the columns from the second table. The most obvious way would be joining them on rowid. But I don't need to SELECT any columns from the first table, and it's a FTS4 table (which always joins a bit slower

[sqlite] Join execution doubt

Hi! I'm studying the join execution... suppose a join with two tables without indexes.. is there any case at all where join will do a complete inner loop for each line in the outer loop? Because it seems it always build a temp table to autoindex the inner table.. thanks!

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

On Tue, Jan 18, 2011 at 10:13:10PM +0100, Florian Weimer wrote: > * Richard Hipp: > > > I don't think it makes sense in SQL (not just SQLite but SQL in > > general) for an aggregate query to return columns that are not in > > the GROUP BY clause. > > Isn't this just what PostgreSQL implements as

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

* Richard Hipp: > I don't think it makes sense in SQL (not just SQLite but SQL in > general) for an aggregate query to return columns that are not in > the GROUP BY clause. Isn't this just what PostgreSQL implements as DISTINCT ON? Then it *is* useful.

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

Dan, Richard, Igor, thanks for your input, and yes, it seems as if the gamble is no longer safe. Hopefully I'm the only one that has run into this side effect ;) :-David On 01/17/2011 04:57 PM, Igor Tandetnik wrote: > David Burström wrote: >> SELECT starttime,

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

David Burström wrote: > SELECT starttime, endtime from entry LEFT JOIN interval ON > interval.entryid = entry.id GROUP BY entry.id HAVING starttime = > MAX(starttime); The behavior of this statement is unspecified. In standard SQL, it is syntactically invalid - in a

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

X(starttime); Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of David Burström Sent: Mon 1/17/2011 9:37 AM To: General Discussion of SQLite Database Subject:

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

On Mon, Jan 17, 2011 at 7:47 AM, David Burström wrote: > Hello all! > > I stumbled across this strange bug during Android development on 2.2.1 > late last night. Please run the following snippet in SQLite 3.7.2 and > 3.6.22 to compare the differences. The comments shows

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

> -- if endtime is in a different position in the table, the query works > CREATE TABLE interval (endtime INTEGER, entryid INTEGER, starttime INTEGER); > CREATE TABLE entry (id INTEGER PRIMARY KEY AUTOINCREMENT); > > INSERT INTO entry (id) VALUES ( 42); > > INSERT INTO interval (endtime,

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

> Senior Scientist > Advanced Analytics Directorate > Northrop Grumman Information Systems > > > > > From: sqlite-users-boun...@sqlite.org on behalf of David Burström > Sent: Mon 1/17/2011 6:47 AM > To: sqlite-users@sqlite.org > Subject:

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

on behalf of David Burström Sent: Mon 1/17/2011 6:47 AM To: sqlite-users@sqlite.org Subject: EXTERNAL:[sqlite] JOIN bug in 3.7.2, not in 3.6.22 Hello all! I stumbled across this strange bug during Android development on 2.2.1 late last night. Please run the following snippet in SQLite 3.7.2

[sqlite] JOIN bug in 3.7.2, not in 3.6.22

Hello all! I stumbled across this strange bug during Android development on 2.2.1 late last night. Please run the following snippet in SQLite 3.7.2 and 3.6.22 to compare the differences. The comments shows what alterations you can make to make the query return the expected result. :-David

Re: [sqlite] join performance query

Let's try that again : expose the [number] column to the outer selection (** are for emphasis**): ( select id_song, **number** from ( select id_song, **number** from PLAYLIST_SONG where id_playlist=2 {and|or } number > 258 ) as MYPLAYLISTSONGS Regards Tim Romano On Tue, May 11, 2010

Re: [sqlite] join performance query

And you would put move your title-condition to the outer query: . . . ) as SONGIDLIST on SONG.id_song = SONGIDLIST.id_song where your title-condition and|or your title-number condition Regards Tim Romano ___ sqlite-users mailing list

Re: [sqlite] join performance query

Arrrgh, Google Chrome ate the top half of my reply. You must also expose the number column in the inner query against PLAYLIST_SONG; include your number-condition there and also specify the number column in the select-list: ( select id_song, number from ( select id_song from PLAYLIST_SONG

Re: [sqlite] join performance query

You could remove the title condition from the inner SONGS select, limiting your conditions to artist and genre; an index on column [artist] would make this subquery run quickly: ( select id_song from SONG where genre_id = 0 AND artist = 'Las ketchup' // AND title >= 'Asereje(karaoke

Re: [sqlite] join performance query

Sorry but in your solution, how can I solve the condition AND title >= 'Asereje(karaoke version)' AND (title > 'Asereje(karaoke >> version)' OR number > 258) ? title is on song and number is song_number on Playlist_Song AS PS. Furthermore I also need title and number in place of your select *

Re: [sqlite] join performance query

1. Try discrete single-column indexes rather than multi-column composite indexes. 2. Try breaking the query down into subsets expressed as parenthetical queries; you can treat these parenthetical queries as if they were tables by assigning them an alias, and then you can join against the aliases.

Re: [sqlite] join performance query

On 11 May 2010 11:07, Andrea Galeazzi wrote: > Hi guys, > I'm in a bind for a huge time consuming query! . . . > The second case is about 35 times slower... so the scrolling is quite > impossible (or useless)! > SELECT song_number AS number,title FROM Song AS S, Playlist_Song

[sqlite] join performance query

Hi guys, I'm in a bind for a huge time consuming query! I made the following database schema: CREATE TABLE Song ( idINTEGER NOT NULL UNIQUE, titleVARCHAR(40) NOT NULL DEFAULT '' COLLATE NOCASE, artistVARCHAR(40) NOT NULL DEFAULT ''

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

Gert Cuykens wrote: > select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0) onhand_qty > from PRODUCTS t left join ORDERS o on t.pid = o.pid group by t.pid > > Works also thanks. > So you do not believe the following has a performance penalty ? > > SELECT pid, txt,

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

On Wed, Sep 16, 2009 at 9:50 PM, Igor Tandetnik wrote: > Gert Cuykens > wrote: >> On Wed, Sep 16, 2009 at 8:26 PM, Igor Tandetnik >> wrote: >>> Gert Cuykens >>> wrote: On Wed, Sep 16, 2009 at 1:35

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

Gert Cuykens wrote: > On Wed, Sep 16, 2009 at 8:26 PM, Igor Tandetnik > wrote: >> Gert Cuykens >> wrote: >>> On Wed, Sep 16, 2009 at 1:35 AM, Igor Tandetnik >>> wrote: >> Perhaps your query could be

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

On Wed, Sep 16, 2009 at 8:26 PM, Igor Tandetnik wrote: > Gert Cuykens > wrote: >> On Wed, Sep 16, 2009 at 1:35 AM, Igor Tandetnik >> wrote: > >>> Perhaps your query could be a bit clearer when written this way: >>> >>> select

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

Gert Cuykens wrote: > On Wed, Sep 16, 2009 at 1:35 AM, Igor Tandetnik > wrote: >> Perhaps your query could be a bit clearer when written this way: >> >> select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0) >> onhand_qty from PRODUCTS t left

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

On Wed, Sep 16, 2009 at 1:35 AM, Igor Tandetnik wrote: > Gert Cuykens > wrote: >>   SELECT t.pid, >>          t.txt, >>          t.price, >>          t.qty - IFNULL(qs.qty_sold, 0) 'onhand_qty' >>     FROM PRODUCTS t >> LEFT JOIN (SELECT o.pid, >>    

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

Gert Cuykens wrote: > SELECT t.pid, > t.txt, > t.price, > t.qty - IFNULL(qs.qty_sold, 0) 'onhand_qty' > FROM PRODUCTS t > LEFT JOIN (SELECT o.pid, > SUM(o.qty) 'qty_sold' > FROM ORDERS o) qs ON qs.pid = t.pid >

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

009/9/15 Gert Cuykens : >   SELECT t.pid, >          t.txt, >          t.price, >          t.qty - IFNULL(qs.qty_sold, 0) 'onhand_qty' >     FROM PRODUCTS t > LEFT JOIN (SELECT o.pid, >                  SUM(o.qty) 'qty_sold' >             FROM ORDERS o) qs ON qs.pid = t.pid

[sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

SELECT t.pid, t.txt, t.price, t.qty - IFNULL(qs.qty_sold, 0) 'onhand_qty' FROM PRODUCTS t LEFT JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs ON qs.pid = t.pid WHERE t.pid = ? i have trouble running this

Re: [sqlite] Join performance in SQLite

> Do other SQL database engines not have this same limitation?" Are MySQL > and PostgreSQL and Firebird and MS-SQL and Oracle creating phantom > indices on-the-fly to help them do joins faster, for example?" Or do > their optimizers do a better job of finding ways to use indices in a > join?"

Re: [sqlite] Join performance in SQLite

As others have already mentioned, hash joins can help in a situation where there are no appropriate indexes. They can make things worse if the inputs aren't large enough though, so there's still some gray area. The biggest thing that other databases have going for them - MSSQL and Oracle

Re: [sqlite] Join performance in SQLite

* D. Richard Hipp: > One of the criticisms of SQLite is that it is slow to do joins. That > is true if SQLite is unable to figure out how to use an index to speed > the join. I was under the impression that SQLite actually did a > fairly reasonable job of making use of indices, if they

Re: [sqlite] Join performance in SQLite

On Sat, May 30, 2009 at 07:01:31PM +0100, Simon Slavin wrote: > I'm interested in how sqlite works differently to the SQL systems > which keep a daemon running as a background task. One of the > advantages of having a daemon which persists between runs of an > application is that the daemon

Re: [sqlite] Join performance in SQLite

SQLite has surprised me with its quick performance, not the other way around. In fact, I've implemented all kinds of lookup queries that I knew could be optimized by caching results so I didn't have to keep repeating the SQL query, but the performance was so good even repeating the queries that I

Re: [sqlite] Join performance in SQLite

I'm interested in how sqlite works differently to the SQL systems which keep a daemon running as a background task. One of the advantages of having a daemon which persists between runs of an application is that the daemon can keep its own list of ORDERs, and JOINs which are asked for

Re: [sqlite] Join performance in SQLite

D. Richard Hipp wrote: > There has been a recent flurry of comments about SQLite at > > http://www.reddit.com/r/programming/comments/8oed5/how_sqlite_is_tested/ > http://news.ycombinator.com/item?id=633151 > > One of the criticisms of SQLite is that it is slow to do joins. That > is

Re: [sqlite] Join performance in SQLite

Assuming memory is sufficiently inexpensive, I would think that it would almost always be useful to build an index for any field in a join rather than doing a full scan. (Or better yet, build a hash table if memory is sufficient.) Indices maintained in the database then become

Re: [sqlite] Join performance in SQLite

> Do other SQL database engines not have this same limitation? Are > MySQL and PostgreSQL and Firebird and MS-SQL and Oracle creating > phantom indices on-the-fly to help them do joins faster, for example? Sort of. There's 2 types of join methods in Oracle for this - Hash joins and Sort merge

[sqlite] Join performance in SQLite

There has been a recent flurry of comments about SQLite at http://www.reddit.com/r/programming/comments/8oed5/how_sqlite_is_tested/ http://news.ycombinator.com/item?id=633151 One of the criticisms of SQLite is that it is slow to do joins. That is true if SQLite is unable to figure

[sqlite] Join Me at Multiply

Check out my Multiply site I set up a Multiply site with my pictures, videos and blog and I want to add you as my friend so you can see it. First, you need to join Multiply! Once you join, you can also create your own site and share anything you want, with anyone you want. Here's the link:

Re: [sqlite] JOIN problem (works in postgres)

Ludvig Strigeus <[EMAIL PROTECTED]> wrote: > The following query does not work in Sqlite: > > SELECT i.user, ia.key, ia.value > FROM invite AS i > JOIN (invite AS j JOIN users AS u ON j.user = u.id AND > u.canonical_username='ludde') ON i.parent = j.id > LEFT JOIN inviteattr as ia ON

[sqlite] JOIN problem (works in postgres)

Hello, The following query does not work in Sqlite: SELECT i.user, ia.key, ia.value FROM invite AS i JOIN (invite AS j JOIN users AS u ON j.user = u.id AND u.canonical_username='ludde') ON i.parent = j.id LEFT JOIN inviteattr as ia ON ia.invite = i.id; It complains about "no such

Re: [sqlite] JOIN works very strange [3.6.2]

On Sat, Sep 20, 2008 at 08:45:16AM +0400, Alexander Batyrshin scratched on the wall: > Hello everyone, > > I gets strange result from this query on SQLite-3.6.2 > > SELECT > town.id, town_log.new_player_id, player.name > FROM > town_log > LEFT JOIN town >

Re: [sqlite] JOIN works very strange [3.6.2]

I just want to add that this SQL query works great at 3.5.4. And this is explain: addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 0 EXPLAIN SELECT town.id,

[sqlite] JOIN works very strange [3.6.2]

Hello everyone, I gets strange result from this query on SQLite-3.6.2 SELECT town.id, town_log.new_player_id, player.name FROM town_log LEFT JOIN town LEFT JOIN player ON town.id = town_log.town_id AND town_log.new_player_id =

Re: [sqlite] Join criteria referencing case select result in 3.6.2 produces different results from 3.4.2

2008/9/17 Dan <[EMAIL PROTECTED]>: > > >> >> On 3.4.2 we get: >> 3.0|3660.5|3 >> 6.0|1360.3|6 >> >> On 3.6.2 we get: >> |5020.8|3 >> > > 3.6.2 has a bug involving DISTINCT or GROUP BY queries that use > expression aliases (AS clauses) in the select-list. Problem is fixed > in cvs: > >

Re: [sqlite] Join criteria referencing case select result in 3.6.2 produces different results from 3.4.2

> Hi All, > We have been using SQLite 3.4.2 for some time. On investigating > upgrading to 3.6.2, we found that different results were produced for > one query. > The following illustrates: > ... > > On 3.4.2 we get: > 3.0|3660.5|3 > 6.0|1360.3|6 > > On 3.6.2 we get: > |5020.8|3 > 3.6.2 has a

[sqlite] Join criteria referencing case select result in 3.6.2 produces different results from 3.4.2

Hi All, We have been using SQLite 3.4.2 for some time. On investigating upgrading to 3.6.2, we found that different results were produced for one query. The following illustrates: CREATE TABLE tst1( tst1Id INTEGER, width REAL, thickness REAL ); CREATE TABLE tst2( tst2Id INTEGER,

Re: [sqlite] Join Syntax Questions

Mitchell Vincent wrote: > I could swear I've done this type of thing before and am sure I'm > overlooking something simple. > > Is this correct syntax? > > SELECT im.invoice_date as invoice_date,im.pay_by as > due_date,im.invoice_id as invoice_id, im.invoice_number as >

  1   2   >