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
-----Original Message-----
From: Doug Bezona [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 09, 2007 11:50 AM
To: CF-Talk
Subject: Getting changed fields from database using an audit table
Hi folks,
The environment is SQL Server 2000 and CFMX 7.0.1
Here's the scenario. I have two tables. One is the primary table of data
(let's call it USERS), the other is a table (say, USERS_UPDATED) with
identical columns that stores a date in each column representing the
last updated date of the corresponding column in the Users table.
The USERS table also has a single "lastupdated" field.
I am looking for the most efficient way to extract all changed fields
from USERS from a given date, using the date values in USERS_UPDATED.
Now, the issue isn't getting the information - I have working code to do
this. The issue is performance with large resultsets.
Currently, I am
1. selecting the records from USERS with lastupdated >= (some date) 2.
selecting the corresponding records from USERS_UPDATED 3. Looping over
the result of the USERS query 4. within the USERS loop, looping over the
list of columns, and comparing the date in USERS_UPDATED with (some
date) to build the output result.
Works fine until there are a lot of changed records in the USERS table
for the provided date.
A couple of real numbers:
USERS has 89 columns.
There are ~12000 updated records.
This means my current code does 12000*89 loop iterations or 1068000
total iterations. It's WAY too slow for our needs.
There has to be a better approach, but I am coming up blank. Anyone have
any ideas?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:266058
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
