Performance of a view that uses pivot operator

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

General SQL Server Forums

New to SQL Server Administration

Performance of a view that uses pivot operator

Author

Topic

sridevi boddu
Starting Member

4 Posts

Posted - 2011-04-18 : 07:25:18

Hi,

I created a view that uses a Pivot operator.
I want to create a index on this view to improve the performance ,but as it uses pivot operator it cannot be indexed.

Please suggest a method to improve the performance of the view that uses the pivot operator

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-18 : 14:01:12

mention complete view definition

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-18 : 14:22:11

The best you can do is to index the table(s) that this view depends on. You'll want to index the column being pivoted and any other columns you're including in the query. You'll have to test each index to see if it improves performance.

sridevi boddu
Starting Member

4 Posts

Posted - 2011-04-19 : 01:55:24

Hi,

Please find the complete view definition
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create view [dbo].[view1] with schemabinding
as

SELECT
Col1,
col2,
col3,
col4,
isnull([1],0) as col5,
isnull([2],0) as col6,
isnull([3],0) as col7,
isnull([4],0) as col8,
isnull([5],0)as col9,
isnull([6],0)as col10,
isnull([7],0) as col11,
isnull(

,0)as col12,
isnull([9],0) as col13,
isnull([10],0)as col14 ,

isnull([11],0) as col15,
isnull([12],0) as col16,
isnull([13],0) as col17,
isnull([14],0) as col18,
isnull([15],0)as col19,
isnull([16],0)as col20,
isnull([17],0) as col21,
isnull([18],0)as col22,
isnull([19],0) as col23,
isnull([20],0)as col24 ,
isnull([21],0)as col25 ,

isnull([22],0) as col26,
isnull([23],0) as col27,
isnull([24],0) as col28,
isnull([25],0) as col29,
isnull([26],0)as col30,
isnull([27],0)as col31,
isnull([28],0) as col32,
isnull([29],0)as col33,
isnull([30],0) as col34,
isnull([31],0)as col35 ,
isnull([32],0)as col36 ,
isnull([33],0) as col37,
isnull([34],0)as col38

FROM
( SELECT t.Col1,BucketId,col2,col3,col4,value
FROM dbo.table t
) AS TableToBePivoted
PIVOT
(
sum(value)
FOR BucketId IN ([1],[2],[3],[4],[5],[6],[7],

,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],
[32],[33],[34] )
) AS PivotedTable

Thanks in advance

sridevi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources