Partition Table

• Loner

  SSC-Insane

  Points: 21279

  More actions

  June 29, 2006 at 8:45 am

  #91134

  I partitioned a big table by years. 

  So now I have Tab2000, Tab2001, Tab2002...

  I created a view to query the data.

  Create View QueryTable

  AS

  SELECT * FROM Tab2000

  UNION ALL

  SELECT * FROM Tab2001

  UNION ALL

  SLELECT * FROM Tab2002

  ...

  The performance is a big problem using the view but 

  I cannot create a indexed view because I used UNION.  

  Is there any other way to improve the performance of the view?

  Thanks

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  June 29, 2006 at 9:59 am

  #646772

  You should not be having performance issues.

  If you have a partitioned view you have to make sure you have constraints on the table.

  So for the Date column in each of your tables make sure you have a constraint that the date is >= 01/01/2001 and < 1/1/2002 and the query optimizer will use that constraint to know which table to look in for what data.

  Another thing is if you join paritioned view to other paritioned views you will experience poor performance, you may need to creae a series of views that join together tables of like years.

  when you partion data you have to take great care in your solution so it will perform optimally.