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:266054 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
