title: Partitioned view and performance for the table with huge records
body: <p>Currently I am facing performance issues with queries and
  stored procedure. Following is the scenario:</p>
  
  <p>We have 3-4 tables in a database (<code>SQL Server 2000 SP4</code>) which
  have huge amounts of records. One of the tables has more than 25 million
  records. These tables are maintaining sales records and thousands of
  records added into them daily. Whenever a stored procedure is executed it takes
  15-30 minutes to complete. There are 3-4 joins on the table. Users are
  complaining about it frequently. Indexes are correct. To improve the performance
  we have implemented partitioned views. The solution was implemented by
  referring the following <a href="http://msdn.microsoft.com/en-us/library/ms190019%28v=sql.105%29.aspx" rel="nofollow">article on MSDN</a></p>
  
  <p>We have split the sales records by year wise and performance
  has improved, a query/stored procedure now takes takes 3-5 minutes to run. To improve
  the performance further, we split the sales records by month wise. We are maintaining
  4 years of data and now we are close to having 48 tables for sales data (After
  splitting sales data by month). I was expecting this to improve the performance. But
  that is not happening. The query is executing much slower than the previous one
  (year wise splitting of data) which surprises me. Also after looking at the
  query plan I found that it is doing an index scan on all 48 sales tables instead
  of scanning only the relevant tables. E.g. When queried to store procedure for the
  period <code>19-NOV-2012</code> and <code>20-DEC-2012</code>, it should consider only 2 tables <code>NOV-2012</code>
  and <code>DEC-2012</code>. But it is considering all 48 tables.  So my question is:</p>
  
  <ol>
  <li><p>Why is it considering all tables instead considering only
  relevant tables. E.g. In above example <code>NOV-2012</code> and <code>DEC-2012</code></p></li>
  <li><p>Why the year wise logic (split sales records by year) is
  performing better than month wise logic (Split sales records by month)</p></li>
  </ol>
  
  <p>Following is the code for partitioned View.<br/>
  For example year Other years are omitted.</p>
  
  <pre><code>    SELECT * FROM tbl_Sales_Jan2010
  UNION ALL
  SELECT * FROM tbl_Sales_Feb2010
  UNION ALL
  SELECT * FROM tbl_Sales_Mar2010
  UNION ALL
  SELECT * FROM tbl_Sales_Apr2010
  UNION ALL
  SELECT * FROM tbl_Sales_May2010
  UNION ALL
  SELECT * FROM tbl_Sales_Jun2010
  UNION ALL
  SELECT * FROM tbl_Sales_Jul2010
  UNION ALL
  SELECT * FROM tbl_Sales_Aug2010
  UNION ALL
  SELECT * FROM tbl_Sales_Sep2010
  UNION ALL
  SELECT * FROM tbl_Sales_Oct2010
  UNION ALL
  SELECT * FROM tbl_Sales_Nov2010
  UNION ALL
  SELECT * FROM tbl_Sales_Dec2010
  </code></pre>
  
  <p>Following is the table structure. </p>
  
  <pre><code>CREATE TABLE [dbo].[tbl_Sales_Jan2010](
      [SalesID] [numeric](10, 0) NOT NULL,
      [StoreNumber] [char](3) NOT NULL,
      [SomeColumn1] [varchar](15) NOT NULL,
      [Quantity] [int] NOT NULL,
      [SalePrice] [numeric](18, 2) NOT NULL,
      [SaleDate] [datetime] NOT NULL,
      [DeptID] [int] NOT NULL,
      [CatCode] [char](3) NOT NULL,
      [AuditDate] [datetime] NOT NULL CONSTRAINT [DF_tbl_Sales_Jan2010_EditDate]  DEFAULT (getdate()),
      [SomeColumn2] [varchar](15) NULL,
      [SaleMonthYear] [int] NULL CONSTRAINT [DF__tbl_Sales__SaleY__Jan2010]  DEFAULT (12010),
      [SaleDateInIntFormat] [int] NULL,
   CONSTRAINT [PK_tbl_Sales_Jan2010] PRIMARY KEY CLUSTERED 
  (
      [SalesID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  GO
  SET ANSI_PADDING OFF
  GO
  ALTER TABLE [dbo].[tbl_Sales_Jan2010]  WITH CHECK ADD CHECK  (([SaleMonthYear] = 12010))
  </code></pre>
  
  <p>Following is the query</p>
  
  <pre><code>SELECT     SUM(C.Quantity) as total
      FROM         Productdatabase.dbo.tbl_Product A , Productdatabase.dbo.tbl_Product_Category B, XDatabase.dbo.vw_Sales_Test C, tbl_Store D
      WHERE     A.ProductID = B.ProductID AND B.CategoryID = @CateID
      AND C.SomeColumn = A.PRoductCode
      AND D.StoreCode = C.StoreNumber
      AND D.country = @country
      AND D.status = 0
      And C.SaleMonthYear between @BeginMonthYear and @EndMonthYear               
      AND C.SalDate between @FromSaleDate and @ToSaleDate     
  </code></pre>