I have two identical tables (NEW and OLD) that records lots of stats
and the memberID is the only one index key to search.

MemberID is a non-clustered index on NEW and OLD. We have about 80,000
members, which means the NEW has about 80,000 entries and the OLD has
about 140,000 entries since it has additional seasonal indexes on it.
These two tables will be accessed by webpage and another application.

We retrieve these numbers with an asp page that sends a specific
memberID and does lots of UNION ALL calculation queries for NEW and
OLD in the front end, which seriously impacts the database
performance. I was assigned to optimize this application and I have
thought of some alternatives, but I am not satisfied with any one.
Here they are:

1. Re-write the calculation from NEW and OLD into Stored Procedure and
save the stats on another table (let's say TABLE_AWARD). Set up a job
schedule at midnight.
=>but in that case we will have two table scans happening at night.
The SP won't be able to use the MemberID to identify a specific
member.

2. Store OLD stats on TABLE_AWARD only and read NEW stats with the asp
file.
=>In that case we are still doing lots of calculations in the front.
It would be like doing UNION ALL with another table.

3. Set additional indexes on NEW and OLD to speed up.
=>but the only one non-changable value is the memberID. Everything
else is dynamic and will be changed at any time by the application.

To me #1 and #2 are more feasible but they both have drawbacks. No
matter how I revise this system there must be some drawbacks that keep
the system loading high, either the table scans or the UNION ALL in
the front. I wonder if there's any other feasible solution that could
really lower down the system loading. Any comments will be
appreciated.

-Tanya

There are certainly times when totals must be calculated ahead of
time, rather than on the fly, but this is generally a last resort. The
first step is to understand the problems when doing them on the fly.

You need to provide the table definitions, including all keys and
indexes, and more information on the queries that you are trying to
optimize.  It is not clear from the given information what any of that
is, and guessing blindly is not productive.

Roy Harvey
Beacon Falls, CT