SQL Server Performance Forum – Threads Archive

Historical Data Reports

Hi everyone,
I have requiremnts to generate historical reports. That means eventhough I changed the status of Client now, it should not be effted to the previously data.
Diffinitly this will going to effect the perdformance of the system. Any one of your guys have an idea of doing this?
If so share your thoughts on this

This isn’t an uncommon practice, and really shouldn’t affect your performance that much if implemented correctly. There are several options of course: <pre id="code"> 1. Use historical tables and immediately copy data on update. –Easiest to implement generally. –You can use views to merge data where needed. –Can slow down overall performance of server as you have to move data for every update. –Requires rewrite of update/delete logic. 2. Same as 1 with active flag or expiration date. –Has benefit of letting you move the data to the history table on a batch basis. –Can result in slower performance if you don’t watch the indexes and statistics. –Requires rewrite of all logic. 3. Keep all data in one table with active flags or dates to determine current data. –With proper indexes, this shouldn’t incur a performance penalty. –Can use partitioned views to alleviate having all the data in one table, file, etc. –Can become cubersone to manage. –Will require rewrite of all select logic. </pre id="code"> If you need to cross multiple entities’ historical data in these reports across specific datetimes, keep in mind that active flags alone won’t work. It seems like something you shouldn’t have to mention, but you’d be surprised. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> What you implement can vary greatly by client. What are the real needs of the client. How dynamic is the data? What are the OLTP vs. DSS vs. OLAP needs. What are the availability demands of the data. The list goes on and on. MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

]]>