Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
union all
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):

execution plan

Oddly enough, the active table is actually using the correct index (plus a RID lookup?) but the archive table is doing a table scan!

share|improve this question
2  
"it's going to do the union on everything from Active and Store before filtering by @val" have you actually demonstrated that behaviour? –  Jack Douglas Apr 4 '12 at 11:53
1  
I would have thought it will push the predicate down to filter against both Active and Archive then union the results. –  Martin Smith Apr 4 '12 at 11:57
1  
@Shaul - No filter after the union so presume that the predicates are indeed being pushed down to the respective tables. As to why you are getting a scan maybe no suitable index or it isn't covering and the query isn't very selective? –  Martin Smith Apr 4 '12 at 12:08
1  
Are you sure there is an index on your IndexedField in the _store table? –  Mikael Eriksson Apr 4 '12 at 12:09
3  
@Shaul - how selective is the index - how many distinct values? –  ConcernedOfTunbridgeWells Apr 4 '12 at 12:23

1 Answer 1

up vote 6 down vote accepted

The comments on the question show that the issue is that the test database the OP was using to develop the query had radically different data characteristics than the production database. It had much fewer rows and the field being used for filtering wasn't selective enough.

When the number of distinct values in a column is too small the index may not be sufficiently selective. In this case a sequential table scan is cheaper than an index seek/row lookup operation. Typically a table scan makes exensive use of sequential I/O, which is much faster than random access reads.

Often, if a query would return more than just a few percent of rows it will be cheaper just to do a table scan than an index seek/row lookup or similar operation that makes heavy use of random I/O.

share|improve this answer
1  
Very clear and useful answer - thank you for all your help! –  Shaul Behr Apr 4 '12 at 12:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.