Re: Views
- From: "Mike Epprecht \(SQL MVP\)" <mike@xxxxxxxxxxxx>
- Date: Wed, 20 Apr 2005 21:20:03 +0200
Then you need to post the DDL so we can look at it.
The views look no different than the average view so trying to guess what
could be wrong is not easy.
Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@xxxxxxxxxxxx
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Joh" <joh@xxxxxxxxxxxx> wrote in message
news:%23DJH%23JdRFHA.2964@xxxxxxxxxxxxxxxxxxxxxxx
> Index is already created but performance is not good... any idea ?
>
>
> "Mike Epprecht (SQL MVP)" <mike@xxxxxxxxxxxx> wrote in message
> news:eybdtXcRFHA.3144@xxxxxxxxxxxxxxxxxxxxxxx
>> Hi
>>
>> Ensure that appropriate indexes are on the column involved in the joins.
>>
>> You are issuing all your reads with NOLOCK hint. This in effect can
>> return
>> 'ghost rows' as NOLOCK indicates read uncommitted. Yes, it does improve
>> performance, but data consistency is not guaranteed.
>>
>> http://www.sql-server-performance.com
>> --
>> --------------------------------
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>>
>> IM: mike@xxxxxxxxxxxx
>>
>> MVP Program: http://www.microsoft.com/mvp
>>
>> Blog: http://www.msmvps.com/epprecht/
>>
>> "Joh" <joh@xxxxxxxxxxxx> wrote in message
>> news:O$RZMObRFHA.2604@xxxxxxxxxxxxxxxxxxxxxxx
>> > Can anyone help me regarding the optimization of the following view
>> > because
>> > the dbo.vw_Main view calls vw_St_Un view and vw_St_Un view calls
> vw_S_OnS
>> > and vw_S_Tfer view... when I execute the Main view it take long time...
>> > can
>> > one give me his / her expert idea.
>> >
>> > thanks in advance.
>> >
>> >
> ____________________________________________________________________________
>> > __________________
>> > CREATE VIEW dbo.vw_S_OnS
>> > AS
>> > SELECT dbo.LInfo.LInfoId,
>> > dbo.OnS.DPID,
>> > dbo.OnS.RCityID,
>> > dbo.OnS.SCityID,
>> > dbo.LInfo.CTID,
>> > dbo.CQ.CDate
>> > FROM dbo.OnS WITH (NOLOCK) INNER JOIN
>> > dbo.CQ WITH (NOLOCK) ON dbo.OnS.LInfoId =
>> > dbo.CQ.LInfoId RIGHT OUTER JOIN
>> > dbo.LInfo WITH (NOLOCK) ON dbo.OnS.LInfo =
>> > dbo.LInfo.LInfoId
>> > WHERE (dbo.LInfo.CTID = 1) OR
>> > (dbo.LInfo.CTID IS NULL)
>> >
>> >
>> > CREATE VIEW dbo.vw_S_Tfer
>> > AS
>> > SELECT dbo.LInfo.LInfoId,
>> > dbo.Tfer.DPId,
>> > dbo.Tfer.SCID,
>> > dbo.Tfer.RCID,
>> > dbo.LInfo.CTID,
>> > dbo.CQ.CDate
>> > FROM dbo.Tfer WITH (NOLOCK) INNER JOIN
>> > dbo.CQ WITH (NOLOCK) ON dbo.Tfer.LInfoId =
>> > dbo.CQ.LInfoId RIGHT OUTER JOIN
>> > dbo.LInfo WITH (NOLOCK) ON dbo.Tfer.LInfoId =
>> > dbo.LInfo.LInfoId
>> > WHERE (dbo.LInfo.CTID = 2)
>> >
>> >
>> > CREATE VIEW dbo.vw_St_Un
>> > AS
>> > SELECT LInfoId,
>> > DPID,
>> > RCID,
>> > SCID,
>> > CTID,
>> > CDate
>> > FROM dbo.vw_S_OnS WITH (NOLOCK)
>> > UNION ALL
>> > SELECT LInfoId,
>> > DPId,
>> > SCID,
>> > RCID,
>> > CTID,
>> > CDate
>> > FROM dbo.vw_S_Tfer WITH (NOLOCK)
>> >
>> >
>> >
>> > CREATE VIEW dbo.vw_Main
>> > AS
>> > SELECT dbo.LInfo.LInfoId,
>> > CONVERT(varchar(20), dbo.LInfo.DFID)
> AS
>> > DFID,
>> > dbo.vw_St_Un.CDate, -- Getting the
> data
>> > from View
>> > dbo.LInfo.PNum,
>> > dbo.Pt.FName,
>> > dbo.Pt.LName,
>> > dbo.FFol.FNum,
>> > CONVERT(varchar(20),dbo.FFol.ACAssi)
> AS
>> > AC,
>> > Ct_1.Des AS SC,
>> > Ct_2.Des AS RC
>> >
>> > FROM dbo.FFol WITH (NOLOCK) RIGHT OUTER JOIN
>> > dbo.Pt WITH (NOLOCK) RIGHT OUTER JOIN
>> > dbo.Ct Ct_2 WITH (NOLOCK) RIGHT OUTER
>> > JOIN
>> > dbo.vw_St_Un WITH (NOLOCK) INNER JOIN
>> > dbo.LInfo WITH (NOLOCK) ON
>> > dbo.vw_St_Un.LInfoId = dbo.LInfo.LInfoId ON
>> > Ct_2.CtID = dbo.vw_St_Un.SCID LEFT
> OUTER
>> > JOIN
>> > dbo.Ct Ct_1 WITH (NOLOCK) ON
>> > dbo.vw_St_Un.RCID = Ct_1.CtID ON
>> > dbo.Pt.LInfoId = dbo.LInfo.LInfoId ON
>> > dbo.FFol.LInfoId = dbo.LInfo.LInfoId
>> >
>> >
>> >
>> >
>> >
>> >
>>
>>
>
>
.
- Follow-Ups:
- Re: Views
- From: Joh
- Re: Views
- Prev by Date: Re: SCHEMABINDING
- Next by Date: Re: Views
- Previous by thread: Re: Views
- Next by thread: Re: Views
- Index(es):
Relevant Pages
|
|
