SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-27-2008, 05:08 PM
| ||||
| ||||
 select slow from the view created from partitioned tables SQL 7.0 and W2K is our environment. I have a very large table 14 million rows. As it takes too long to select data from the table I decided to implement partitioned table. One of date column in the table is always used as a criteria. So I made one table per month and the date column got a constraint : ALTER TABLE [dbo].[Ledger_0201] WITH NOCHECK ADD CONSTRAINT [PK_Ledger_0201] PRIMARY KEY CLUSTERED ( [REGDATE], [IDNO], [CUSTDATE] ) ON [Data] , --****-- CONSTRAINT [CK_Ledger_0201] CHECK ([custdate] >= ' 2002 - 1 - 1' and [custdate] <= '2002 - 1 - 31') GO And I made a view by union all for approximately 20 tables. When I select count for one of the date against this view, it takes much long time. From the execution plan I could see that all partitioned table are accessed and SQL server is doing PK scan. Which means our SQL server cannot know which table to go with this query: select count (custdate) from view where custdate = '2001- 01-09' The partitioned tables have PK which include these three columns. Partitioned table nor the view have no index on custdate column. Can anyone tell me why??? [REGDATE],[IDNO],[CUSTDATE]      |
Linear Mode
