August 5, 2009 at 8:50 am
Hello.
I have this strange issue:
runing the following stored procedure (from Managment studio), return after more then 130 seconds.
I added 'print' inside the code of the stored procedure and the time dif between START and END is 3 seconds...
How can it be?
This is the SP:
---------------
ALTER PROCEDURE [dbo].[SpName]
AS
Declare @FromDate Datetime
Declare @ToDate Datetime
print 'START ' + cast(getdate() as varchar(50))
Set @ToDate = SystemConfig.dbo.Fnc_GetMidnightOfDate(Getdate())
Set @ToDate = DateAdd(dd, -Day(@ToDate)+1, @ToDate)
Set @FromDate = DateAdd(mm, -1, @ToDate)
CREATE TABLE #AATmpTable (AccountID int,
BB int,
CHB_By_TrxData int,
ClearingProcessing int,
CLR_By_TrxData int,
ManuallyInserted int)
Select 1 as 'Header',
'' as 'Black list status checked',
@FromDate as 'From System Date',
@ToDate as 'To System Date'
set nocount on
insert into #AATmpTable
select
OriginAccountID as AccountID,
sum(case Reason when 0 then 1 else 0 end) as BB,
0,
sum(case Reason when 1 then 1 else 0 end) as ClearingProcessing,
0,
sum(case Reason when 2 then 1 else 0 end) as ManuallyInserted
From dbo.SomeTblName with (nolock)
Where UpdateTime >= @FromDate
And UpdateTime < @ToDate
Group By OriginAccountID
DECLARE @DB_Name char(50)
DECLARE @ACCOUNT_ID int
DECLARE @sql char(1000)
DECLARE crs CURSOR READ_ONLY
FOR
SELECT AccountID FROM #AATmpTable
OPEN crs
FETCH NEXT FROM crs
INTO @ACCOUNT_ID
WHILE @@FETCH_STATUS = 0
BEGIN
set @DB_Name = (select TxDBName from accountsconfig.dbo.txaccounts where accountid = @ACCOUNT_ID)
set @sql = 'update #AATmpTable set CHB_By_TrxData = '
+ '(select count(*) from '+ rtrim(ltrim(@DB_Name)) +'.dbo.transactiondata with (nolock) where TxProcessingStartTime between @' + convert(varchar(10), @FromDate , 120) + '@ and @'+ convert(varchar(10), @ToDate , 120) +'@ and InstructionType = 5 and TxInternalErrorID = 0) '
+ ' where AccountId = ' + cast(@ACCOUNT_ID as char(10))
set @sql = replace(@SQL, '@', '''')
EXEC(@SQL)
SET @sql = ''
set @sql = 'update #AATmpTable set CLR_By_TrxData = '
+ ' (select count(CCNumber) from '+ rtrim(ltrim(@DB_Name)) +'.dbo.transactiondata with (nolock)'
+ ' where instructiontype in (1, 2)'
+ ' and TxProcessingStartTime between @' + convert(varchar(10), @FromDate , 120) + '@ and @'+ convert(varchar(10), @ToDate , 120) +'@'
+ ' and ClearingInternalErrorCode in (001, 002, 005))'
+ ' where AccountId = ' + cast(@ACCOUNT_ID as char(10))
set @sql = replace(@SQL, '@', '''')
EXEC(@SQL)
FETCH NEXT FROM crs
INTO @ACCOUNT_ID
END
CLOSE crs
DEALLOCATE crs
select * from #AATmpTable
print 'END ' + cast(getdate() as varchar(50))
drop table #AATmpTable[/i]
August 6, 2009 at 3:18 am
what happens when you remove the PRINT statement again, does it still run in 130 seconds.
It sounds to me as if the procedure is cached.
August 6, 2009 at 6:29 am
That could be the compile time and the time needed to move the data from disk to memory and from there to your machine. All that adds up. Instead of print statements, try using SET STATISTICS TIME to see the accumalated times, including compile time & execution time. You can also use a trace, set up through Profiler, to capture accurate execution time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply