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:
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.
Subject:Nested XQuery FLOWRs with RDBMS joining all tables? Author:Minollo I. Date:28 Jul 2009 08:32 AM
To start, we’ll need some additional information:
- The database and version
- Which version of DDXQ being used
- A copy of the table DDL and even better some sample data
- The actual XQuery you are running
Subject:Nested XQuery FLOWRs with RDBMS joining all tables? Author:Ivan Pedruzzi Date:29 Jul 2009 02:58 PM
try using a single for
for $emp in collection("Employee"),
,$rec in collection("EmployeeRecord")
,$detail in collection("EmployeeRecordDetail")
,$additional in collection("AdditionalInfo")
where $rec/SSN = $emp/SSN
and $rec/recId = $detail/recId
and $detail/recId = $additional/recId