Optimization Assistance, algorithm suggestions

solfid

<p>Hello,</p><p> </p><p>I am in dire need of some assistance optimizing a report that is currently taking up to two hours to process.  I'm attempting to run a progress report in an LMS environment to determine the course status (Complete VS Incomplete).  I'm currently trying different types of join algorithms, however I'm not having much luck.  I'm wondering if I can simply apply the algorithm to one of the joins at the top level rather than applying it to every join.  Below are the details, any feedback you can provide would be great! (Attached is a schema I'm using) </p><p> </p><p>23,000 users total </p><p>80 courses</p><p>Status returned (Incomplete and Completed)</p><p>Parameters used for filtering: Manager username </p><p> </p><p>I am using an Oracle schema to filter the user list down by their manager mapping.  I have a required parameter to filter the list down by the manager username (Manager EID).  The average manager has about 25-40 users total.  </p><p> </p><p>IOB USER 1 (Manager table which has the parameter applied to) <===> IOB_SupervisorMapping (this table is joined with the user table) <===> IOB_User (this table contains the 23,000 users, each user account has a manager mapping) <===> IOB_CourseProgress (this is to determine the user progress (Complete vs Incomplete for each one of the 80 courses in the next table) <===> IOB_Course (this table houses all 80 courses).</p><p> </p><div>SELECT DISTINCT LC_IOB_User_1.Username AS "Manager EID", LC_IOB_User.Username AS "Username EID", </div><div>LC_IOB_User.UserFirstName AS UserFirstName_1, LC_IOB_User.UserLastName AS UserLastName_1, </div><div>LC_IOB_Course.CourseName AS "Course Name", LC_IOB_CourseProgress.CourseUserStatus AS "Course Status"</div><div> </div><div>FROM </div><div>"LCIOB/Information Objects/LC_IOB_SupervisorMapping.iob" AS LC_IOB_SupervisorMapping</div><div> INNER DEPENDENT JOIN </div><div>"LCIOB/Information Objects/LC_IOB_User.iob" AS LC_IOB_User ON </div><div>(LC_IOB_SupervisorMapping.SupervisorUserID=LC_IOB_User.UserID ) </div><div>{ CARDINALITY ('1-?') }</div><div> INNER DEPENDENT JOIN </div><div>"LCIOB/Information Objects/LC_IOB_CourseProgress.iob" AS LC_IOB_CourseProgress ON </div><div>(LC_IOB_User.UserID=LC_IOB_CourseProgress.CourseUserID ) </div><div>{ CARDINALITY ('+-+') }</div><div> INNER DEPENDENT JOIN </div><div>"LCIOB/Information Objects/LC_IOB_Course.iob" AS LC_IOB_Course ON (LC_IOB_CourseProgress.CourseID=LC_IOB_Course.CourseID ) </div><div>{ CARDINALITY ('+-+') }</div><div> INNER DEPENDENT JOIN </div><div>"LCIOB/Information Objects/LC_IOB_User.iob" AS LC_IOB_User_1 ON </div><div>(LC_IOB_SupervisorMapping.SupervisorID=LC_IOB_User_1.UserID ) </div><div>{ CARDINALITY ('1-1') }</div>

micajblock

<p>Everything should be pushed to the database. I am not clear on why you need the extra table. What version are you using?</p>