Re: [sqlite] Performance Question

[Dennis Cote]

Slater, Chad wrote:
Hello,
I'm having trouble with the performance of one of my queries and my "sql
kung fu" is limited. Any help with this problem would be greatly
appreciated

Here's a stripped down version of the tables I'm dealing with:

CREATE TABLE A ( 
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
)

CREATE TABLE B ( 
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
)

CREATE TABLE C ( 
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
)

CREATE TABLE JoinAToB ( 
    A_id INTEGER NOT NULL REFERENCES A ( id ), 
    B_id INTEGER NOT NULL  REFERENCES B ( id ), 
    UNIQUE( A_id, B_id )  
)

CREATE TABLE JoinAToC ( 
    A_id INTEGER NOT NULL  REFERENCES A ( id ), 
    C_id INTEGER NOT NULL  REFERENCES C ( id ), 
    UNIQUE( A_id, C_id )  
)

The following query takes so long I end up killing the app before the
query returns:

SELECT DISTINCT A.id
  FROM A,  
       C,
       B,
       JoinAToB,
       JoinAToC
 WHERE 
       ( ( ( JoinAToB.B_id IN ( 1 ) ) 
       AND ( JoinAToB.B_id = B.id ) 
       AND ( JoinAToB.A_id = A.id ) )  

    OR ( ( JoinAToC.C_id IN  ( 1 ) ) 
       AND ( JoinAToC.C_id = C.id ) 
       AND ( JoinAToC.A_id = A.id ) ) ) ;


Table A has approx 13,000 rows
Table B has 15 rows
Table C has 5 row
JoinTableAToB has 11 rows
JoinTableAToC has approx 450 rows

If I execute either of these queries separately they are very fast:

SELECT DISTINCT A.id
  FROM A, B,
       JoinAToB
 WHERE 
       JoinAToB.B_id IN ( 1 ) AND JoinAToB.B_id = B.id AND JoinAToB.A_id
= A.id ;


SELECT DISTINCT A.id
  FROM A,  
       C,
       JoinAToC
 WHERE 
      JoinAToC.C_id IN  ( 1 ) AND JoinAToC.C_id = C.id AND JoinAToC.A_id
= A.id ;


Adding the OR clause to combine the results seems to be the culprit but
I don't know why...

Chad,

You seem to be overly complicating the matter. Your query

SELECT DISTINCT A.id
 FROM A, B,
      JoinAToB
WHERE 
      JoinAToB.B_id IN ( 1 ) AND JoinAToB.B_id = B.id AND JoinAToB.A_id
= A.id ;


is the same as

SELECT A_id
 FROM JoinAToB
WHERE JoinAToB.B_id = 1;


Your table JoinAToB relates some A ids to some B ids. You don't need to 
join this to the tables A and B to do a query on the ids in that table. 
Similarly arguments apply to your table JoinAToC.

It looks like you are trying to get all the A ids that are referenced by 
these two tables where the B id is 1 or the C id is 1. In SQL this is:

SELECT A_id
FROM JoinAToB
WHERE JoinAToB.B_id = 1
UNION
SELECT A_id
FROM JOINAToC
WHERE JoinAToC.C_id = 1

HTH
Dennis Cote

-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------