Gracefully Handling CLR Function Problems

Question

Gracefully Handling CLR Function Problems

Ted Zatopek

SSCommitted

Points: 1925
More actions
January 6, 2017 at 2:05 pm

#317339

We have a CLR function we use to calculate returns (specifically yield) on a set of financial assets (bonds, loans, etc.). Occasionally we have a situation where a particular asset will cause the CLR function calculation to either fail, or worse, fall into an infinite loop.
See below for how the current code looks. Sometimes we call the first function directly to calc for just one asset. Sometimes we run the second function (which calls the first via CROSS APPLY).
The issue is really in the set-version call in the second function. Say there's 1000 assets in the table.
CLR Error: If any one of the assets cause the CLR call to error, then the whole query fails. I'd like to gracefully trap the CLR failure and let the process keep going.
CLR Infinite Loop: Similarly if one CLR call goes into an infinite loop, I'd like to end the one particular call after a certain timeout and move on to the next record.
I'm looking for suggestions on how to do either. In a perfect world, there's a simple and elegant way to do this that performs at least as well as the current process. But I'm open to all suggestions
I'm currently looking at putting in a TRY CATCH block in the first one-asset function to trap the error, but that won't handle the infinite loop.
CREATE FUNCTION TVFSingleRowWrapperOnCLRToCalculateYield
(
@AssetID INT,
@Price DECIMAL(26,13)
)
-- Technically a Table Value Function but will always only return one row for the asset passed in
RETURNS @AssetPrice TABLE
(
Price FLOAT ,
Yield FLOAT
)
AS
BEGIN
DECLARE @Yield FLOAT;
DECLARE @OkayToCalculate AS BIT;
DECLARE @AssetAttribute1 FLOAT;
DECLARE @AssetAttribute2 DATE;
-- Do a bunch of stuff
-- to either gather attribute data
-- or detect that the CLR calc will have an problem and so not call the calc and instead return NULL
IF @OkayToCalculate = 1
SELECT @Yield = ( SELECT TotallySQL.YIELD( @AssetAttribute1 , @AssetAttribute2 ) )
ELSE
SELECT @Yield = NULL;
INSERT INTO @AssetPrice
( Price, Yield )
VALUES ( @Price , @Yield )
RETURN;
END
GO
CREATE FUNCTION TVFSetBasedCallToCalculateYield()
RETURNS @AssetYield TABLE
(
AssetID INT ,
Yield FLOAT
)
AS
BEGIN
INSERT INTO @AssetYield
SELECT
Asset.ID
,Asset.Price
,Calculated.Yield
FROM
dbo.Asset AS Asset
CROSS APPLY TVFSingleRowWrapperOnCLRToCalculateYield( Asset.ID, Asset.PriceL) AS Calculated;
RETURN;
END
GO

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24275 More actions · Answer 1

When SQL processes a batch of records, it's an all or nothing process. If one of them has an error, all are aborted.

The only way to do skip a problem record/batch and continue with the rest, is to create a loop, and process each record/batch with error handling within the loop.

How to post data/code on a forum to get the best help.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 2

The 2nd most graceful way to handle these wayward CLRs would be to get someone to write them correctly so that they don't cause the type of problems you're having.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ted Zatopek SSCommitted Points: 1925 More actions · Answer 3

Thanks for the responses.

Switching to a looping process was going to be my next attempt. I've found no set-based solutions yet.

I also agree that trapping for these conditions in the CLR routines itself would be best. Although my thinking is that's not in my wheelhouse, and these are fairly complicated calculations. I'm also thinking that i don't want to assume that they won't ever have problems in the future.

thanks!