Performance / Timing issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Performance / Timing issue

Author

Topic

ampo
Starting Member

6 Posts

Posted - 2009-08-05 : 10:49:06

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-05 : 11:13:49

ugh - seperate database for each account, cursor, dynamic sql. That's a nasty design - I'm not surprised it takes minutes to run.

Your "start" and "end" print statements are probably just showing down to minutes. Are you sure its not 3 minutes rather than 3 seconds?

change this:
print 'Start/END ' + cast(getdate() as varchar(50))

to this:
print 'Start/END ' + convert(varchar(23), getdate(), 121)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources