Informix SE SQL knows JOIN as does Interbase ?
The next informix SE query is too slow. It's written by an external
company.
Users on the clients PC's have to be very patient while browsing through
this query.
Test with DBACCESS-tool on the server : it takes 30 sec on the server
itself to write the results to a file.
Should my customer (who knows informix SE) use a better query ?
I don't know informix SQL. Can informix SE SQL work with joins as does
Interbase ?
Thanks,
Jef Charlier
The main table "STUDIE" has only 848 records. Every table has an index
on ID.
Server : biprocessor Pentium Pro,128 MB, Unix SCO. The CPU or I/O usage
stay's very normal.
Most of the time we have 95% idle.
-- informix SE 7.10 UC1 / SCO Unix 3.10a
-- (By the way an update is on his way : informix SE 7.20 UC3 but this
has nothing to do with this problem)
-- ==============================
SELECT *
FROM studie, outer werknemer,electrodienst,
status,outer gemeente, aard,outer studiebureau,
outer bezetter, outer aannemer, outer eenheid, outer memo
WHERE werknemer_id = werknemer.id
AND electrodienst_id = electrodienst.id
AND status_id = status.id
AND gemeente_id = gemeente.postcode
AND aard_id = aard.id
AND studiebureau_id = studiebureau.id
AND bezetter_id = bezetter.id
AND aannemer_id = aannemer.id
AND uitvterm_eenh=eenheid.id
AND studie.referentie=memo.referentie
-- ==============================
The same query rewritten for local Interbase takes 6 seconds.
We have used the same tables and indexes as for the informix SE test.
/* interbase local server 4.2 / win95 pentium 166 Mhz, 32 Mb
/*==============================
SELECT *
FROM
((((( (((((studie left outer join werknemer
on werknemer_id = werknemer.id)
join electrodienst
on electrodienst_id = electrodienst.id)
join status
on status_id = status.id)
left outer join gemeente
on gemeente_id = gemeente.postcode)
join aard
on aard_id = aard.id)
left outer join studiebureau
on studiebureau_id = studiebureau.id)
left outer join bezetter
on bezetter_id = bezetter.id)
left outer join aannemer
on aannemer_id = aannemer.id)
left outer join eenheid
on uitvterm_eenh=eenheid.id)
left outer join memo
on studie.referentie = memo.referentie);
/*==============================
Found on the interbase help :
"InterBase supports two methods for creating inner joins. For
portability and compatibility with existing SQL applications, InterBase
continues to support the old SQL method for specifying joins. In older
versions of SQL, there is no explicit join language.
An inner join is specified by listing tables to join in the FROM clause
of a SELECT, and the columns to compare in the WHERE clause."