I have a large table (tens to hundreds of millions of records) that we have split for performance reasons into active and archive tables, using a direct field mapping, and running an archive process every night.
In several places in our code we need to run queries that combine the active and archive tables, almost invariably filtered by one or more fields (which we've obviously put indexes on in both tables). For convenience it would make sense to have a view like this:
create view vMyTable_Combined as
select * from MyTable_Active
select * from MyTable_Archive
But if I run a query like
select * from vMyTable_Combined where IndexedField = @val
it's going to do the union on everything from Active and Store before filtering by
@val, which is going to kill performance.
Is there any clever way of making the two sub-queries of the union view each filter by
@val before they create the union?
Or maybe there's some other approach you would suggest that achieves what I'm going for, i.e. an easy and efficient way of getting the union record set, filtered by the indexed field?
EDIT: here's the execution plan (and you get to see the real table names here):
Oddly enough, the active table is actually using the correct index (plus a RID lookup?) but the archive table is doing a table scan!