Adrian, > > It is terrible (60 seconds) when run like this: > SELECT a.f1,a.f2,b.ff1,b.ff2 FROM table1 a LEFT OUTER JOIN table2 b ON a.id > = b.id AND b.ff3 = 'T' > WHERE b.mydate BETWEEN '3/4/2002' AND '3/6/2002' AND b.ff4 = 'somevalue' >
It may be total rubbish, but have you tried rotating the whole join? SELECT a.f1,a.f2,b.ff1,b.ff2 FROM table2 b RIGHT OUTER JOIN table1 a ON a.id = b.id AND b.ff3 = 'T' WHERE b.mydate BETWEEN '3/4/2002' AND '3/6/2002' AND b.ff4 = 'somevalue' I may be making this up, but this way table2 is the main table, so SQL will have direct access to the dates in that table, rather than having to join everything up in order to get to b.mydate Just a thought.... Regards Stephen ______________________________________________________________________ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation � $99/Month � Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
