SQLTeam.com | Weblogs | Forums

Indexed View for Joining Query


#1

I am trying to determine, before going too far, if this would be the correct solution. I have a table of over 100 million records, there is a specific set of records with the current date (about a million, and each day its different as the new days data loads) that I want to specifically join with a query I am using.

Currently I create a temp table to load this data in, then join that temp table, but it's pretty slow. I was wondering if an indexed view on this data would be faster? That way I can us ea view of all records with the current date, then index on the fields I need. It's a little confusing since it seems the same as using a temp table, but it seems like from other things I have read that this would be faster.


#2

The only really effective way to get best performance is to properly cluster the underlying table. How is the table with 100M rows clustered? Almost certainly it should be clustered on that datetime, and it sounds like almost certainly it's not. Once you get that fixed, this join -- and (almost) all your other processing -- will be vastly faster.