I would suggest some data warehouse techniques here. Where you would extract the emails from 73 databases and insert them all into one database and table and run the query against that single table.
Doing a 73 table union is just never going to work well even with proper indexing. Byron Mann Lead Engineer & Architect HostMySite.com On May 5, 2013 4:09 PM, "Richard White" <[email protected]> wrote: > > > Thanks Dave, good idea. Ill setup the separate database for this query. > > Yes unfortunately this query specifically collects data from all databases. I used to have it as a single query per database inside of a loop but this caused an error signifying too many connections. > > The query is as follows: > > <cfquery name="userExistsInDatasource" datasource="rebasedb_manage"> > <cfloop index="i" from="1" to="#getAllDatasources.recordcount#"> > > <!--- ENSURE ALL THE SELECT STATEMENTS GET ADDED TOGETHER ---> > <cfif i gt 1> > UNION > </cfif> > > SELECT #getAllDatasources.datasourceid[i]# as datasourceid, > userid > FROM rebasedb_#getAllDatasources.datasourceid[i]#.users > > <!--- add the where clause which applies the filters entered into the grid ---> > WHERE email = <cfqueryparam value="#session.currentUser.email#" cfsqltype="cf_sql_varchar" maxlength="50"> > > </cfloop> > </cfquery> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:355652 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
