|
|
 next
|
  Subject: FLOWR Loop Counter
Author: Marc Van Cappellen
Date: 02 Aug 2007 04:36 PM
|
Keith,
The first query,
for $DETAIL in collection("DBNAME.dbo.DETAIL")/DETAIL
where ($DETAIL/CUSTOMERKEY = $ck) and
($DETAIL/HEADERKEY = $DETAIL/HEADERKEY)
is translated straight into SQL. I.e. the where clause will end up in the SQL statement and only minimal data will be retrieved out of the database.
With the second query,
for $DETAIL at $i in collection("DBNAME.dbo.DETAIL")/DETAIL
where ($DETAIL/CUSTOMERKEY = $ck) and
($DETAIL/HEADERKEY = $DETAIL/HEADERKEY)
The "at $i" is not translated into SQL. We say that it is "compensated" by DataDirect XQuery. As such the where clause can't be pushed to the database and as a result the complete DETAIL table is retrieved to the client.
Second, you indicated that the counter starts at two. That might well be the case yes. Note that the $DETAIL/$i tupples are first produced and only afterwards the where clause is evaluated. As such it is likely that certain tupples will be filtered out, and for example in your scenario this happens for the first tupple (likely also many others are filtered).
So we have two issues here... How can we resolve them?
I would try the following query (not tested, hopefully it doesn't include syntax errors).
a) the inner flwor can be translated into SQL which will resolve your performance issue
b) the top-level flwor doesn't have a where clause and as such the results will include incremental numbers 1,2,3,etc
for $d at $i in (for $DETAIL in collection("DBNAME.dbo.DETAIL")/DETAIL
where ($DETAIL/CUSTOMERKEY = $ck) and
($DETAIL/HEADERKEY = $DETAIL/HEADERKEY)
return $DETAIL)
return
<GROUP_3>
<LX>
<ANCHOR id="LoopCounter">
<LX01>{$i}</LX01>
</ANCHOR>
</LX>
</GROUP_3>
Regards,
Marc
|
|
|
|
top