40k records is nothing. I think you only need to be concerned when you actually see a performance decrease.
You are correct in that the proper indexes will certainly increase performance. I'd rather deal with one huge table, and the proper indexes, than several smaller tables and have to join them together. If you don't need online access to the data, then you can archive it. M!ke -----Original Message----- From: James Davis [mailto:[EMAIL PROTECTED] Sent: Friday, September 21, 2007 3:00 PM To: CF-Talk Subject: Archiving SQL table data In general when using a relational database for client data storage, is it a good idea to archive data older than a certain date (like say 2 years) or just let good indexes take care of data growth? For example, we would move all data out of certain tables that grow very large (approx. 20K - 40K records, growing by 10K / yr.) to tables that aren't used often. This theoretically would speed up queries on those often used tables. The downside of course is writing new queries that use the "archived" tables to get old data when needed. So my question is: What is the standard practice for this issue? Archive or not? James Davis Software Engineer Kaleida Systems, Inc. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Check out the new features and enhancements in the latest product release - download the "What's New PDF" now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289146 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
