Re: Informix SE SQL knows JOIN as does Interbase ?

Jef Charlier wrote: > > 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." 1. Run UPDATE STATISTICS and try again. 2. Try using SET EXPLAIN ON before the query and then examine the contents of the file sqexplain.out in your current directory. It will tell you where it is doing the sequential scan which is slowing down your query. 3. Based on the above, create indexes. -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 -- If all else fails, read the instructions and the release notes. Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/