Thanks Noah, Yes that is what I have is a cross join. I was trying to simplify the definition.
A more correct definition is: TabA.ID1 TabA.ID2 TabA.field1 TabB.ID1 TabB.ID2 TabB.field2 TabC.ID1 TabC.field3 ViewBC: SELECT * FROM TabB INNER JOIN TabC On TabB.ID1 = TabC.ID1 This is slow: SELECT field1, field2, field3 from TabA LEFT OUTER JOIN ViewBC ON TabA.ID1 = ViewBC.ID1 AND TabA.ID2 = ViewBC.ID2 This is fast: SELECT field1, field2, field3 from TabA LEFT OUTER JOIN TabB ON TabA.ID1 = TabB.ID1 AND TabA.ID2 = TabB.ID2 INNER JOIN TabC ON TabB.ID1 = TabC.ID1 Any insight would help as I am bailing on Views and have a major programming change to do so. Thanks, Matt -----Original Message----- From: Noah Hart [mailto:[EMAIL PROTECTED] Sent: Thursday, June 07, 2007 10:45 AM To: sqlite-users@sqlite.org; [EMAIL PROTECTED] Subject: RE: [sqlite] Slow View Performance Matt, if looks like you have a cross join between your tables. Are the tables A, B, C related in some way? IF so, then your queries should look something like SELECT fields from TabA LEFT OUTER JOIN TabB where TabA.IDb = TabB.IDb And your view like SELECT fields FROM TabB INNER JOIN TabC where TabB.IDc = TabC.IDc This assumes that IDx is the related column Regards, Noah -----Original Message----- From: Matt [mailto:[EMAIL PROTECTED] Sent: Thursday, June 07, 2007 10:41 AM To: sqlite-users@sqlite.org Subject: [sqlite] Slow View Performance I have Tables TabA, TabB and TabC I have a view ViewBC that is a view SELECT fields FROM TabB INNER JOIN TabC If I run SELECT fields FROM TabA LEFT OUTER JOIN ViewBC it is way slow (like ignoring all indexes) If I run SELECT fields FROM TabA LEFT OUTER JOIN TabB INNER JOIN TabC it runs as I expect. Is this a know issue? Is there a simple workaround? Matt Froncek QODBC Development Support / FLEXquarters.com LLC Consultant QODBC Driver for QuickBooks - Unleash your data at <http://www.qodbc.com/> www.qodbc.com CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
