Pause long procedure to avoid other users stalling

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 2000 Forums

Transact-SQL (2000)

Pause long procedure to avoid other users stalling

Author

Topic

Mathias
Posting Yak Master

119 Posts

Posted - 2004-05-21 : 06:45:09

I have a calculation procedure that takes apparently all the resources of the two CPU of the server.
With certain values, the procedure takes all the resources and block the other users. The procedure last from few seconds with no apparent blocking for other users for simple values to 15 minutes with more complicated values. When reaching a certain amount of data, it blocks the other processes. Is there a way to add some line in the code to pause the procedure and let the other user get the hand back?
Thanks, Paul

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-05-21 : 11:19:50

Pleast post the procedure with an estimate of row counts of very large tables.

There is no "yield with no locks" or anything similar, but I suspect there are ways to rewrite the procedure to prevent blocks and to speed it up.

Mathias
Posting Yak Master

119 Posts

Posted - 2004-05-24 : 08:53:09

ALTER procedure dbo.usp_PAU_Auto_Cash_Check_Log @LogNb numeric(10,4), @DEBUG as varchar(10)='NoDebug'
/*Will release logged instruction automatically if there is enough cash*/
AS
set NOCOUNT ON
declare @Err Int
declare @Return nchar(2)
declare @CurrentDateAndTimeChar char(14)
declare @CurrentDate char(10)
declare @CurrentDateAndTime smalldatetime
declare @CurrentUser varchar(25)
declare @myRecNum int
declare @ShortCode int
declare @Cif int
declare @Status varchar(40)
declare @BasedOnStatus varchar(40)
declare @NewStatus varchar(40)--for the based on, the new status of current record
declare @BasedOnLogNb numeric(10,4)
declare @Amount numeric(18,2)
declare @ConfirmedStatus varchar(40)
declare @ptrval binary(16)
declare @MyLogHis nvarchar(2000)
declare @BREACH varchar(300)
declare @CREDIT_USAGE varchar(300)
declare @MinimumBal int
declare @MinimumBalCCY char(3)
declare @LastRunningBal numeric(18,2)
declare @CancelMinimumBal char(1)

