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  SQL Server 2000 Forums  SQL Server Development (2000)  Partitioned View seems slow??

Author  Topic  eddyo Starting Member 6 Posts Posted - 2001-04-11 : 12:21:51 Hi, I began with a single table of about 700,000 rows. Spent 6 hours last night with the website offline to split the table into 14 different tables. I successfully created a partitioned view so the website's logic did not require much intervention. Unfortunately, using the view has not sped up the application. In fact, for most functions it is slower. The partitioning column is a varchar identifier that I provide on each of the tables. All check constraints were entered properly (the view is updateable so I know the partitions worked). table1 check (ID = Primary Key between 'A000' and 'A999') table2 check = (ID =Primary Key between 'B000' and 'B999') etc, etc. Now, when I search the original table for item 'A129', the result comes in about 750ms. Run the same search against the partitioned view and it takes 3000ms. Query optimizer should recognize that item 'A129' must be in table1 and only search table1. This should be significantly faster. Any ideas?