| Author |
 Topic  |
|
|
jim_jim
Constraint Violating Yak Guru
USA
277 Posts |
 Posted - 07/31/2012 : 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
876 Posts |
Posted - 07/31/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 07/31/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 07/31/2012 : 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
USA
277 Posts |
Posted - 08/01/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3442 Posts |
Posted - 08/01/2012 : 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
USA
277 Posts |
Posted - 08/01/2012 : 11:23:18
|
| to support applications created using infopath and some ad hoc reporting using Business Objects |
|
|
|
jim_jim
Constraint Violating Yak Guru
USA
277 Posts |
Posted - 08/01/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/01/2012 : 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
USA
277 Posts |
Posted - 08/01/2012 : 12:43:16
|
Thanks Againquote:
Originally posted by sunitabeck
Change the part shown in RED:....
SELECT
a.reqNo,
STUFF(b.String,1,1,'') as String
FROM
....
|
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/01/2012 : 14:37:10
|
| You are welcome .) |
|
|
| |
Topic |
|
performance of a view
