[thelist] asp LOGIC QUESTION

[Matt Warden]

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.