Re: Help: slow SQL query performance using date range

Dear Simon! I am very glad to speak to you! Try to create composite indexes create index idx1 on tab_b ( due_date, status_ref); create index idx2 on tab_a ( cnhldgid_ref, fk_ref); & update statistics if it don't solve you problem try different combination like create index idx1 on tab_b ( status_ref, due_date); create index idx2 on tab_a ( fk_ref, cnhldgid_ref); etc. Success? SB> Hi there, I'd appreciate some advice on getting the following SQL to SB> run faster. As it stands the sql below takes around 2 minutes to SB> return 36 rows, but if the two date range filters are commented out SB> the query takes 1-2 seconds and returns 123 rows, even if just one of SB> the date filters (any one) is commented out the query takes 1-2 SB> seconds, but as soon as both are used the optimizer seems to get SB> screwed over which index to use. SB> In my mind, the main index join (using cnhldgid_ref) filters the SB> subset down to around 123 rows - so the remaining filters in the query SB> should not need to use any indexes. SB> BTW, Informix Online is v7.23.UC1 and update statistics (high) has SB> been run on the table. SB> The developer is now looking to run a first query to get the 123 rows SB> to a temp table and then do a following sql on that. SB> # of rows in tab_a = 280,000 SB> # of rows in tab_b = 1,200,000 SB> QUERY: SB> ------ SB> select tab_b.* SB> from tab_b, tab_a SB> where tab_a.cnhldgid_ref = tab_b.cnhldgid_ref SB> and tab_b.due_date >= '17/02/1997' SB> and tab_b.due_date <= '16/02/1998' SB> and tab_a.fk_ref = 298516 SB> and tab_b.status_ref = 99 SB> Estimated Cost: 7 SB> Estimated # of Rows Returned: 1 SB> 1) new067.tab_b: INDEX PATH SB> Filters: new067.tab_b.status_ref = 99 SB> (1) Index Keys: due_date SB> Lower Index Filter: new067.tab_b.due_date >= 17/02/1997 SB> Upper Index Filter: new067.tab_b.due_date <= 16/02/1998 SB> 2) new067.tab_a: INDEX PATH SB> Filters: new067.tab_a.fk_ref = 298516 SB> (1) Index Keys: cnhldgid_ref SB> Lower Index Filter: new067.tab_a.cnhldgid_ref = SB> new067.tab_b.cnhldgid_ref SB> -- SB> Simon Barber Sincerely yours, Petro 22:17 ''' H'' 02 1998 --- GoldED/W32 3.00.Beta1+ * Origin: ppa@antec.carrier.kiev.ua http://www.chat.ru/~ppa (2:463/510.33)