I thought I'd obeyed all the Rushmore optimization tricks, but a tweak to one of my views slowed down noticably. When I turned on SYS(3054), I didn't like the answers I received. I'd like to know what I'm doing wrong so I can get this thing back up to speed.

Rushmore optimization level for table attendance: none
Rushmore optimization level for table termcourse: none
Rushmore optimization level for table courses: none
Rushmore optimization level for table programlist: none
Rushmore optimization level for table courselist: none
Rushmore optimization level for table enroll: none
Rushmore optimization level for table person: none
Rushmore optimization level for table roomslist: none
Rushmore optimization level for table schedulehours: none
Rushmore optimization level for table teacherlist: none
Joining table schedulehours and table termcourse (Cartesian product)
Joining intermediate result and table roomslist (Cartesian product)
Joining intermediate result and table programlist (Cartesian product)
Joining intermediate result and table teacherlist (Cartesian product)
Joining intermediate result and table courses (Cartesian product)
Joining intermediate result and table courselist (Cartesian product)
Joining intermediate result and table enroll (Cartesian product)
Joining intermediate result and table attendance (Cartesian product)
Joining intermediate result and table person (Cartesian product)
Using index tag Date to rushmore optimize table attendance
Rushmore optimization level for table attendance: partial
Using index tag Crsschedid to rushmore optimize table termcourse
Rushmore optimization level for table termcourse: partial
Rushmore optimization level for table courses: none
Rushmore optimization level for table programlist: none
Rushmore optimization level for table courselist: none
Using index tag Scheduleid to rushmore optimize table enroll
Rushmore optimization level for table enroll: partial
Rushmore optimization level for table person: none
Rushmore optimization level for table roomslist: none
Rushmore optimization level for table schedulehours: none
Rushmore optimization level for table teacherlist: none
Joining table termcourse and table roomslist using index tag Roomid
Joining intermediate result and table programlist using index tag Programid
Joining intermediate result and table schedulehours using index tag Hourid
Joining intermediate result and table teacherlist using index tag Teacherid
Joining intermediate result and table attendance using index tag Crsschedid
Joining intermediate result and table enroll using index tag Enrollid
Joining intermediate result and table courses using temp index
Joining intermediate result and table courselist using index tag Programid
Joining intermediate result and table person using index tag Personid

The tables:
attendance: 889347 records, index on attendid, crsschedid, date, enrollid
termcourse: 3815 records, index on termcrsid, crsschedid, courseid
courses: 93659 records, index on courseid, enrollid, programid, termcrsid
programlist: 189 records, index on programid
courselist: (programlist used again)
enroll: 6321 records, index on enrollid, personid, scheduleid
person: 5950 records, index on personid
roomslist: 28 records, index on roomid
schedulehours: 30 records, index on scheduleid, hourid
teacherlist: 47 records, index on teacherid
Result set: 29 records. 

SQL Statement:
SELECT Person.loginid, Person.last-(", "+Person.first) AS fullname,;
  Enroll.enrollid, Programlist.programname, {} AS startdate, {} AS enddate,;
  Courses.courseid, Courselist.programcode AS coursecode,;
  Courses.exclattend, Termcourse.crsschedid, Attendance.attendid,;
  Schedulehours.hourcode, Roomslist.roomcode, Teacherlist.teachername,;
  Attendance.date, Attendance.attendcode, SPACE(100) AS attendance;
 FROM ;
     smagic!attendance ;
    INNER JOIN smagic!termcourse ;
   ON  Attendance.crsschedid = Termcourse.crsschedid ;
    INNER JOIN smagic!courses ;
   ON  Termcourse.termcrsid = Courses.termcrsid ;
    INNER JOIN smagic!programlist ;
   ON  Termcourse.courseid = Programlist.programid ;
    INNER JOIN smagic!programlist Courselist ;
   ON  Courses.programid = Courselist.programid ;
    INNER JOIN smagic!enroll ;
   ON  Enroll.enrollid = Courses.enrollid;
   AND  Attendance.enrollid = Enroll.enrollid ;
    INNER JOIN smagic!person ;
   ON  Person.personid = Enroll.personid ;
    INNER JOIN smagic!roomslist ;
   ON  Termcourse.roomid = Roomslist.roomid ;
    INNER JOIN smagic!schedulehours ;
   ON  Termcourse.hourid = Schedulehours.hourid ;
    INNER JOIN smagic!teacherlist ;
   ON  Termcourse.teacherid = Teacherlist.teacherid;
 WHERE (  Termcourse.crsschedid = ( ?vp_crsschedid );
   AND  Enroll.scheduleid = ( ?vp_scheduleid ) );
   AND  Attendance.date = ( ?vp_date );
 ORDER BY Schedulehours.hourcode, Roomslist.roomcode, 2