On 4/21/06, Ian Anderson <ian at zstudio.co.uk> wrote: > What I do in these situations is to pull one or both recordsets into > arrays, and then iterate once through the child array looking for > matches for each instance in the parent array (or recordset). > > For example > aryCategoryList = rsCategoryList.getRows() > aryServiceList = rsServiceList.getRows() > > (close the connections and bin the objects) > > For i = 0 to Ubound(aryCategoryList, 2) > For j = 0 to UBound(aryServiceList, 2) > ...compare the PK ID of the current item in aryCategoryList with > the FK ID in the current item of aryServiceList and if they match, pull > the required information into a string for concatenation with previous > matches... > Next > Next > ... > It's horribly inefficient because you're doing N * M iterations where N > and M are the number of items in the two arrays, but it executes like > blazes. VBScript screams along when processing arrays, and even if it > takes a wee while because of the amount of data, you've closed and > released the database connection so you're not hogging resources and > creating a bottleneck. Yeah, but you're hogging CPU resources. You're basically re-inventing a join algorithm. Yours takes O(N*M) time, whereas databases can do it in less than O(N+M) time. > If the result sets you're working with are of the order of a few tens of > items times a few hundred items, I don't think it's too impractical. And indeed the query optimizer might use a nested loop algorithm in a case like that. But, the point is that the query optimizer is a lot smarter than the code you're writing, and that's a good thing. If the 2nd-query-in-the-loop doesn't sit right with you, just join the tables and work on the result. e.g.: CATEGORY ------------------------ catid catname THING ----------------------- thingid catid thingname select * from CATEGORY c inner join THING t on t.catid = c.catid order by c.catname, t.thingname var currentcat = '' loop until end of recordset if currentcat != recordset.catname then do whatever you need to do for each category end if do whatever you need to do for each thing end loop and you can just as easily use an array rather than a recordset object, if that's an issue. -- Matt Warden Miami University Oxford, OH, USA http://mattwarden.com This email proudly and graciously contributes to entropy.