Performance Issues

• Sachin Bhaygude

  SSC-Addicted

  Points: 418

  More actions

  September 26, 2008 at 3:54 am

  #193593

  How to Improve performance on partion View?

  I have partition view that will fetch data by having union all to 9 database tables. All 9 databases have same table and schema structure on same instance.

  At the background, this tables keep loading new data for every 1 min to all 9 databases through automated jobs since morning. Means every 1 minute heavy INSERT operaion is going on this tables and at the middle of the day table holds more than 2 milion records.

  I am calling this view in one of the stored procedure. So due to heavy insert operation my procedure is running long or getting TIME OUT.

  Is there any other way to resolve the issue or any other solution to bit up the issues? Thanks in advance.

  Sample Script of View :-

  Create view VIEW1

  AS

  select * from FROM DB1.dbo.table1

  union all

  select * from FROM DB2.dbo.table1

  union all

  select * from FROM DB3.dbo.table1

  union all

  select * from FROM DB4.dbo.table1

  union all

  select * from FROM DB5.dbo.table1

  union all

  select * from FROM DB6.dbo.table1

  union all

  select * from FROM DB7.dbo.table1

  union all

  select * from FROM DB8.dbo.table1

  union all

  select * from FROM DB9.dbo.table1

  Thanks and Regards

  Sachin Bhaygude

  
  Sachin Bhaygude

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  September 26, 2008 at 6:49 am

  #877622

  Same suggesetion as I posted here

  http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111457

  CREATE TABLE#Stage

  (

  {same structure as "linked" tables}

  )

  INSERT #Stage SELECT * FROM DB1.dbo.table1

  INSERT #Stage SELECT * FROM DB2.dbo.table1

  INSERT #Stage SELECT * FROM DB3.dbo.table1

  INSERT #Stage SELECT * FROM DB4.dbo.table1

  INSERT #Stage SELECT * FROM DB5.dbo.table1

  INSERT #Stage SELECT * FROM DB6.dbo.table1

  INSERT #Stage SELECT * FROM DB7.dbo.table1

  INSERT #Stage SELECT * FROM DB8.dbo.table1

  INSERT #Stage SELECT * FROM DB9.dbo.table1

  ---

  N 56°04'39.16"
  E 12°55'05.25"