March 5, 2009 at 7:41 am
I am testing table partitioning using partitioned views with a plan to purchasing SQL Enterprise 2005 to manage the partitioning if we can justify the performance improvements.
80 million row transaction table has been split in 4 based on InternalmemberID the relevant check constraints have been added to the tables.
I want to list a bunch of transactions based on the memberID:
select * from vwTransactionTable
where Internalmemberid = 1111
The execution plan shows a seek on only one of the partitioned tables which is great
select * from vwTransactionTable
where Internalmemberid = (select Internalmemberid from vwAgency where memberid = 2222)
or
select * from vwTransactionTable acct
INNER JOIN vwAgency ai on acct.Internalmemberid = ai.Internalmemberid and ai.memberid = 2222
The execution plan for the 2 queries above shows a seek which is run against each of the partitioned tables, the actual SP is more complicated that this but I can't even get the basic query to work how I want it to which is:
* Get the internalmemberid based on the external memberid from the agency table
* Get all transactions for the internalmemberid (and only scan/seek the one table)
I would appreciate any help or comments on this.
Thanks.
March 5, 2009 at 9:43 am
Done a bit more research and it seems although Standard Version supports Partitioned Views it does not always consider it when creating an execution plan, hence why the first query works and the second doesn't.
Here is the link:
http://msdn.microsoft.com/en-us/library/ms187864(SQL.90).aspx
Would appreciate if someone could confirm/disagree with me.
Thanks.
Stef.
March 5, 2009 at 1:36 pm
Stef (3/5/2009)
Done a bit more research and it seems although Standard Version supports Partitioned Views it does not always consider it when creating an execution plan, hence why the first query works and the second doesn't.Here is the link:
http://msdn.microsoft.com/en-us/library/ms187864(SQL.90).aspx
Would appreciate if someone could confirm/disagree with me.
Thanks.
Stef.
Partitioned views exists in both editions and the main problem is that you must specify a LITERAL to see partition elimination in the execution plan.
M$ removed an important piece of information from it to see partition elimination at work even when the plan shows them all, which is "No OF Executions" in SQL 2000 you will actually "see" that the ACTUAL plan worked.
Partitioned views are not as robust as partitioned tables but for this last one you do need EE.
* Noel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply