Computed column possibility

Question

Post reply

Computed column possibility

EasyBoy

SSCertifiable

Points: 5301
More actions
December 6, 2016 at 11:09 am

#316606

We have applicationpro column in main table asp_profile. We are using view to refer the asp_profile and one more table. Applicationpro column from the view to main SP. I have attached definition of SP and View.
This view is too large and SP takes long time to execute. I prefer not to read Applicationpro column from view, rather read directly from main table asp_profile. Is there any way i can create computed column to avoid view and read from main table?

Viewing 5 posts - 1 through 4 (of 4 total)

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

drew.allen SSC Guru Points: 76987 More actions · Answer 1

Here is the text.

create procedure sp_dailyreq

.....

SELECT u.id,

u.NAME,

ra.reqid substring (substring (p.applicationpro, charindex('ReqStartDate', p.applicationpro), charindex('</ReqStartDate',p.applicationpro) - charindex('ReqStartDate',p.applicationpro) ), charindex('>', substring(p.applicationpro, charindex('ReqStartDate', p.applicationpro), charindex('</ReqStartDate',p.applicationpro) - charindex('ReqStartDate', p.applicationpro))) + 1, len(substring(p.applicationpro, charindex('ReqStartDate',p.applicationpro), charindex('</ReqStartDate',p.applicationpro) - charindex('ReqStartDate',p.applicationpro)))) AS reqstartdate

FROM [aspUsers] AS u WITH(nolock)

INNER JOIN [vw_aspUser] AS p WITH(nolock)

ON u.id = p.id

INNER JOIN .....

CREATE VIEW [vw_aspUser] AS

SELECT p.id,

p.NAME,

Substring(Substring(P.propertyvaluesstring, Charindex('ApplicationPro', P.propertyvaluesstring), Charindex('</ApplicationPro', P.propertyvaluesstring) - Charindex('ApplicationPro', P.propertyvaluesstring)), Charindex('>', Substring(P.propertyvaluesstring, Charindex('ApplicationPro', P.propertyvaluesstring), Charindex('</ApplicationPro', P.propertyvaluesstring) - Charindex('ApplicationPro', P.propertyvaluesstring))) + 1, Len(Substring(P.propertyvaluesstring, Charindex('ApplicationPro', P.propertyvaluesstring), Charindex('</ApplicationPro', P.propertyvaluesstring) - Charindex('ApplicationPro', P.propertyvaluesstring)))) AS ApplicationPro

FROM dbo.asp_profile p AS P

INNER JOIN dbo.asp_users u AS U

ON U.id = P.id

Given the strings that you're searching on, your data is in XML format, and you should be using XML functions instead of manipulating it with string functions.

For instance, this should give you the same results.

CREATE VIEW [vw_aspUser] AS

SELECT p.id,

p.NAME,

P.propertyvaluesstring.value('//ApplicationPro[1]/text()', 'VARCHAR(50)') AS ApplicationPro

FROM dbo.asp_profile p AS P

INNER JOIN dbo.asp_users u AS U

ON U.id = P.id

It's not only going to perform better, it's much easier to read.

Drew

Edit: Added /text() to the XPath.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

EasyBoy SSCertifiable Points: 5301 More actions · Answer 2

Thanks for reply. But, how can i use that column into main SP?

drew.allen SSC Guru Points: 76987 More actions · Answer 3

EasyBoy (12/6/2016)
Thanks for reply. But, how can i use that column into main SP?

It's no different from any other column. You either reference it indirectly through the view or you add the table to the stored procedure and reference it directly with the exact same syntax.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

EasyBoy SSCertifiable Points: 5301 More actions · Answer 4

Can you please illustrate through the example?

I was trying to use the column (P.propertyvaluesstring.value('//ApplicationPro[1]/text()', 'VARCHAR(50)') AS ApplicationPro) as you mentioned in the view but i am getting error can't call nvarchar(max) error as this column is nvarchar(max).