Help Making Query More Efficient

Am 15.03.2010 um 12:23 schrieb Adrien Guillon: > 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/Database"; > 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 > _______________________________________________ > BaseX-Talk mailing list > BaseX-Talk at mailman.uni-konstanz.de > https://mailman.uni-konstanz.de/mailman/listinfo/basex-talk

On Mon, Mar 15, 2010 at 7:44 AM, Michael Seiferle <michael.seiferle at uni-konstanz.de> wrote: > Hi Adrien, > > while I can not promise to actually make it run faster I will have a look at your query. 13166ms sounds fairly long... > > It would be nice if you could provide me with the source XML so I can run it on real data. > You may send it to: michael.seiferle at uni-konstanz.de > > Regards > Michael > > Am 15.03.2010 um 12:23 schrieb Adrien Guillon: > >> 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/Database"; >> 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 >> _______________________________________________ >> BaseX-Talk mailing list >> BaseX-Talk at mailman.uni-konstanz.de >> https://mailman.uni-konstanz.de/mailman/listinfo/basex-talk > >