[thelist] asp LOGIC QUESTION
Ian Anderson
ian at zstudio.co.uk
Fri Apr 21 11:28:02 CDT 2006
Brian Delaney wrote:
> I have a sql record set that contains 12 uids. For each UID I want to go
> the servie table and return the services that are under this catagory's ID
>
> Once I get the list of services I want to dynamically build the html for
> display..
>
> The problem is this: I can loop through the catagory recoredset and find
> the services per uid I just am stuck on how to create a dynaic unique
> variable name to hold teh html for each service list.
I think retrieving recordsets inside a loop like this is the spawn of
the devil - I'm pretty sure I got burned badly trying to customise SQL
queries and execute SELECTs inside a loop this way before.
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
The only fiddly bits are remembering where in the array the different
fields are, and getting the logic right for concatenating the strings
inside and at the end of each loop.
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.
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.
Wouldn't want to do it with thousands of rows in either set, though. Had
a page once doing about 300 * 300, and it took about 5-10 seconds to
build the page; just about acceptable in the context. That was building
an enormous set of nested lists for a site map, and I was being quite
inefficient in assembling the strings. When I had time, I redid it using
a recursive function to find children of each page, and the processing
time went down to about 1 second.
Hope this helps
Cheers
Ian
--
zStudio - Web development and accessibility
- http://zStudio.co.uk
Snippetz.net - Your personal, private code library
- http://snippetz.net
More information about the thelist
mailing list