Problem with SP vs QA

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
(
@.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

Problem with SP vs QA

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,
VanAlthough 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

Problem with SP vs QA

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,
VanAlthough 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...
> > 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 :)
>|||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...
> 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

problem with select and int statement

hey the following code doesnt worki it comes up with an error Specified cast is not valid. pointing at the exceutescalar

any ideas?

cheers

string maxquery = "Select Max(activity_order) from roomactivitylk where room_code = 'v1106'";

//int max = 0;
int max;

SqlCommand cmd15 = new SqlCommand();
cmd15.Connection = con;
cmd15.CommandText = maxquery;
max = (int)cmd15.ExecuteScalar();

++max;
max++;
max = max + 1;
tbtest1.Text = max.ToString();

I'm not shure, but AFAIK aggregate functions (like MAX()) usually requires GROUP BY clause.

Try modify line with error such way.

max = cmd15.ExecuteScalar();

And look what woul be max equal to in debugger.

Looks like your query returns result which simply can't be parsed to integer.

|||

Your query is OK. Run the query in SQL directly, make sure it return integer. If not integer, you need to set it as 0 value.

|||

Be sure to check to see what happens if it returns NULL as well. I don't believe your code would work if that were the case.

problem with query

Hi

I have the following bit of code

string test0001 = "Select Max(activity_order) from roomactivitylk";

int max;

SqlCommand cmd15 = new SqlCommand();
cmd15.Connection = con;
cmd15.CommandText = test0001;
max = (int)cmd15.ExecuteScalar();


max = max + 1;

what it does is add 1 to the value max which is taken from the database

however it seems to be set to 0 as everytime it brings back 0 even though the next incrment value should be 2

any suggestions?

cheerts

Hi

I am not sure which parts of the code is in a loop. But i guess, the "int max" declaration should be outside of the loop (if it is already not).

Hope this helps.

VJ

|||

My guess is u want to perform autoincrement . If i m right i think u may not get it because when there r no records exist check the return value.u may get null if u get null make it as 1 or increment with the max value. Plz reply to me am i rt or wrong

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

Hi

thanks for the replys manmaged to sort it

this is the soltuion

string maxquery = "Select Max(activity_order) from roomactivitylk ";
//cmd14.ExecuteScalar();

int max;

SqlCommand cmd15 = new SqlCommand();
//SqlCommand cmd15 = new SqlCommand(test0001, con);
cmd15.Connection = con;
cmd15.CommandText = maxquery;
max = (int)cmd15.ExecuteNonQuery();


max = max + 2;
++max;
max++;

ps yes i was tring to increment:-)

cheers!!