CROSS POSTED IN ALT.COMP.LANG.COLDFUSION...

I am working on a script to list markets and submarkets. I need it for
a table display that allows for editing, as well as for drop down lists
for selection. I have a table that has the following structure...

SC_MARKET
---------
userid
mkt_name
main_mkt_uid

The main_mkt_uid is a reference to another record's userid in the same
table. Records that are top level have a main_mkt_uid of zero.

I have a page that queries the top level markets, and then a CF_MODULE
that queries all the submarkets, calling itself recursively. This
works, but the dataset takes a while to display.

Sample data:

userid: 1
mkt_name: Midwest
main_mkt_uid: 0

userid: 2
mkt_name: Illinois
main_mkt_uid: 1

userid: 3
mkt_name: Chicago
main_mkt_uid: 2

userid: 4
mkt_name: West Coast
main_mkt_uid: 0

When the drop downs are built it will look something like this...

---------------------------
Midwest
-- Illiniois
---- Chicago
West Coast
(etc.)
---------------------------

My two questions are...
1) Is there a better way to do this that will allow it to display
faster (or do I just need to index my table?)

2) Would it be smarter to have the script create a static list for the
drop-downs that can be included, and just write over the static code
when something is edited?

2b) If "2" above is the way to go, how do I concatenate a variable that
is part inside the CF_MODULE code, and part outside of it?

SAMPLE CODE:
------------

<!--- -------------------------------------------- --->
<!--- The form page with a drop down selection.... --->
<!--- -------------------------------------------- --->
<tr>
<td>Submarket:</td>
<td>
<cfquery name="qryMetro" datasource="#application.dsn#">
SELECT userid,mkt_name
FROM rptdta.sc_market
WHERE main_mkt_uid=0
ORDER BY mkt_name
</cfquery>

<select name="submarket_uid" class="clean_select">
<option value="" selected>Select</option>
<cfoutput query="qryMetro">
<cfmodule
template="../_modules/MD_subMarketDD.cfm"
curUID="#qryMetro.userid#" curIndent="0">
</cfoutput>
</select>
</td>

<!--- -------------------------------------------- --->
<!--- CF_MODULE code that get's called recursively --->
<!--- -------------------------------------------- --->
<cfoutput>

<!--- get the info for the current id --->
<cfquery name="qryMarketInfo" datasource="#application.dsn#">
SELECT userid, mkt_name
FROM rptdta.sc_market
WHERE userid=#attributes.curUID#
ORDER BY mkt_name
</cfquery>

<cfset curIndent = #attributes.curIndent#>

<cfparam name = "url.mmuid" default = "0">

<!--- display the current id's info --->
<option value="#qryMarketInfo.userid#" <cfif #qryMarketInfo.userid# EQ
#url.mmuid#>selected</cfif>>
#RepeatString("-",variables.curIndent)#
#qryMarketInfo.mkt_name#
</option>

<!--- now get the submarkets for this id --->
<cfquery name="qrySubmarketIDs" datasource="#application.dsn#">
SELECT userid, mkt_name
FROM rptdta.sc_market
WHERE main_mkt_uid=#qryMarketInfo.userid#
ORDER BY mkt_name
</cfquery>

<!--- set the "--" indent for diplay purposes --->
<cfset curIndent = evaluate(#variables.curIndent# + 2)>

<!--- loop over the current submarket id list --->
<cfloop query="qrySubmarketIDs">
<!--- call this function recursively --->
<cfmodule template="./MD_subMarketDD.cfm"
curUID="#qrySubmarketIDs.userid#" curIndent="#variables.curIndent#">
</cfloop>

</cfoutput>

<!--- ---------------- --->
<!--- END CODE EXAMPLE --->
<!--- ---------------- --->

Thanks for any help. I'm using CF MX, but our production box is still
running v.5, so I'm trying to be backwards compatible.

I've been doing PHP work for the past few years and am a bit rusty with
CF code, so I appreciate the help.

Don