FIX: Running JOIN on Three or More Tables is Slow (163420)

      SELECT * FROM FORCE test1 ;
       INNER JOIN test2 ON test2.id = test1.id ;
       INNER JOIN test3 ON test3.id = test2.id

      SELECT * FROM FORCE test1 ;
       INNER JOIN (test2 INNER JOIN test3 ON test3.id = test2.id );
       ON test2.id =test1.id
				

MORE INFORMATION

This problem seems to occur when there is a join of three or more tables when there are only one or two records in one of the tables or there are only one or two records that have related records in the other tables. The tables also have to have a large number of records in them (30,000, 70,000 and 90,000). All of the table join scenarios that can exhibit the problem are not known.

If the slow query is allowed to run, it may fill the computer's hard drive with a large temporary file and the following program error message may be received:

Steps to Reproduce Behavior

The following steps provide sample code that can be used to reproduce the problem. Press the Escape key to cancel the query in step 2 when desired.

1. Place the code below in a program file and run it. The code creates three tables that will take up about 6.6 MG of disk space:

         CLOSE ALL
         SET ECHO OFF
         SET TALK OFF
         CREATE TABLE test1 (ID C(15),Dummy C(15))
         CREATE TABLE test2 (ID C(15),Dummy C(15))
         CREATE TABLE test3 (ID C(15),Dummy C(15))
         CLOSE ALL
   
         WAIT WINDOW NOWAIT "filling test1"
         USE test1
         INDEX ON ID TAG ID
         FOR x = 1 TO 71894
         APPEND BLANK
         REPLACE ID WITH SYS(2015)
         NEXT
         GO TOP
         REPLACE NEXT 2 ID WITH "TEST"
   
         WAIT WINDOW NOWAIT "filling test2"
         USE test2
         INDEX ON ID TAG ID
         FOR x = 1 TO 30622
         APPEND BLANK
         REPLACE ID WITH SYS(2015)
         NEXT
         GO TOP
         REPLACE NEXT 2 ID WITH "TEST"
   
         WAIT WINDOW NOWAIT "filling test3"
         USE test3
         INDEX ON ID TAG ID
         FOR x = 1 TO 90000
         APPEND BLANK
         REPLACE ID WITH SYS(2015)
         NEXT
         GO TOP
         REPLACE NEXT 2 ID WITH "TEST"
         CLOSE ALL
   							

2. Run the following query from a program file:

         SELECT * FROM test1,test2,test3 ;
          WHERE test1.id=test2.id AND test2.id=test3.id
   							

3. The following two SQL Select statements can also be used to reproduce the problem using the sample data created in step 1 above:

         SELECT * FROM test1 ;
          INNER JOIN test2 ON test2.id = test1.id ;
          INNER JOIN test3 ON test3.id = test2.id
   
         SELECT * FROM test1 ;
          INNER JOIN (test2 INNER JOIN test3 ON test3.id = test2.id );
          ON test2.id =test1.id