set @Return=char(13)+char(10)
Set @CurrentDateAndTime=getdate()
Set @CurrentDateAndTimeChar= dbo.usf_FormatDate(@CurrentDateAndTime,'yy-mm-dd hh:nn')
Set @CurrentUser=right(system_user, len(system_user)-charindex('\',system_user))
Set @CurrentDate= dbo.usf_FormatDate(@CurrentDateAndTime,'yyyy-mm-dd')

/*Update The LogHis field if necessary*/
UPDATE TRANSACTIONS
SET LOG_HIS=''
WHERE floor(LOG_NB)=floor(@LogNb) and
IDX_NB=dbo.usf_A_IDX_Table('TRANSACTIONS') and
LOG_HIS is null

/*Update the Log with the previous status*/
DECLARE R0 SCROLL CURSOR FOR
SELECT
t.REC_NUM,
t.SHORT_CODE,
f.CIF,
t.STATUS_,
CASE WHEN t.STATUS_ COLLATE SQL_Latin1_General_CP1_CI_AS in
(SELECT STATUS_ from TRANSACTION_STATUS_TO_BE_CLOSED_V) THEN 'Confirmed'
WHEN t.STATUS_ COLLATE SQL_Latin1_General_CP1_CI_AS in
(SELECT STATUS_ from TRANSACTION_STATUS_TO_BE_HIDDEN_V) THEN 'Hidden'
WHEN t.AMOUNT>0 THEN 'Logged In'
WHEN t.AMOUNT<0 THEN 'Logged Out' END,
'<tr>'+@Return +
'<td>'+@CurrentUser+' Auto Cash Check</td>'+@Return +
'<td>'+@CurrentDateAndTimeChar+'</td>'+@Return +
'<td>Field:TRANSACTIONS_STATUS_</td>'+@Return +
'<td>Old value:'+t.STATUS_+'</td>'+@Return +
'<td>New value:Approved outgoing</td>'+@Return +
'</tr>'+@Return +@Return,
isnull(f.MINIMUM_BAL,0) as 'MINIMUM_BAL',
isnull(f.MINIMUM_BAL_CCY,'USD') as 'MINIMUM_BAL_CCY',
TEXTPTR(t.LOG_HIS),
t.BASED_ON,
CASE when t.BASED_ON is null then ''
when floor(t.BASED_ON)=t.BASED_ON then (select STATUS_ from TRANSACTIONS where REC_NUM=t.BASED_ON)
else (select STATUS_ from TRANSACTIONS where LOG_NB=t.BASED_ON and IDX_NB=dbo.usf_A_IDX_Table('TRANSACTIONS'))
end as BASED_ON_STATUS,
t.AMOUNT
FROM TRANSACTIONS t, FUNDS f
where
floor(t.LOG_NB)=floor(@LogNb) and
t.IDX_NB=dbo.usf_A_IDX_Table('TRANSACTIONS') and
t.SHORT_CODE=f.SHORT_CODE and
f.IDX_NB=dbo.usf_A_IDX_Table('FUNDS') and
t.STATUS_ COLLATE SQL_Latin1_General_CP1_CI_AS in
(SELECT STATUS_ from TRANSACTION_STATUS_AUTO_CASH_CHECK_V
UNION SELECT STATUS_ from TRANSACTION_STATUS_TO_BE_CLOSED_V)
order by t.LOG_NB

OPEN R0
FETCH NEXT FROM R0 INTO @myRecNum, @ShortCode, @CIF, @Status, @ConfirmedStatus, @MyLogHis, @MinimumBal, @MinimumBalCCY, @ptrval,@BasedOnLogNb , @BasedOnStatus, @Amount
set @Err=@@ERROR
IF @Err>0 GOTO ErrHandler
PRINT 'Auto Check CURSOR declaration ok'

WHILE @@FETCH_STATUS<>-1
BEGIN
IF (@@FETCH_STATUS <> -2) and @ConfirmedStatus<>'Hidden'
BEGIN
--BASED ON stuff ---------------------------------------------------
PRINT 'Start BASED ON'+ cast(@myRecNum as varchar(20))+'@BasedOnStatus='+isnull(@BasedOnStatus,'NULL')
IF @BasedOnLogNb is not null and
@Status not in (SELECT STATUS_ from TRANSACTION_STATUS_TO_BE_HIDDEN_V)
BEGIN
Set @NewStatus=''
IF @BasedOnStatus in (SELECT STATUS_ from TRANSACTION_STATUS_BLOCKED_V)
Set @NewStatus=(case when @Amount>0 then 'Blocked In' else 'Blocked Out' end)
ElSE
IF @Status like 'Blocked%'
Set @NewStatus=(case when @Amount>0 then 'Logged only in' else 'Logged only in' end)
PRINT '@BasedOnStatus='+@BasedOnStatus+' New Status='+@NewStatus
IF @NewStatus<>''
BEGIN
UPDATE TRANSACTIONS
Set STATUS_=@NewStatus, SUPERVISOR_STATUS=''
WHERE REC_NUM=@myRecNum
Set @MyLogHis=Replace(@MyLogHis,'Approved outgoing',@NewStatus)
Set @MyLogHis=Replace(@MyLogHis,'Auto Cash Check','Based On Check')

/*single line update, insert at the beginning*/
UPDATETEXT TRANSACTIONS.LOG_HIS @ptrval 0 0 @MyLogHis
set @Err=@@ERROR
IF @Err>0 GOTO ErrHandler
PRINT 'One log rolled back during Based on:'+cast(@myRecNum as varchar(20))

IF @NewStatus like 'Blocked%'
exec dbo.usp_POOL_update_tree_for_one_CIF @CIF, @BREACH output,@CREDIT_USAGE output, @LastRunningBal Output, @Debug

FETCH RELATIVE 0 FROM R0 INTO @myRecNum, @ShortCode, @CIF, @Status, @ConfirmedStatus, @MyLogHis, @MinimumBal, @MinimumBalCCY, @ptrval, @BasedOnLogNb,@BasedOnStatus, @Amount
END
END
-- END of Based on stuff --------------------------------------------
---------------------------------------------------------------------
IF @Status not like 'Blocked%'
BEGIN
IF @ConfirmedStatus ='Confirmed' --we go ahead without further approval
BEGIN
exec dbo.usp_POOL_update_tree_for_one_CIF @CIF, @BREACH output,@CREDIT_USAGE output, @LastRunningBal Output, @Debug
--exec usp_PAU_update_one_ShortCode @ShortCode, @BREACH output,@CREDIT_USAGE output, @LastRunningBal Output
PRINT 'Confirmed already: REC_NUM='+ cast(@myRecNum as varchar(20))+' - BREACH= '+isnull(@BREACH,'Null') +' - last running bal='+cast(isnull(@LastRunningBal,-0.11) as varchar(25))
END
ELSE
BEGIN
---------------------------
PRINT 'Start the auto check'+cast(@myRecNum as varchar(20))
BEGIN TRANSACTION
UPDATE TRANSACTIONS
SET STATUS_=(SELECT CASE WHEN @ConfirmedStatus='Logged Out' Then 'Approved outgoing'
WHEN @ConfirmedStatus='Logged In' Then 'Confirmed receipt not posted'
ELSE STATUS_ END)
WHERE REC_NUM=@myRecNum

IF @ConfirmedStatus='Logged In'
Select @MyLogHis=Replace(@MyLogHis,'Approved outgoing','Confirmed receipt not posted')

/*single line update, insert at the beginning*/
UPDATETEXT TRANSACTIONS.LOG_HIS @ptrval 0 0 @MyLogHis
set @Err=@@ERROR
IF @Err>0 GOTO ErrHandler
PRINT 'One log ready to be commited :'+cast(@myRecNum as varchar(20))

IF @MinimumBal<>0 and @ConfirmedStatus<>'Logged In'
BEGIN
SAVE TRANSACTION MinimumBalCheck
PRINT 'Minimal Balance Transaction'
INSERT INTO TRANSACTIONS
( CURRENCY, AMOUNT, VALUE_DATE, DESCRIPT, TYP, STATUS_, CREATION_DATE,
SHORT_CODE, CREATED_BY , IDX_NB)
SELECT
@MinimumBalCCY,
-@MinimumBal,
@CurrentDate,
'MINIMUM BALANCE CHECK NO LOG',
CASE WHEN @MinimumBal>0 then 'Money Out' else 'Money In' end,
CASE WHEN @MinimumBal>0 then 'Approved outgoing' else 'Confirmed receipt not posted' end,
@CurrentDateAndTime,
@ShortCode,
@CurrentUser,
dbo.usf_A_IDX_Table('TRANSACTIONS')

END

exec dbo.usp_POOL_update_tree_for_one_CIF @CIF, @BREACH output,@CREDIT_USAGE output, @LastRunningBal Output, @DEBUG

IF @MinimumBal<>0 and @LastRunningBal<0 and @ConfirmedStatus <>'Logged In'
Set @CancelMinimumBal='Y'
ELSE
Set @CancelMinimumBal='N'

IF @DEBUG<>'NoDebug'
PRINT 'REC_NUM='+ cast(@myRecNum as varchar(20))+' - BREACH= '+isnull(@BREACH,'Null') +' - last running bal='+cast(isnull(@LastRunningBal,-0.11) as varchar(25))

/*select SHORT_CODE, @CancelMinimumBal, AMOUNT, STATUS_, BAL_USD, @LastRunningBal as LAST_BAL, @MinimumBal as LIMIT, @BREACH from transactions where short_code=@ShortCode
order by VALUE_DATE, AMOUNT DESC*/

IF ( @BREACH in('No Breach','BREACH Minimum Balance!') and @CancelMinimumBal='N' )
OR @ConfirmedStatus ='Logged In'
BEGIN
IF @MinimumBal<>0 and @ConfirmedStatus <>'Logged In' /*we just need to rollback the checking process*/
BEGIN
ROLLBACK TRANSACTION MinimumBalCheck
exec dbo.usp_POOL_update_tree_for_one_CIF @CIF, @BREACH output,@CREDIT_USAGE output, @LastRunningBal Output
END
COMMIT TRANSACTION
PRINT 'One Log commited :'+cast(@myRecNum as varchar(20))
END
----------------------XXXX-----------
ELSE
BEGIN
IF @MinimumBal<>0
ROLLBACK TRANSACTION MinimumBalCheck
ROLLBACK TRANSACTION

UPDATE TRANSACTIONS
SET STATUS_=(SELECT CASE WHEN @ConfirmedStatus='Logged Out' Then 'Unapproved until suff cash'
WHEN @ConfirmedStatus='Logged In' Then 'Expected proceed'
ELSE STATUS_ END)
WHERE REC_NUM=@myRecNum

IF @ConfirmedStatus='Logged Out'
Set @MyLogHis=Replace(@MyLogHis,'Approved outgoing','Unapproved until suff cash')
ELSE
Set @MyLogHis=Replace(@MyLogHis,'Approved outgoing','Expected proceed')

/*single line update, insert at the beginning*/
UPDATETEXT TRANSACTIONS.LOG_HIS @ptrval 0 0 @MyLogHis
set @Err=@@ERROR
IF @Err>0 GOTO ErrHandler
PRINT 'One log rolled back :'+cast(@myRecNum as varchar(20))

END
-------------------------------
END
END
END
Set @Breach=''
FETCH NEXT FROM R0 INTO @myRecNum, @ShortCode, @Cif, @Status, @ConfirmedStatus, @MyLogHis, @MinimumBal, @MinimumBalCCY, @ptrval,@BasedOnLogNb, @BasedOnStatus, @Amount
END
CLOSE R0
DEALLOCATE R0
set @Err=@@ERROR
IF @Err>0 GOTO ErrHandler
PRINT 'All the batch completed'
RETURN

--The procedure is working fine in 95% of the case. It will take a stack of transactions (up to 90) and see for each of them if there is enough money. For this, it will start with the balance of the account (wich can contain up to 26 different currencies=rows). This procedure causes problem when it is called 90 times in a row for a client that manages up to 26 currencies.
The number of transactions is about 1300 rows.
Thanks, Paul

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-05-24 : 09:33:00

Most of your problem is to do with the cursor....you should be thinking SET-BASED because SQL is designed to work on SET's of data.....unless the processing of row#1 affects the processing of row#2, there should be no need for cursor-type processing....

There is also (99.99999% of the time) a big performance gain involved in moving away from cursors...search here for cursor + performance for examples of what I am talking about.

If/when the procedure is speeded up, then the length of time that locks exist....will be minimised.

If you were able to supply 'sample' input data, sample DDL for the tables involved and 'sample matching expected/actual results'...this could be advanced a lot faster.

Mathias
Posting Yak Master

119 Posts

Posted - 2004-05-25 : 07:11:31

The procedure is used to check a block of incoming/outgoing cash transactions in the Bank. One single instruction will influent the next one since the balances are changing. In the design, I store the account balances on each line. While it could be argued not to store any calculation, people that have read access get the latest stored calculation. Other users get a fully recalculated balance. The balances are also not simple balances because the underlying credit structure could be pyramidal (a sum of sub account) up to 3 levels (one calculation is necessary by level). That is why some of the procedure are using recursive functions to get the start balance.

It will take me some time to get a set of data ready. I will need to create a database with the strict necessary. Let me know if at least I could avoid blocking the other users by for example forcing the use of only one processor?
Thanks

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-05-25 : 07:56:57

using only one processor...will have no 'programmed/controlled' effect....remember these are multi-tasking operating systems that will be running the SQL Server....as such, multiple users can independently request the SP to be run...and the OS will run multiple versions of the SP at the same time....having only one processor will just deliver poorer normal performance....given that multiple processors are meant to deliver better performance.
it will deliver no guarantees on the locks/blocks.

I presume you have looked at the execution plans.....

Mathias
Posting Yak Master

119 Posts

Posted - 2004-05-25 : 10:50:51

How can I setup the profiler to see where it hangs?

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-25 : 13:59:17

Go to your menu and run PROFILER and change the template options and filters...

Is this in Production?

If I did a code review, I'd have made it an ealier re-write canidate

I wonder what the data model looks like...

Good Luck...

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources