Sorry if I was unclear: I'm not doing any single row queries...here's
the code which may make it clearer.
It's a bit "out of context", since this is part of a CFC which is being
called as a web service. I'm making it procedural for simplicity:
The queries (no performance issues here - they execute quite fast
enough):
<!--- This is to get the column list from USER_UPDATE, which has fewer
columns that USERS --->
<cfquery name="collist" datasource="#application.ds#" maxrows="1">
select * from user_update
</cfquery>
<!--- Using an Array here, rather than just the columnlist list. An
attempt to improve performance in the loops at the end --->
<cfloop list="#collist.columnlist#" index="i">
<cfset ArrayAppend(variables.columns,i)>
</cfloop>
<cfquery name="getusers" datasource="#application.ds#">
select #collist.columnlist# from USERS
where lastupdated >= <cfqueryparam cfsqltype="cf_sql_date"
value="#createodbcdate(since)#">
order by userid
</cfquery>
<cfset variables.userRecords = getusers>
<!--- The subquery here may seem redundant when I could just use
valuelist and the pk result from the previous query, but with 12000
results, this way is MUCH faster than a huge list string) --->
<cfquery name="getupdated" datasource="#application.ds#">
select * from USERS_UPDATE
where userid IN (select userid from users
where lastupdated >= <cfqueryparam cfsqltype="cf_sql_date"
value="#createodbcdate(since)#">)
</cfquery>
<cfset variables.updatedRecords = getUpdated>
Now, the loops to generate what I need. I am doing this entirely in
cfscript, again for performance:
<cfscript>
var since = arguments.sincedate;
var counter = 1;
getData(since); // calls the above queries
// Loop over each record
for(x=1;x LTE variables.userRecords.recordCount;x=x+1) {
// Loop through each column
for (i=1;i LTE ArrayLen(variables.columns);i=i+1){
if(variables.updatedRecords[variables.columns[i]][x]
gte createodbcdate(since)) {
counter = counter + 1; //just a counter - even
with this as the only output, it's WAY too slow.
}
}
}
</cfscript>
> -----Original Message-----
> From: Gaulin, Mark [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, January 09, 2007 12:10 PM
> To: CF-Talk
> Subject: RE: Getting changed fields from database using an audit table
>
> Try joining the two user tables rather than doing tons of single-row
> queries on the updated table. Unfortunately you will have to alias
all
> of the columns from one of the tables to get unique column names.
(You
> might do a single query of the users table that returns zero records
> just to get the column names... Then you could build the aliasing SQL
in
> code and then just execute it.)
>
> Mark
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266061
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
