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 6:35 am
How many places was this posted? This is the third that I've seen.
Many, maybe most, of the people who read the forums & answer questions regularly hit most of the forums. Cross-posting just leads to confusion because of multiple conversations about the same topic and frustration by those that are trying to help. Please don't do it.
"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 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply