very slow join
Tom Lehr had a join between two temp tables matching rows where a timestamp fell within a 10-second window of another (BETWEEN created_datetime AND created_datetime+10 UNITS SECOND). SELECT COUNT(*) or FIRST 1000 returned quickly, but fetching all ~13,000 ids ran for hours; explain showed the plan scanning the large table instead of the small one. Indexes and UPDATE STATISTICS HIGH didn't help. Suggestions included checking the explain plan and noting optimizer bugs in 11.50.FC6 (fixed in FC7). He was on version 10 and solved it by rewriting the query to join against a derived table: inner join table(multiset(select distinct created_datetime from edi214tmp)), which returned quickly; the cause was suspected to be a near-Cartesian repeated scan.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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
Tom Lehr wrote:
> 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?
Any indexes?
--
Cheers,
Obnoxio The Clown
http://obotheclown.blogspot.com
I will now proceed to pleasure myself with this fish.
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
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:
> 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 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)
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 fromedi214tmp)) 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