the single parameter (an int) that it expects, it takes 6
minutes to run. It's just a select statement with the
parameter in the where clause. When I copy this to QA and
declare a variable as int, set it to the same parameter I
passed to the SP and use it in the where clause, it takes
4 seconds to run in QA. Not sure why this is happening.
SQL 2000, SP3a. I'm running these on the same server.
Here's the SP, just so you can see what it looks like:
CREATE PROCEDURE usp_ATLAS_HistoricalAdvanceData_Select
(
@.AdvRecordIDint
)
AS
/************************************************** ********
********************
* Name: usp_ATLAS_HistoricalAdvanceData_Select
* Desc: A stored procedure to return the latest modified
Advance record from the history.
*
* Return values:
*
* Called by:
*
* Parameters:
*Input
*--
*@.AdvRecordIDint
*
*
* Date: 11/7/2003
************************************************** *********
********************
* Change History
************************************************** *********
********************
* Date:Author:Description:
* ---
************************************************** *********
********************/
BEGIN -- main procedure
SET NOCOUNT ON
Select
hst.AdvRecordID,
hst.CustomerID,
adt.AdvTypeID,
hst.AdvTypeCode,
hst.AdvanceID,
hst.SpecialOfferID,
hst.PrePayOptionsCode,
hst.Term,
tmu.TermUnitID,
hst.TermUnitName as TermUnit,
hst.MatDate,
hst.TradeDate,
hst.SettleDate,
hst.TransDate,
hst.Amt,
st1.ApprovalStatusID,
st2.ActivationStatusID,
hst.TransContactFirstName,
hst.TransContactLastName,
dbo.ufn_ATLAS_GetSwapNumbers
(hst.AdvRecordID) as SwapNum,
dbo.ufn_ATLAS_GetARBNumber
(hst.AdvRecordID, 1) AS ARBNumber,
adr.AdvRate as CurrentRate,
hst.UsesLondonHoliday,
hst.AuthorizedContactFlag,
hst.ReissueFlag,
ppo.PrePayOptionsID,
ppo.PrePayOptionsDesc as PrepaymentOption,
cst.CustID AS BusinessCustomerID,
cst.CustomerName AS CustomerFullName,
cst.Address,
cst.City,
cst.State AS StateCode,
cst.Zip,
hst.ModByLogin
From GUTSHistoryAdvances hst
Inner Join GUTSAdvanceTypes adt On adt.ActiveFlag
= 1
And adt.AdvTypeCode = hst.AdvTypeCode
Inner Join vw_ATLAS_CustomerInfo cst ON
hst.CustomerID = cst.CustomerID
Inner Join GUTSApprovalStatuses st1 On
st1.ActiveFlag = 1
And st1.ApprovalStatusCode =
hst.ApprovalStatusCode
Inner Join GUTSActivationStatuses st2 On
st2.ActiveFlag = 1
And st2.ActivationStatusCode =
hst.ActivationStatusCode
Inner Join GUTSPrePayOptions ppo On ppo.ActiveFlag
= 1
And hst.PrePayOptionsCode = ppo.PrePayOptionsCode
Inner Join GUTSTermUnits tmu On tmu.ActiveFlag = 1
And tmu.TermUnitName = hst.TermUnitName
left outer JOin GUTSAdvanceRates adr On
adr.ActiveFlag = 1
And hst.AdvRecordID = adr.AdvRecordID
And Not adr.AdvRate Is Null
And adr.RateEndDate Is Null
Where hst.ActiveFlag = 1
And hst.AdvRecordID = @.AdvRecordID
And hst.ToDate = (select max(ToDate) from
GUTSHistoryAdvances Where ActiveFlag = 1 And AdvRecordID =
hst.AdvRecordID)
RETURN(0)
END -- main procedure
As you can see, @.AdvRecordID is the only parameter. From
QA with the select statement pasted and @.AdvRecordID
declared as int and used in the where clause this takes 4
seconds. With the same AdvRecordID used in the SP (called
in QA) it takes 6 minutes.
Any thoughts?
Thanks,
Van
Although unlikely to cause such a difference, it might be a problem of an
out-of-date execution plan compiled into the sp in cache. Can you try
running sp_recompile usp_ATLAS_HistoricalAdvanceData_Select then run it
twice and check the second running time.
Regards,
Paul Ibison
|||Van,
take your set nocount on out of the begin ... end block. In fact you should
remove the begin/end altogether -- it has no reason to be there. See
whether this works. It should not be due to recompilation since that is
what you do in the QA when first time running the query.
Quentin
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:b84d01c43790$d48a0590$a101280a@.phx.gbl...
> I have a strange issue. When I execute an SP and pass it
> the single parameter (an int) that it expects, it takes 6
> minutes to run. It's just a select statement with the
> parameter in the where clause. When I copy this to QA and
> declare a variable as int, set it to the same parameter I
> passed to the SP and use it in the where clause, it takes
> 4 seconds to run in QA. Not sure why this is happening.
> SQL 2000, SP3a. I'm running these on the same server.
> Here's the SP, just so you can see what it looks like:
> CREATE PROCEDURE usp_ATLAS_HistoricalAdvanceData_Select
> (
> @.AdvRecordID int
> )
> AS
> /************************************************** ********
> ********************
> * Name: usp_ATLAS_HistoricalAdvanceData_Select
> * Desc: A stored procedure to return the latest modified
> Advance record from the history.
> *
> * Return values:
> *
> * Called by:
> *
> * Parameters:
> * Input
> * --
> * @.AdvRecordID int
> *
> *
> * Date: 11/7/2003
> ************************************************** *********
> ********************
> * Change History
> ************************************************** *********
> ********************
> * Date: Author: Description:
> * -- -- --
> --
> ************************************************** *********
> ********************/
> BEGIN -- main procedure
> SET NOCOUNT ON
> Select
> hst.AdvRecordID,
> hst.CustomerID,
> adt.AdvTypeID,
> hst.AdvTypeCode,
> hst.AdvanceID,
> hst.SpecialOfferID,
> hst.PrePayOptionsCode,
> hst.Term,
> tmu.TermUnitID,
> hst.TermUnitName as TermUnit,
> hst.MatDate,
> hst.TradeDate,
> hst.SettleDate,
> hst.TransDate,
> hst.Amt,
> st1.ApprovalStatusID,
> st2.ActivationStatusID,
> hst.TransContactFirstName,
> hst.TransContactLastName,
> dbo.ufn_ATLAS_GetSwapNumbers
> (hst.AdvRecordID) as SwapNum,
> dbo.ufn_ATLAS_GetARBNumber
> (hst.AdvRecordID, 1) AS ARBNumber,
> adr.AdvRate as CurrentRate,
> hst.UsesLondonHoliday,
> hst.AuthorizedContactFlag,
> hst.ReissueFlag,
> ppo.PrePayOptionsID,
> ppo.PrePayOptionsDesc as PrepaymentOption,
> cst.CustID AS BusinessCustomerID,
> cst.CustomerName AS CustomerFullName,
> cst.Address,
> cst.City,
> cst.State AS StateCode,
> cst.Zip,
> hst.ModByLogin
>
> From GUTSHistoryAdvances hst
> Inner Join GUTSAdvanceTypes adt On adt.ActiveFlag
> = 1
> And adt.AdvTypeCode = hst.AdvTypeCode
> Inner Join vw_ATLAS_CustomerInfo cst ON
> hst.CustomerID = cst.CustomerID
> Inner Join GUTSApprovalStatuses st1 On
> st1.ActiveFlag = 1
> And st1.ApprovalStatusCode =
> hst.ApprovalStatusCode
> Inner Join GUTSActivationStatuses st2 On
> st2.ActiveFlag = 1
> And st2.ActivationStatusCode =
> hst.ActivationStatusCode
> Inner Join GUTSPrePayOptions ppo On ppo.ActiveFlag
> = 1
> And hst.PrePayOptionsCode = ppo.PrePayOptionsCode
> Inner Join GUTSTermUnits tmu On tmu.ActiveFlag = 1
> And tmu.TermUnitName = hst.TermUnitName
> left outer JOin GUTSAdvanceRates adr On
> adr.ActiveFlag = 1
> And hst.AdvRecordID = adr.AdvRecordID
> And Not adr.AdvRate Is Null
> And adr.RateEndDate Is Null
> Where hst.ActiveFlag = 1
> And hst.AdvRecordID = @.AdvRecordID
> And hst.ToDate = (select max(ToDate) from
> GUTSHistoryAdvances Where ActiveFlag = 1 And AdvRecordID =
> hst.AdvRecordID)
> RETURN(0)
> END -- main procedure
>
>
> As you can see, @.AdvRecordID is the only parameter. From
> QA with the select statement pasted and @.AdvRecordID
> declared as int and used in the where clause this takes 4
> seconds. With the same AdvRecordID used in the SP (called
> in QA) it takes 6 minutes.
> Any thoughts?
> Thanks,
> Van
>
|||That could have been it. I eventually dropped and
recreated the SP and it runs fine now...strange. I didn't
get a change to do the sp_recomplile, but I'll keep that
in mind incase in comes up again.
>--Original Message--
>Although unlikely to cause such a difference, it might be
a problem of an
>out-of-date execution plan compiled into the sp in cache.
Can you try
>running sp_recompile
usp_ATLAS_HistoricalAdvanceData_Select then run it
>twice and check the second running time.
>Regards,
>Paul Ibison
>
>.
>
|||"Quentin Ran" <ab@.who.com> wrote in message
news:%23U6%23IS5NEHA.1160@.TK2MSFTNGP09.phx.gbl...
> take your set nocount on out of the begin ... end block. In fact you
should
> remove the begin/end altogether -- it has no reason to be there. See
Quentin,
Why would moving the SET NOCOUNT out of the block change the runtime of
the query? Just curious because I also use BEGIN/END blocks in my stored
procedures with SET NOCOUNT within the blocks, and I've never had a problem
with them. I use them because I occasionally need to script out databases
into single text files and I find that the BEGIN/END blocks make it easier
for humans to navigate through the massive amount of text. I guess a
comment at the beginning and end would accomplish the same thing... But
habits die hard
|||> take your set nocount on out of the begin ... end block. In fact you
should
> remove the begin/end altogether -- it has no reason to be there.
Why do you think that removing SET NOCOUNT ON will improve performance?
Also, while it is purely subjective, I am surprised that so many people
don't see the benefits of using a BEGIN / END block around the procedure
body. I find it immensely useful when looking at a properly-indented script
comprising multiple stored procedures...
A
|||Quentin,
that's true, but the distinction is that rerunning the sp will pull an old
compiled plan from cache, which is potentially inaccurate, while Van was
comparing this to a newly compiled plan from QA which was quicker.
Regards,
Paul Ibison
|||Paul,
I agree with the outdated exec plan part -- I missed it. What I was saying
was that a recompilation is effectively the same when you run the query in
the QA the first time -- the query will be compiled there.
Quentin
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:en$K8a5NEHA.556@.TK2MSFTNGP10.phx.gbl...
> Quentin,
> that's true, but the distinction is that rerunning the sp will pull an old
> compiled plan from cache, which is potentially inaccurate, while Van was
> comparing this to a newly compiled plan from QA which was quicker.
> Regards,
> Paul Ibison
>
|||Adam,
I was not saying "this will work". What I was saying was "see whether this
works" (I know my post looks bad together with the miss of the "outdated
exec plan"). The reason for my uncertainty is what I saw with not having
"set nocount on" -- it is not a simple performance degrader. It sometimes
takes WAY longer than what's needed to just return the "count". I had a
case where with "set nocount on" the proc runs for seconds, and without it
it runs for minutes. I don't know what it is, and privately communicated
with a recognized SQL expert and we were not able to get a plausible
conclusion. It was so frustrating for me that I want to share this even
though I do not know the mechanism of the problem. I certainly agree when
you need the count, you put it there (but make sure it does not make problem
for you). In the original post, it is not needed.
Quentin
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:#Ta1BY5NEHA.628@.TK2MSFTNGP11.phx.gbl...
> "Quentin Ran" <ab@.who.com> wrote in message
> news:%23U6%23IS5NEHA.1160@.TK2MSFTNGP09.phx.gbl...
> should
> Quentin,
> Why would moving the SET NOCOUNT out of the block change the runtime
of
> the query? Just curious because I also use BEGIN/END blocks in my stored
> procedures with SET NOCOUNT within the blocks, and I've never had a
problem
> with them. I use them because I occasionally need to script out databases
> into single text files and I find that the BEGIN/END blocks make it easier
> for humans to navigate through the massive amount of text. I guess a
> comment at the beginning and end would accomplish the same thing... But
> habits die hard
>
|||If your SP will cause such a problems on permanent basis then you should
think about adding WITH RECOMPILE option onto it
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:b89a01c43795$04dbe070$a101280a@.phx.gbl...[vbcol=seagreen]
> That could have been it. I eventually dropped and
> recreated the SP and it runs fine now...strange. I didn't
> get a change to do the sp_recomplile, but I'll keep that
> in mind incase in comes up again.
> a problem of an
> Can you try
> usp_ATLAS_HistoricalAdvanceData_Select then run it
No comments:
Post a Comment