How do you do to keep useless data with good performance?

Question

Environment: SQL Server 2005, Windows Server 2003

The system in question is an online reservation system. In it you can create special offers.

The special offer has a validity period.

We store in our database every period entered by our client, even if the period is in the past. We must keep these periods.

The system has existed since 10 years, so most of the periods in the database have passed.

Problem:

• to select periods for a special offer for a very long time
• I choose special offers without a valid period, so I need to select my offers, then my periods, after which I will delete offers that do not have a valid period.

How do you deal with this? Is there a built-in tool to ignore some data in some case?

Answer 1

You can probably speed up the process by adding the necessary indexes, but if that doesn't help (enough), you might consider creating a second table to archive past offers. Thus, you will not be disturbed by the old data, but if you need it (for reports or for other reasons), you can still refer to the archive.