Indexed Union All view ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Indexed Union All view ?

Author

Topic

folettra
Starting Member

15 Posts

Posted - 2010-05-08 : 11:41:36

Hi, I need to figure out how to optimize a query that is based on a union all view.
I am running SQL 2000.
I have 13 tables that are joined in a view like so.

Create view dbo.vw_Union_all_call_stats
as
select * from call_Stats
Union All
Select * from Call_stats_01
Union All
Select * from Call_Stats_02
and so on up to Call_stats_12

all of the tables have the same structure.
the difference between them is that the first table contains 4 months of current data and the rest of the tables contains data older then 4 months broken out by month.

for example call_stats has data from Jan 1 2010 to April 30 2010. The most current 4 months.
Call_stats_01 has Jan 2009 data + Jan 2008 and so on.
Call_stats_02 has Feb 2009 data + Feb 2008 and so on.

I have a clusterd index on timestamp, Dnis, Site
DNIS is like a phone number and site is the location of the source data.
we have a lookup table that associates DNIS numbers with clients so in the data we could have dnis 100, 101, and 102 belong to client A
the view I use in the query is

Create view dbo.vw_call_stats_by_Client
as
select lookup.client, vw_union_all_call_stats.*
from lookup.client inner join vw_union_all_call_stats
on lookup.dnis = vw_union_all_call_stats.dnis

My query would then be
select
client,
sum(callsoffered) as Offered,
Sum(callsanswered) as answered
from dbo.vw_call_stats_by_Client
where client = 'A'
and timestamp >= yesterday
and timestamp < today

yesterday and today would be variables for yesterdays date and todays date.

When I show the executioin plan for this query it shows that it is searching throught the tables 01 to 12 eventhough the date range contains data that is entirely stored in the current 4 month table.

How do I get the query to recognize that the data it is looking for is in the first table and not waist time looking in the other tables?

Kristen
Test

22859 Posts

Posted - 2010-05-08 : 13:26:04

Check that you have satisfied all the horizontal partition requirements - in particular CHECK constraints (which you don't mention, so perhaps you don't currently have?):

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54747#185941

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-09 : 18:43:04

When you say you have a clustered index, do you mean an index on the view, or an index on the source tables?

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

folettra
Starting Member

15 Posts

Posted - 2010-05-17 : 15:40:49

The clustered inxex is on the source table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources