|
|
 next
|
  Subject: Nested XQuery FLOWRs with RDBMS joining all tables?
Author: David Isaac
Date: 27 Jul 2009 03:46 PM
|
Hello-
I'm hoping someone can help me improve performance of an XQuery against a relational database source using the DataDirect processor and drivers. I have four key tables, say Employee, EmployeeRecord, EmployeeRecordDetail, and AdditionalInfo. So my query looks something like this:
for $emp in collection("Employee")
return
<emp info...>
for $rec in collection("EmployeeRecord")
where $rec/SSN = $emp/SSN
return
<emp record info...>
for $detail in collection("EmployeeRecordDetail")
where $rec/recId = $detail/recId
return
<emp record detail...>
for $additional in collection("AdditionalInfo")
where $detail/recId = $additional/recId and ...other join info
return
<additional detail info.../>
</emp record detail>
</emp record info>
</emp info>
The query plan shows that, for each FLOWR, Stylus joins all tables in the where clause plus all tables in the where clause of all parent FLOWR statements. So in the inner most loop, I get a join like this:
select...from Employee, EmployeeRecord, EmployeeRecordDetail, AdditionalInfo...
Although Stylus seems to only be getting the rowId of the parents, this still seems to be causing performance problems: I let the query run all weekend and it never finished. My test database has:
10,000 employees
11,000 employee records
100,000 employee record details
10,000 additional info rows
My real database has 100x this much information. I think it would help if I could force Stylus to leverage the database to only get the info for each employee as needed, rather than all at once, by eliminating the parents from the joins and using the parent references as a constraint on a repeated query (e.g., where SSN = $outerLoopSSN).
Any suggestions would be greatly appreciated...this is a show stopper for our project.
|
|
|
|
top