SQL Server Performance Forum – Threads Archive

No Lock condition for views

I have a view joining several tables
e.g. CREATE VIEW dbo.VView
as
Select a, b,c, d
FROMTable1 pp
JOIN table2 p ON pp.a = p.a AND pp.b= p.b
JOIN dbo.table3 pc ON pp.b = pc.b
JOIN dbo.Table4 pr ON pc.a= pr.a
JOIN dbo.table5 pt ON pr.c = pt.c
WHERE pp.a=1
Then I have a query in an SP where it queries like, SELECT a,b,c,x,y
FROM dbo.VView ps (NOLOCK)
WHERE EXISTS (select SELECT 1 FROM HPTable (NOLOCK) WHERE a= ps.a) My Question is if the NOLOCK condition here help or if I have to give nolock condition at the creation of view?
e.g. with a script like this
CREATE VIEW dbo.VView
as
Select a, b,c, d
FROMTable1 pp
JOIN table2 p (NOLOCK) ON pp.a = p.a AND pp.b= p.b
JOIN dbo.table3 pc(NOLOCK) ON pp.b = pc.b
JOIN dbo.Table4 pr (NOLOCK) ON pc.a= pr.a
JOIN dbo.table5 pt (NOLOCK) ON pr.c = pt.c
WHERE pp.a=1
It would be highly appreciated if someone can clarify this.
My problem is SP is very slow at the querry involving the view.

---

You can test these scenarios yourself in development. But, what I would suggest is check the indexes on those tables (probably create indexes on view if necessary etc). Can you post your SP?
Name
———
Dilli Grg (1 row(s) affected)

---

Hi this is the SP **************************************************** CREATE PROCEDURE [dbo].aa_sp_IQPA_Read
@Zcode char(5),
@EDate smalldatetime,
@PTypeId tinyint=NULL,
@RId int = NULL
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED DECLARE @PRatingId int
IF @PTypeId = 0
SELECT @PRatingId = NULL
ELSE
SELECT @PRatingId = PRId FROM LUPTypeD WHERE PTypeDDId = @PTypeId SET @RId = CASE @RId WHEN 1 THEN 1 ELSE NULL END SELECT
ps.PPId,
ps.SCode,
ps.MPform,
ps.PPname,
ps.PRId,
ps.PRDesc,
ps.PTypeId,
ps.PTypeDesc,
–ps.PlanID
[dbo].aa_fn_DerivePID(ps.PPId, @RId, @Zcode, @EDate, ps.MPform, SCode) as ‘PID’
FROM dbo.vwPSummary ps (NOLOCK)
JOIN dbo.aa_fn_AvailRPs (@EDate, @Zcode) ap ON ps.PlanId = ap.PlanId
WHERE EXISTS (
SELECT 1
FROM HPSAZ (NOLOCK)
WHERE SAId = ps.SAId AND Zcode = @Zcode
)
AND ps.PPId = ps.PPID
AND ps.PRId = isnull(@PRatingId, ps.PRId)
ORDER BY
ps.PRId,
ps.SOrder RETURN 1 END GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

---

Ok, first of all, there is no point of putting NOLOCK on each table since you already have SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED which is equal to NOLOCK. Second, the problem might be with the functions but not the view you are referring. See if you can replace any of these two functions and use select or may be create stored procedure. Functions are not recommended on most the cases because of the performance issue.
Name
———
Dilli Grg (1 row(s) affected)

---

Fuctions has to do row by row operations… try using using temp tables or table variables…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

]]>