I have what I would think is a relatively small database (~1MB) and I have full indexing turned on. I am writing a query to rank instructors based on various properties, which are determined from the database. The query is much slower than I would expect, and I'd like to ask for assistance in optimizing it. The full query is below... if you would like me to send you the XML input file I am working on I will do so. The query takes 13166ms on a high-end system.
declare namespace db="http://cdf.utoronto.ca/2010/CSC301H1S/TeamYankee/TeacherSubstitution/Databas..."; declare variable $database_name as xs:string := 'substituter_db';
declare variable $course_code as xs:string := 'MAT244'; declare variable $section as xs:string := 'L0101'; declare variable $session as xs:string := 'S'; declare variable $date as xs:date := xs:date('2010-02-11'); declare variable $start as xs:time := xs:time('10:00:00'); declare variable $end as xs:time := xs:time('11:00:00');
(: --------- Declare Functions ------------- :)
(: --- Compute weight based on same department --- :) declare function local:same_department_weight($instructor as element(db:instructor), $lecture as element(db:lecture)) as xs:int { let $department_id := basex:db($database_name)/db:database/db:courses/db:course_details[db:course_code eq $lecture/db:course/db:course_code]/db:department_id/text()
let $weight := basex:db($database_name)/db:database/db:ranking_criteria/db:same_department/text()
return if ($instructor/db:department_id eq $department_id) then xs:int($weight) else xs:int(0)
};
(: --- Compute weight based on the fact they teach the same course --- :) declare function local:teaches_same_course_weight($instructor as element(db:instructor), $lecture as element(db:lecture)) as xs:int {
let $weight := basex:db($database_name)/db:database/db:ranking_criteria/db:teaches_same_course/text() let $same_course := basex:db($database_name)/db:database/db:lecture_schedule/db:lecture [ db:course/db:course_code eq $lecture/db:course/db:course_code and db:instructor_id eq $instructor/db:instructor_id ]
return if ( exists($same_course) ) then xs:int($weight) else xs:int(0)
};
(: --- Compute weight based on the fact they have covered the course before --- :)
(: --- Compute weight based on the fact they teach at the same level --- :) declare function local:teaches_at_same_level_weight($instructor as element(db:instructor), $lecture as element(db:lecture)) as xs:int {
(: Courses taught by this instructor :) let $courses_taught := distinct-values ( basex:db($database_name)/db:database/db:lecture_schedule/db:lecture [ db:instructor_id eq $instructor/db:instructor_id ]/db:course/db:course_code/text() )
let $levels_taught := distinct-values( for $course_taught in $courses_taught return basex:db($database_name)/db:database/db:courses/db:course_details[db:course_code/text() eq $course_taught]/db:course_level/text() )
(: The level of the lecture the sub is for :) let $level_of_lecture := basex:db($database_name)/db:database/db:courses/db:course_details[db:course_code eq $lecture/db:course/db:course_code]/db:course_level/text()
let $weight := basex:db($database_name)/db:database/db:ranking_criteria/db:teaches_at_same_level/text()
return if ( exists(index-of($levels_taught, $level_of_lecture)) ) then xs:int($weight) else xs:int(0)
};
(: --- Compute weight based on the total number of hours spent covering before --- :)
(: --- Compute weight based on the fact this course is one they prefer to teach --- :)
(: --- Compute weight based on the fact they teach in the same department --- :)
(: Compute the rank of the instructor for this course :) declare function local:compute_rank($instructor as element(db:instructor), $lecture as element(db:lecture)) as xs:int { xs:int(local:same_department_weight($instructor, $lecture) + local:teaches_same_course_weight($instructor, $lecture) + local:teaches_at_same_level_weight($instructor, $lecture)) };
(: ------- Do actual Work --------- :)
(: Get the lecture :)
let $lecture := basex:db($database_name)/db:database/db:lecture_schedule/db:lecture [ db:course/db:course_code eq $course_code and db:course/db:section eq $section and db:course/db:session eq $session and xs:date(db:event_time/db:date) eq $date and xs:time(db:event_time/db:time/db:start) eq $start and xs:time(db:event_time/db:time/db:end) eq $end ]
(: Iterate over all instructors and rank them :) let $instructors := basex:db($database_name)/db:database/db:instructors/db:instructor
let $ranked_instructors := for $instructor in $instructors return db:ranked_instructor {$instructor} db:rank{local:compute_rank($instructor, $lecture)}</db:rank> </db:ranked_instructor>
for $ranked_instructor in $ranked_instructors order by xs:int($ranked_instructor/db:rank) descending return $ranked_instructor