How to Improve performance on partion View? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to Improve performance on partion View?

Author

Topic

sbhaygude
Starting Member

4 Posts

Posted - 2008-09-26 : 06:42:15

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

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-09-26 : 06:58:33

You can try "dirty reads" as in

select * from FROM DB1.dbo.table1 WITH(NOLOCK)

Are there only inserts running or updates, too?

sbhaygude
Starting Member

4 Posts

Posted - 2008-09-26 : 07:09:16

Only inserts running on. No updates on those tables.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 07:37:34

[code]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[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources