XML Editor
Sign up for a WebBoard account Sign Up Keyword Search Search More Options... Options
Chat Rooms Chat Help Help News News Log in to WebBoard Log in Not Logged in
Show tree view Topic
Go to previous topicPrev TopicGo to next topicNext Topic
Postnext
David IsaacSubject: 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.

Postnext
Minollo I.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

Posttop
Ivan PedruzziSubject: 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

return
Ivan Pedruzzi
Stylus Studio Team

 
Go to previous topicPrev TopicGo to next topicNext Topic
Download A Free Trial of Stylus Studio 6 XML Professional Edition Today! Powered by Stylus Studio, the world's leading XML IDE for XML, XSLT, XQuery, XML Schema, DTD, XPath, WSDL, XHTML, SQL/XML, and XML Mapping!  
go

Log In Options

Site Map | Privacy Policy | Terms of Use | Trademarks
Stylus Scoop XML Newsletter:
W3C Member
Stylus Studio® and DataDirect XQuery ™are from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2016 All Rights Reserved.