Re: 3-Way Join Running Sloowww with the Query/Schema

melvin.mariney2@bridge.bst.bls.com (Melvin Mariney) wrote: >I have a query that is returning 1791 rows which consists of a 3-way >join. I have attach a copy of the 3 tables with the query and a copy >of sqexplain.out. I have run the query on an HP h70-2 CPU 9.04 and >HP T500-2 CPU box from Dbaccess using Informix 7.11 UC1. The query >runs in 17 minutes on the H70 and 21 minutes on the T500. > >The tables are not that large and it seems like this query should run >a lot faster than it is, especially on a T500. > >Any suggestions would be appreciated from the Informix gurus out there >and thanks in advance for any response. Please email and post to the >newsgroup since others will probably be interested in the results. >snip I wanted to let the Informix gang know what the resolution was to my slow running query. Special thanks to Jack Parker, Billy Wheeler, and Robert Minter, as they all were accurate in identifying the 'or' statements as being the problem. Using their suggestions of replacing the 'or' statments with 'union' into temp tables caused the query to run in 10 seconds compared to 17 minutes before. Bill Ennis replied with a suggestion regarding the indexes also which helped to further reduce the runtime to 4 seconds. Thanks again for everyone's help. You all help to defend the honor of Informix not being able to handle a 3-way join and putting the ball back in the application developer's court. -- Melvin Mariney, Informix DBA e-mail: melvin.mariney2@bridge.bst.bls.com