performance of a view - SQL Server Forums

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 Programming

performance of a view

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-07-31 : 16:54:44

i created a view such that it concatenates all the policies of a request into a single cell using the below.The performance of the view is getting bad as the table is getting bigger and bigger

When i try to access this view from access or info path it just crashes due to time out errors

Is there a better way to accomplish the concatenation Iam doing with the below view

Create View [dbo].[customerInfo] as 
SELECT

DISTINCT Reqno,STUFF(( 

SELECT 

',' + policy





FROM Policies WHERE Requestno= T.Requestno

FOR XML PATH('') 

),1,1,'') AS CSN

FROM Policies T

GO

DML of the table

CREATE TABLE [dbo].[POLICIES](
	[Reqno] [numeric](18, 0) NOT NULL,
	[policy] [varchar](25) NOT NULL,
	[PolicyName] [varchar](150) NULL,
	[State] [varchar](50) NULL,
	[Platform] [varchar](50) NULL,
	[OrigEffDate] [datetime] NULL,
	[NextRenewal] [datetime] NULL
Go

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-07-31 : 21:04:14

Not quite sure what you're trying to do here - you haven't really given us much to go on in terms of scale and volume either.
I'd suggest getting your data out as rows and fix it up in the front end.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-31 : 21:16:26

Not sure how much this would help, but if you don't already have it, you might try adding a non-clustered index on RequestNo with Policy as included column.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-31 : 21:23:40

You might also try to extract the ReqNo into a CTE and then run the concatenation against that as in:

;WITH cte AS
(
	SELECT DISTINCT ReqNo FROM Policies
)
SELECT
	a.reqNo,
	b.String
FROM
	cte a
	OUTER APPLY
	(
               SELECT ',' + policy
               FROM   Policies p
               WHERE p.[Reqno] = a.[Reqno]
                      
                      FOR XML PATH('')
	) b(String);

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-08-01 : 08:28:14

Thank you so much.It just improved the data retreival drastically

quote:

Originally posted by sunitabeck

You might also try to extract the ReqNo into a CTE and then run the concatenation against that as in:

;WITH cte AS
(
	SELECT DISTINCT ReqNo FROM Policies
)
SELECT
	a.reqNo,
	b.String
FROM
	cte a
	OUTER APPLY
	(
               SELECT ',' + policy
               FROM   Policies p
               WHERE p.[Reqno] = a.[Reqno]
                      
                      FOR XML PATH('')
	) b(String);

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-08-01 : 09:24:13

why do it in the db at all?

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-08-01 : 11:23:18

to support applications created using infopath and some ad hoc reporting using Business Objects

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-08-01 : 11:23:59

When i use the below to view data there is a preceeding coma.Is there a way we can eliminate the preceeding coma

;WITH cte AS
(
SELECT DISTINCT ReqNo FROM Policies
)
SELECT
a.reqNo,
b.String
FROM
cte a
OUTER APPLY
(
SELECT ',' + policy
FROM Policies p
WHERE p.[Reqno] = a.[Reqno]

FOR XML PATH('')
) b(String);

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-01 : 11:39:05

Change the part shown in RED:

....
SELECT
	a.reqNo,
	STUFF(b.String,1,1,'') as String
FROM
....

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-08-01 : 12:43:16

Thanks Again

quote:
Originally posted by sunitabeck

Change the part shown in RED:
....
SELECT
a.reqNo,
STUFF(b.String,1,1,'') as String
FROM
....

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-01 : 14:37:10

You are welcome .)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources