Re: Stored procedure tuning
From: Vladimir Vasiliev (vasiliev_at_kaspersky.com)
Date: 02/19/04
- Next message: Uri Dimant: "Re: BULK COPY changes row order importing Text file"
- Previous message: Trapulo: "error with"
- In reply to: Vladimir Vasiliev: "Re: Stored procedure tuning"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 19 Feb 2004 14:53:15 +0300
Hi
I got the point, the problem was due to
SET CONCAT_NULL_YIELDS_NULL OFF
option at the start of the procedure!
Anyway big thanx!
"Vladimir Vasiliev" <vasiliev@kaspersky.com> wrote in message
news:%23sMcK1t9DHA.4084@tk2msftngp13.phx.gbl...
> Hi Ray,
>
> Thank you for suggestion,
>
> I think that calling another stored proc could be considered as a last
> resort.
> Is there any other possible way of problem resolution?
>
> Vladimir
>
> "Ray Higdon" <sqlhigdon@nospam.yahoo.com> wrote in message
> news:%23eTVZkt9DHA.1596@TK2MSFTNGP10.phx.gbl...
> > In your IF statement you might consider calling another stored proc to
do
> > the update as it may improve your cached plan.(For both)
> >
> > --
> > Ray Higdon MCSE, MCDBA, CCNA
> > ---
> > "Vladimir Vasiliev" <vasiliev@kaspersky.com> wrote in message
> > news:%2363d2At9DHA.2368@TK2MSFTNGP11.phx.gbl...
> > > Hello!
> > >
> > > I've got stored proc in which the is an IF condition that is always
> false.
> > > Within that IF clause there is an UPDATE statement.
> > > I examine Reads and Duration values reported by the SQL Profiler while
> > > executing that proc.
> > > I normally get 1800 reads and 170 ms of duration.
> > > And then I remove that UPDATE (that is never executed!) and get 1300
> reads
> > > and 70 ms of duration!!!!!
> > >
> > > Can somebody tell me how could I solve this.
> > >
> > > The problem is that originally the was a condition that checks if
input
> > > parameter is specified and if specified than we must perform the
update.
> > But
> > > this optimization does not work as expected.
> > >
> > > Thanx in advance.
> > >
> > > CREATE PROCEDURE dbo.sp_SetDeletedFlag
> > > (
> > > @i_nObjectId int,
> > > @i_strComment nvarchar(4000) = null
> > > )
> > > AS
> > > ...
> > > -- IF NOT @i_strComment IS NULL
> > > IF 1=0
> > > BEGIN
> > >
> > > UPDATE dbo.vw_ObjProc
> > > SET Notes = @i_strComment
> > > WHERE
> > > ObjectId=@i_nObjectId
> > >
> > > END
> > >
> > > RETURN 0
> > >
> > >
> >
> >
>
>
- Next message: Uri Dimant: "Re: BULK COPY changes row order importing Text file"
- Previous message: Trapulo: "error with"
- In reply to: Vladimir Vasiliev: "Re: Stored procedure tuning"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
