Re: Help: slow SQL query performance using date range

Simon Barber wrote in message ... >Hi there, I'd appreciate some advice on getting the following SQL to run >faster. As it stands the sql below takes around 2 minutes to return 36 >rows, but if the two date range filters are commented out the query >takes 1-2 seconds and returns 123 rows, even if just one of the date >filters (any one) is commented out the query takes 1-2 seconds, but as >soon as both are used the optimizer seems to get screwed over which >index to use. ---snip--- ># of rows in tab_a = 280,000 ># of rows in tab_b = 1,200,000 > >QUERY: >------ >select tab_b.* >from tab_b, tab_a >where tab_a.cnhldgid_ref = tab_b.cnhldgid_ref >and tab_b.due_date >= '17/02/1997' >and tab_b.due_date <= '16/02/1998' >and tab_a.fk_ref = 298516 >and tab_b.status_ref = 99 > > >Estimated Cost: 7 >Estimated # of Rows Returned: 1 > >1) new067.tab_b: INDEX PATH > > Filters: new067.tab_b.status_ref = 99 > > (1) Index Keys: due_date > Lower Index Filter: new067.tab_b.due_date >= 17/02/1997 > Upper Index Filter: new067.tab_b.due_date <= 16/02/1998 > >2) new067.tab_a: INDEX PATH > > Filters: new067.tab_a.fk_ref = 298516 > > (1) Index Keys: cnhldgid_ref > Lower Index Filter: new067.tab_a.cnhldgid_ref = >new067.tab_b.cnhldgid_ref >-- >Simon Barber Try a couple of composite indexes: ... ON tab_b(status_ref, due_date, cnhldgid_ref); -- test to see if cnhldgid_ref helps! ... ON tab_a(cnhldgid_ref, fk_ref); -- will force an index only read if the index is used at all S.W.