Table / DB Structure
Hi everyone. I am a relatively new to the architectural side of SQL server, and was wondering if I could get some advice on setting up the following environment. What I have is a web application in which a user can create merge documents on the fly from a CSV file and a document with merge fields in it (Word, txt, html, etc...). Each client can create a job in which 1-100,000 unique documents can be created. For each document created I store a record in a table called tbl_messages. This table can grow to immense sizes as you could imagine... say I have 5 clients using the app daily, each constructing 4 or 5 jobs of 50,000 records each... that is a lot of records. Anyways, my question really lies in the following: I would like to only use this table for transactions I guess... the records staus gets update from 1,2,3 then 4. Once at a status of 4 for each record in a job, the job is complete and that information is then available to be searched upon. Should I move all the records with a status of 4 to a different table, such as tbl_reportMessages or to a new server even used soley for reporting?
What is the rule of thumb when dealing with large amounts of records as it relates to the records being updated, and then just ebing used for reporting purposes? We have some queries that take a very long time to run because of the number of records in the Messages table. Would it be best to have a server dedicated to only housing data to report off of?
Any insight would be most appreciated.
Mike
|
