I'm wondering if it's a data modeling strategy to store data in a retrieval table (not sure if that's a thing).

I have 5-6 joins going through 10,000s of records that is only going to get more expensive. When a user performs an action (finalizing payroll for example) I was thinking of storing all the very important and commonly retrieved table data into a separate table called Tablename_History.

If I can simply access this new retrieval by id and retrieve ONE record with all the important information, wouldn't that be a better option?

I obviously know of 3 issues with this strategy:

  1. Data is not live.
  2. Duplication of data.
  3. Adding new columns within the history table would be difficult to set defaults and I might have to do some versioning.

That being said, am I missing something?

  • If it's to improve the performance of some queries, I would analyze the execution plans and indexes before going there... if these don't satisfy your issues, I would look into dividing the queries into a temporary table (divide and conquer), return the results and dropping the table at the end. Usually, an analysis of the execution plan, making sure the statistics are up to date and making sure the indexes are not fragmented will do the trick. – JohnG Feb 17 at 21:07
  • @JohnG I have no idea how to go about that; last time I hired someone to assist me in that the answer was "you're asking for too much data"... – Phil Feb 17 at 21:23
  • You're not actually dealing with much data in the grand scheme of things. As mentioned above, your best bet in the first instance is to identify any indexes that may help speed up any joins or help to satisfy and filtering you might be doing. "You're asking for too much data" is not the correct answer, and sounds like the person you hired did not know what they were talking about in this regard – Philᵀᴹ Feb 18 at 11:25
  • Add the structure of the tables involved, the estimated execution plan, the actual execution plan and the query to the posted question... We would then be able to help. – JohnG Feb 18 at 13:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.