[sqlite] Performance Question

[Slater, Chad]

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...


Regards,


Chad

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