Re: This SELECT hangs one of its three tables
Bruce,
When I read your first message on this subject, I thought that I had your
answer: namely, don't use "OR" conditions if you can possibly avoid them.
For some reason, Informix takes an AWFULLY long time to process queries
that contain OR. Maybe SET EXPLAIN ON would help us figure out why.
However, your second version of the query does not contain OR, and you
say it is also slow and "hangs" one of the tables. BTW is it still ~25
minutes till first row? Your timecard table, at 47 MB, is non-trivial
but not really HUGE.
You may want to change
select ...
from timecard, batch
where tbatch = bbatch and
tstatus <> "ADE"to
select ...
from timecard
where tstatus <> "ADE"
and tbatch in
(select bbatch from timecard)
When working with even moderately large tables, I get better performance
using "where col_x in (select ... )" then with joins. Perhaps the "IN"
syntax avoids the cartesian product problem.
Please let me know if this helps.
Regards,
Alan
+------------------------------+---------------------------------------+
| R. Alan Popiel | Internet: alan@den.mmc.com |
| Martin Marietta, LSC | ( Please note: My opinions do not ) |
| P.O. Box 179, M/S 5422 | ( represent official Martin policy. ) |
| Denver, Colorado 80201-0179 | Voice: 303-977-9998 |
+------------------------------+---------------------------------------+