March 11, 2009 at 10:10 am
Check your plan for the view query - it should show a clustered index seek on PK_t1, which means that the optimizer determined that the requested data could only exist in one of the three tables, in which case the view is partitioning correctly.
The estimated plan costs may be misleading, especially if the statistics are inaccurate on that column in the original table. Alternatively the stats are accurate and there just isn't that much of a saving to be made by partitioning. 446,261 / 427,000,000 is a little over 0.1% ie a very selective index. If you can attach the DDL for the original table and the estimated plans (in .sqlplan format) then we could make a better determination.
March 16, 2009 at 3:20 pm
My apologies on the delay in posting a followup. Here is the DDL for the original table:
CREATE TABLE [dbo].[list_names](
[LIST_ID] [int] NOT NULL,
[NAME_ID] [int] NOT NULL,
[OWNER_FLAG] [bit] NULL,
[create_date] [datetime] NOT NULL CONSTRAINT [DF__list_name__creat__6774552F] DEFAULT (getdate()),
[update_date] [datetime] NULL CONSTRAINT [DF_LIST_NAMES_update_date] DEFAULT (getdate()),
CONSTRAINT [PK__LIST_NAMES__251C81ED] PRIMARY KEY CLUSTERED
(
[LIST_ID] ASC,
[NAME_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[list_names] WITH NOCHECK ADD CONSTRAINT [FK_LIST_NAMES_NAMES] FOREIGN KEY([NAME_ID])
REFERENCES [dbo].[names] ([NAME_ID])
GO
ALTER TABLE [dbo].[list_names] NOCHECK CONSTRAINT [FK_LIST_NAMES_NAMES]
Also, the two plan files are attached as a single .ZIP file.
Thanks!
April 3, 2009 at 6:05 am
SwedishOrr (3/10/2009)
After the initial setup, the query execution plans looked great, but actual query time was just about the same as when I wasn't using the partitioned view.
Hi!
Can I just say that from my experience with 2005 partitioning (not indexed views) performance is pretty much always worse than with an unpartitioned table, with some specific exceptions - I'm talking generally. I feel fairly confident in saying that 2005 partitioning should only really be done for the maintenance advantages, never in the expectation of performance gains.
Now, sure, partitioning (schemes and functions) is a very different animal from a partitioned view. In several important respects (including the efficiency of locating the correct 'partition' and the ability to use parallelism effectively) partitioned views are superior. [2008 seems to do a better job in both the problem areas I highlight by the way.]
Even so, realizing performance gains from partitioned views (let's not even discuss distributed ones!) is easily achievable in simple examples, where the narrowing of the search(es) to a single table is clearly of benefit. However, as complexity increases, and the QO has more plan options to consider, it can become very difficult to retain the performance advantages without extensive hinting or re-writing of queries in a way that makes the optimal choice clearer to the QO.
If your one large table is on a single disk, you might find more overall performance benefit in adding more or faster (or both) IO capacity, or more main system memory, or moving to 64-bit (or...or...or!) instead of implementing the partitioned views. It's just a thought...!
/Paul
Addendum: partitioned views are *cool* though - if only because it is one of the few ways to get a SWITCH operator into a showplan output!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply