the set up: 2 temp tables, aatemp & edi214tmp

table aatemp has 2 fields.
first field is an id column - integer. 2nd field is a datetime year
to fraction - 191,274 records
table edi214tmp has 2 fields.
first field is an id column - integer. 2nd field is a datetime year
to fraction - 29, 955 records

the SQL is as follows :

select distinct aa.activity_audit_id
from aatemp aa
inner join edi214tmp e
on aa.update_date between e.created_datetime AND (e.created_datetime +
10 UNITS SECOND);

this runs for hours - have not gotten it to complete yet.


however , if i run select count(*), it returns the result in a few
seconds. (count is a bit over 13,000)
if i run select first 1000, it still only takes a few minutes.
but when i try to get them all, it keeps running.

i notice the explain for the select count(*) so the table scan is run
over the smaller table
as opposed to the table scan running over the larger table when
running the select for the id's themselves.

any ideas why this might be the case?

Thanks for any help
Tom

yes - applied some as well as stats

here's the script if it helps
i'm going to kick it off early thursday just to see if it will
complete in a workday on the server - \
Thanks




select t.id
from tour t, job j --, tour_point tp, activity a, activity_audit aa,
import_edi214 e
where t.update_date > '2010-05-21 00:00:00'
and t.job_id = j.id
and j.cust_role_id = 58
and t.status_cid IN (1324, 1325, 1546, 2889)
into temp tourtemp with no log;

select distinct tour_id, created_datetime
from import_edi214
where tour_id in (select id from tourtemp)
and type_cid = 1191
and status_cid = 1190
into temp edi214tmp with no log;

select aa.activity_audit_id, aa.update_date
from activity_audit aa, activity a, tour_point tp, tour t
where aa.activity_id = a.id
and a.tour_point_id = tp.id
and tp.tour_id = t.id
and t.id in (select id from tourtemp)
and (aa.updated_by <> 'wlsedi')
into temp aatemp with no log;

create index 'informix'.idxedi214tmp on edi214tmp
( created_datetime );
create index 'informix'.idxaatemp on aatemp
( update_date );
UPDATE statistics high for table aatemp;
UPDATE statistics high for table edi214tmp;

drop table tourtemp;

select distinct aa.activity_audit_id
from aatemp aa,edi214tmp e
where aa.update_date between e.created_datetime AND
(e.created_datetime + 10 UNITS SECOND)

Tom Lehr wrote:
What does the explain plan say?

JWC

Which version are you using ? There are some serious optimizer bugs in
11.50FC6, we solved a lot of performance problems when we upgraded to
11.50.FC7

Ulf

when doing a first 10 test - the explain said there was a scan of the
larger table.
and when replacing select first 10 with select count(*), the plan said
the scan was on the smaller table.

we rewrote the sql and it now returns quickly- not exactly sure why
the other was behaving so weirdly - my only guess was a cartesian
join that ended up
scanning the large table for each row as each row was looked at in the
smaller table

rewritten sql:

select distinct aa.activity_audit_id
from aatemp aa
inner join table(multiset(select distinct created_datetime from
edi214tmp)) e
on aa.update_date between e.created_datetime AND (e.created_datetime +
10 UNITS SECOND)

we are still using version 10 but have a migration planned soon - good
to know about the fc7 because we originally were going to do fc6
but now are going to do fc7


thanks everyone