Post reply

How to Improve performance on partion View?

  • September 26, 2008 at 4:28 am

    #154760

    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

  • September 26, 2008 at 6:41 am

    #877612

    Hi Sachin

    What isolation level are you using?? reading from a database or view shouldn't affect, what's the SQL you are using to read from the view, since a bad SQL takes more time to respond from a view like this, i would also suggest to look into dirty read, if the data you are fetching from these table dont have to be accurate.

    🙂

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply