February 12, 2010 at 8:01 am
Hi Elias
Thanks for being so patient.
Don't forget - always test.
-- data setup
DROP TABLE #Working
DROP TABLE #zzzCovers
CREATE TABLE #zzzCovers(
[CollateralID] [nvarchar](50) NOT NULL,
[CollateralTypeID] [varchar](4) NOT NULL,
[CollateralPriority] [smallint] NULL,
[CollateralValue] [decimal](18, 3) NULL,
[ObligorID] [nvarchar](50) NOT NULL,
[AccountID] [nvarchar](50) NOT NULL,
[FacilityTypeID] [smallint] NOT NULL,
[FacilityPriority] [smallint] NULL,
[FacilityValue] [decimal](18, 3) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT #zzzCovers ([CollateralID], [CollateralTypeID], [CollateralPriority], [CollateralValue], [ObligorID], [AccountID], [FacilityTypeID], [FacilityPriority], [FacilityValue]) VALUES (N'1', N'1', 1, CAST(100000.000 AS Decimal(18, 3)), N'1', N'101', 1, 1, CAST(190000.000 AS Decimal(18, 3)))
INSERT #zzzCovers ([CollateralID], [CollateralTypeID], [CollateralPriority], [CollateralValue], [ObligorID], [AccountID], [FacilityTypeID], [FacilityPriority], [FacilityValue]) VALUES (N'1', N'1', 1, CAST(100000.000 AS Decimal(18, 3)), N'1', N'102', 1, 1, CAST(130000.000 AS Decimal(18, 3)))
INSERT #zzzCovers ([CollateralID], [CollateralTypeID], [CollateralPriority], [CollateralValue], [ObligorID], [AccountID], [FacilityTypeID], [FacilityPriority], [FacilityValue]) VALUES (N'2', N'1', 1, CAST(10000.000 AS Decimal(18, 3)), N'2', N'201', 1, 1, CAST(150000.000 AS Decimal(18, 3)))
INSERT #zzzCovers ([CollateralID], [CollateralTypeID], [CollateralPriority], [CollateralValue], [ObligorID], [AccountID], [FacilityTypeID], [FacilityPriority], [FacilityValue]) VALUES (N'2', N'1', 1, CAST(10000.000 AS Decimal(18, 3)), N'3', N'301', 1, 1, CAST(30000.000 AS Decimal(18, 3)))
INSERT #zzzCovers ([CollateralID], [CollateralTypeID], [CollateralPriority], [CollateralValue], [ObligorID], [AccountID], [FacilityTypeID], [FacilityPriority], [FacilityValue]) VALUES (N'3', N'1', 1, CAST(20000.000 AS Decimal(18, 3)), N'1', N'101', 1, 1, CAST(190000.000 AS Decimal(18, 3)))
INSERT #zzzCovers ([CollateralID], [CollateralTypeID], [CollateralPriority], [CollateralValue], [ObligorID], [AccountID], [FacilityTypeID], [FacilityPriority], [FacilityValue]) VALUES (N'3', N'1', 1, CAST(20000.000 AS Decimal(18, 3)), N'1', N'102', 1, 1, CAST(130000.000 AS Decimal(18, 3)))
INSERT #zzzCovers ([CollateralID], [CollateralTypeID], [CollateralPriority], [CollateralValue], [ObligorID], [AccountID], [FacilityTypeID], [FacilityPriority], [FacilityValue]) VALUES (N'4', N'1', 1, CAST(80000.000 AS Decimal(18, 3)), N'3', N'301', 1, 1, CAST(30000.000 AS Decimal(18, 3)))
INSERT #zzzCovers ([CollateralID], [CollateralTypeID], [CollateralPriority], [CollateralValue], [ObligorID], [AccountID], [FacilityTypeID], [FacilityPriority], [FacilityValue]) VALUES (N'5', N'1', 1, CAST(110000.000 AS Decimal(18, 3)), N'1', N'102', 2, 2, CAST(50000.000 AS Decimal(18, 3)))
INSERT #zzzCovers ([CollateralID], [CollateralTypeID], [CollateralPriority], [CollateralValue], [ObligorID], [AccountID], [FacilityTypeID], [FacilityPriority], [FacilityValue]) VALUES (N'6', N'1', 2, CAST(30000.000 AS Decimal(18, 3)), N'1', N'101', 1, 1, CAST(190000.000 AS Decimal(18, 3)))
-- Create a processing order pk for the quirky update, and new surrogate keys for Collateral and Facility
-- ObligorID + AccountID + FacilityTypeID is the primary key for facilities
-- CollateralID + CollateralTypeID is the primary key for collaterals
SELECT ExOrder = ROW_NUMBER() OVER (ORDER BY s.FacilityPriority, s.CollateralPriority, s.FacilityValue DESC, s.CollateralValue DESC,
s.ObligorID, s.AccountID, s.FacilityTypeID, s.CollateralID, s.CollateralTypeID),
c.ColID, f.FacID,
s.CollateralID, s.CollateralTypeID, s.CollateralPriority, s.CollateralValue, s.ObligorID, s.AccountID, s.FacilityTypeID, s.FacilityPriority, s.FacilityValue,
CollateralRemaining = CAST(0 AS [decimal](12, 3)),
FacilityCoverage = CAST(0 AS [decimal](12, 3)),
FacilityRemaining = CAST(0 AS [decimal](12, 3)),
CAST(',' AS VARCHAR(MAX)) AS CollateralStore,
CAST(',' AS VARCHAR(MAX)) AS FacilityStore
INTO #Working
FROM #zzzCovers s
INNER JOIN (
SELECT ColID = ROW_NUMBER() OVER (ORDER BY CollateralID, CollateralTypeID), CollateralID, CollateralTypeID
FROM #zzzCovers
GROUP BY CollateralID, CollateralTypeID
) c ON c.CollateralID = s.CollateralID AND c.CollateralTypeID = s.CollateralTypeID
INNER JOIN (
SELECT FacID = ROW_NUMBER() OVER (ORDER BY ObligorID, AccountID, FacilityTypeID), ObligorID, AccountID, FacilityTypeID
FROM #zzzCovers
GROUP BY ObligorID, AccountID, FacilityTypeID
) f ON f.ObligorID = s.ObligorID AND f.AccountID = s.AccountID AND f.FacilityTypeID = s.FacilityTypeID
CREATE CLUSTERED INDEX IdExOrder ON #Working (ExOrder)
-- /data setup
-- Solution
DECLARE @CollateralStore VARCHAR(MAX), @FacilityStore VARCHAR(MAX), @CollateralRemaining [decimal](18, 3), @FacilityRemaining [decimal](18, 3)
SELECT @CollateralStore = ',', @FacilityStore = ','
UPDATE #Working SET
-- retrieve collateral balances from store
@CollateralRemaining = CASE
WHEN @CollateralStore LIKE '%,' + CAST(ColID AS VARCHAR) + '(%'
THEN ISNULL(CAST(NULLIF(SUBSTRING(@CollateralStore,
1 + LEN(CAST(ColID AS VARCHAR(6)) + '(') + CHARINDEX((','+CAST(ColID AS VARCHAR(6))+'('), @CollateralStore, 1),
9),'') AS [decimal](12, 3)), 0) -- get collateral remaining from @CollateralStore
ELSE CollateralValue END,
-- retrieve facility balances from store
@FacilityRemaining = CASE
WHEN @FacilityStore LIKE '%,' + CAST(FacID AS VARCHAR) + '(%'
THEN ISNULL(CAST(NULLIF(SUBSTRING(@FacilityStore,
1 + LEN(CAST(FacID AS VARCHAR(6)) + '(') + CHARINDEX((','+CAST(FacID AS VARCHAR(6))+'('), @FacilityStore, 1),
9),'') AS [decimal](12, 3)), 0) -- get facility remaining from @FacilityStore
ELSE FacilityValue END,
-- update values in table
CollateralRemaining =
CASE WHEN @CollateralRemaining <= @FacilityRemaining THEN 0 ELSE @CollateralRemaining - @FacilityRemaining END,
FacilityRemaining =
CASE WHEN @CollateralRemaining >= @FacilityRemaining THEN 0 ELSE @FacilityRemaining - @CollateralRemaining END,
FacilityCoverage = CASE WHEN @CollateralRemaining <= @FacilityRemaining THEN @CollateralRemaining ELSE @FacilityRemaining END,
-- save collateral balances to store, and carry over
@CollateralStore = CollateralStore =
CASE WHEN @CollateralStore LIKE '%,' + CAST(ColID AS VARCHAR) + '(%'
THEN -- update the collateral balance in the store using @CollateralRemaining
STUFF(@CollateralStore, LEN(CAST(ColID AS VARCHAR(6)) + '(') + CHARINDEX((','+CAST(ColID AS VARCHAR(6))+'('), @CollateralStore, 1), 14,'(' + STR(CASE WHEN @CollateralRemaining <= @FacilityRemaining THEN 0 ELSE @CollateralRemaining - @FacilityRemaining END, 12, 3) + ')')
ELSE -- create a record for this collateral using @CollateralRemaining
@CollateralStore + CAST(ColID AS VARCHAR(6))+ '(' + STR(CASE WHEN @CollateralRemaining <= @FacilityRemaining THEN 0 ELSE @CollateralRemaining - @FacilityRemaining END, 12, 3) + '),'
END,
-- save facility balances to store, and carry over
@FacilityStore = FacilityStore =
CASE WHEN @FacilityStore LIKE '%,' + CAST(FacID AS VARCHAR) + '(%'
THEN -- update the facility balance in the store using @facilityRemaining
STUFF(@FacilityStore, LEN(CAST(FacID AS VARCHAR(6)) + '(') + CHARINDEX((','+CAST(FacID AS VARCHAR(6))+'('), @FacilityStore, 1), 14,'(' + STR(CASE WHEN @CollateralRemaining >= @FacilityRemaining THEN 0 ELSE @FacilityRemaining - @CollateralRemaining END, 12, 3) + ')')
ELSE -- create a record for this facility using @facilityRemaining
@FacilityStore + CAST(FacID AS VARCHAR(6))+ '(' + STR(CASE WHEN @CollateralRemaining >= @FacilityRemaining THEN 0 ELSE @FacilityRemaining - @CollateralRemaining END, 12, 3) + '),'
END
-- /Solution
-- check results
SELECT CollateralID, CollateralTypeID, CollateralPriority, CollateralValue, ObligorID, AccountID,
FacilityTypeID, FacilityPriority, FacilityValue, CollateralRemaining, FacilityCoverage, FacilityRemaining
FROM #Working
----------------------
Cheers
ChrisM
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 12, 2010 at 2:11 pm
Hi Chris,
Its a smart and elegant solution that indeed produces right results.
However, I was (and I still am) somewhat sceptical about keeping all these data in strings in ram. At first glance I thought that it is rather unlikely that the space offered by VARCHAR(MAX) string could be enough for the 1.000.000 facilities or the 850.000 collaterals of the production database but I was probably wrong. Besides, if the performance is high enough, maybe I could split the problem in several parts (for example per collateral priority) and use your approach.
Then I ran your script in my development environment for 19592 records (a subset of the sample)after removing test fields CollateralStore and FacilityStore. It returned after 1 hour and 3' with right results. The performance seems absolutely unacceptable, BUT I noticed that, as it is, the solution uses as stores variable lenght strings (varchar) and it adds parts to them as it runs. Moreover the searches and seeks in them are sequential. So, in order to examine the viability of the solution, I think it is necessary to replace the stores with fixed length strings with preset ordered placeholders for totals and to apply a fast seek (through SUBSTRING and STUFF) based on ColID and FacID instead of sequential search. Removing the orders from the string has the additional benefit of reducing the lenght of the entries. Moreover I will try to find a more compact format to store numbers into a string [as the type is decimal(18,3) which means we need 21 characters per entry]. Any ideas on it?
Finally, if the ordering of the #Covers file is that I described in my last message (I meen the initial approach without the loops) then one only store is enough (FacilityStore) while the ordering cares about keeping the collateral's running totals in one variable.
Since it is 11:10 PM I'll leave the above tests for tomorrow and, of course, I'll keep you informed.
Many thanks and best regards,
Elias
February 12, 2010 at 2:35 pm
Hi Elias
Thanks for giving it a try. "Make it work, make it fast, make it pretty". There's plenty of scope for improvement.
Firstly, as you suggest, perhaps only one string store could be used, which would cut the processing time in half. You're the best judge of that - can either the facilities or collaterals run contiguously?
Secondly, when the last occurrence of a collateral (or facility, whichever is maintained by the string store) has been reached, running through the table in execution order, then that collateral could be excised from the store hence reducing the overall string length. I think this would be an excellent starter because the code to do this is straightforward and at a guess it would significantly improve performance. ROW_NUMBER OVER (PARTITION BY ColID ORDER BY ColID, ExOrder) compared to COUNT(*) OVER(PARTITION BY ColID ORDER BY ColID, ExOrder) or something similar will identify the last occurrence.
Please post back your results at the end of your working day tomorrow, I'm intrigued to see how this "mongrel" solution performs when it's been fitted to task.
Have fun and good luck.
Cheers
ChrisM@home
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 13, 2010 at 4:55 am
Hi Elias
Here's version 2. The facility string store has been removed and replaced with conventional processing. Collateral balances are only stored when they need to be, and are removed when they are finished with. There's some extra pre-processing of the source table to enable this tuning of collateral handling.
-- data setup
-- ##########
-- Create a processing order pk for the quirky update, and new surrogate keys for Collateral and Facility
-- ObligorID + AccountID + FacilityTypeID is the primary key for facilities
-- CollateralID + CollateralTypeID is the primary key for collaterals
drop table #Working
SELECT ExOrder = ROW_NUMBER() OVER (ORDER BY s.FacilityPriority,
s.FacilityValue DESC,
s.ObligorID, s.AccountID, s.FacilityTypeID,
s.CollateralPriority,
s.CollateralValue DESC,
s.CollateralID, s.CollateralTypeID),
c.ColID,
ColCount = COUNT(*) OVER (PARTITION BY c.ColID),
ColInstance = ROW_NUMBER() OVER (PARTITION BY c.ColID ORDER BY s.FacilityPriority,
s.FacilityValue DESC,
s.ObligorID, s.AccountID, s.FacilityTypeID,
s.CollateralPriority,
s.CollateralValue DESC,
s.CollateralID, s.CollateralTypeID),
f.FacID,
s.CollateralID, s.CollateralTypeID, s.CollateralPriority, s.CollateralValue, s.ObligorID, s.AccountID, s.FacilityTypeID, s.FacilityPriority, s.FacilityValue,
CollateralRemaining = CAST(0 AS [decimal](12, 3)),
FacilityCoverage = CAST(0 AS [decimal](12, 3)),
FacilityRemaining = CAST(0 AS [decimal](12, 3))
INTO #Working
FROM zzzCovers s
INNER JOIN (
SELECT ColID = ROW_NUMBER() OVER (ORDER BY CollateralID, CollateralTypeID),
CollateralID, CollateralTypeID
FROM zzzCovers
GROUP BY CollateralID, CollateralTypeID
) c ON c.CollateralID = s.CollateralID AND c.CollateralTypeID = s.CollateralTypeID
INNER JOIN (
SELECT FacID = ROW_NUMBER() OVER (ORDER BY ObligorID, AccountID, FacilityTypeID),
ObligorID, AccountID, FacilityTypeID
FROM zzzCovers
GROUP BY ObligorID, AccountID, FacilityTypeID
) f ON f.ObligorID = s.ObligorID AND f.AccountID = s.AccountID AND f.FacilityTypeID = s.FacilityTypeID
CREATE CLUSTERED INDEX IdExOrder ON #Working (ExOrder)
-- /data setup
-- Solution
DECLARE @CollateralStore VARCHAR(MAX), @FacilityStore VARCHAR(MAX),
@CollateralRemaining [decimal](18, 3), @FacilityRemaining [decimal](12, 3), @FacilityCarriedOver [decimal](12, 3),
@FacID INT
SELECT @FacID = 0, @CollateralStore = ',', @FacilityStore = ','
UPDATE #Working SET
-- retrieve collateral balance from store
@CollateralRemaining =
CASE
-- first visit so won't be in @CollateralStore
WHEN ColInstance = 1 THEN CollateralValue
-- get collateral remaining from @CollateralStore
WHEN @CollateralStore LIKE '%,' + CAST(ColID AS VARCHAR) + '(%'
THEN ISNULL(CAST(NULLIF(SUBSTRING(@CollateralStore,
1 + LEN(CAST(ColID AS VARCHAR(6)) + '(') + CHARINDEX((','+CAST(ColID AS VARCHAR(6))+'('), @CollateralStore, 1),
9),'') AS [decimal](12, 3)), 0)
ELSE CollateralValue END,
-- retrieve facility balance
@FacilityRemaining = CASE WHEN @FacID = FacID THEN @FacilityCarriedOver ELSE FacilityValue END,
-- update values in table
CollateralRemaining = CASE WHEN @CollateralRemaining <= @FacilityRemaining THEN 0 ELSE @CollateralRemaining - @FacilityRemaining END,
FacilityRemaining = CASE WHEN @CollateralRemaining >= @FacilityRemaining THEN 0 ELSE @FacilityRemaining - @CollateralRemaining END,
FacilityCoverage = CASE WHEN @CollateralRemaining <= @FacilityRemaining THEN @CollateralRemaining ELSE @FacilityRemaining END,
-- save collateral balance to store, and carry over
@CollateralStore =
CASE
-- only one visit to collateral, no need to store
WHEN ColCount = 1
THEN @CollateralStore
-- last visit to collateral, remove from store
WHEN ColInstance = ColCount
THEN STUFF(@CollateralStore, CHARINDEX((','+CAST(ColID AS VARCHAR(6))+'('), @CollateralStore, 1), 16, '')
-- append a 'record' for this collateral using @CollateralRemaining
WHEN ColInstance = 1
THEN @CollateralStore + CAST(ColID AS VARCHAR(6))+ '(' + STR(CASE WHEN @CollateralRemaining <= @FacilityRemaining THEN 0 ELSE @CollateralRemaining - @FacilityRemaining END, 12, 3) + '),'
-- update the collateral balance in the store using @CollateralRemaining
ELSE
STUFF(@CollateralStore, LEN(CAST(ColID AS VARCHAR(6)) + '(') + CHARINDEX((','+CAST(ColID AS VARCHAR(6))+'('), @CollateralStore, 1), 14,'(' + STR(CASE WHEN @CollateralRemaining <= @FacilityRemaining THEN 0 ELSE @CollateralRemaining - @FacilityRemaining END, 12, 3) + ')')
END,
-- save facility balance to store, and carry over
@FacilityCarriedOver = CASE WHEN @CollateralRemaining >= @FacilityRemaining THEN 0 ELSE @FacilityRemaining - @CollateralRemaining END,
-- obtain warning of change of facility
@FacID = FacID
-- /Solution
-- check results
SELECT CollateralID, ColInstance, ColCount, CollateralTypeID, CollateralPriority, CollateralValue, ObligorID, AccountID,
FacilityTypeID, FacilityPriority, FacilityValue, CollateralRemaining, FacilityCoverage, FacilityRemaining
FROM #Working
ORDER BY ExOrder
----------------------
Cheers
ChrisM
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 13, 2010 at 7:08 am
Chris, not bad at all. Your new script runs in just 2' 18'' on the same sample (vs 1h 3' of the previous version). I liked your trick with ColCount; it gets rid of the collaterals that appear only once. I think it is very usefull even for my initial solution as the percentage of collaterals of this kind is more than 25%.
I also tried to apply your idea of using a VARCHAR(MAX) to store data and here is the code I reached at:
IF OBJECT_ID('tempdb..#Covers') IS NOT NULL DROP TABLE #Covers
-- Create #Covers table
;WITH Facilities AS
(
SELECT
*,
ROW_NUMBER() OVER(ORDER BY ObligorID, AccountID, FacilityTypeID) AS FacID
FROM
dbo.zzzFacilities
)
SELECT
dbo.zzzCollaterals.CollateralID,
dbo.zzzCollaterals.CollateralTypeID,
dbo.zzzCollaterals.CollateralPriority,
dbo.zzzCollaterals.CollateralValue,
Facilities.ObligorID,
Facilities.AccountID,
Facilities.FacilityTypeID,
Facilities.FacilityPriority,
Facilities.FacilityValue,
FacID,-- a unique ID per facility
CAST(0 AS DECIMAL (18,3)) AS CoveredAmount, -- The field stores the results
-- The following fields are used for CoveredAmount calculation
CAST(0 AS FLOAT) AS CollateralCoverage,
CAST(0 AS DECIMAL (18,3)) AS CollateralUsed
--CAST(0 AS DECIMAL (18,3)) AS PreviousCovers
INTO
#Covers
FROM
dbo.zzzConnections
INNER JOIN dbo.zzzCollaterals
ON dbo.zzzConnections.CollateralID = dbo.zzzCollaterals.CollateralID
AND dbo.zzzConnections.CollateralTypeID = dbo.zzzCollaterals.CollateralTypeID
INNER JOIN Facilities
ON dbo.zzzConnections.ObligorID = Facilities.ObligorID
AND dbo.zzzConnections.AccountID = Facilities.AccountID
AND dbo.zzzConnections.FacilityTypeID = Facilities.FacilityTypeID
-- CollateralCoverage is used for ensuring that the collaterals which
-- do not cover the associated facilities are fully exploited (take
-- higher percentage. Not a crucial assumption but sometimes leads to
-- better results.
UPDATE
#Covers
SET
#Covers.CollateralCoverage = #Covers.CollateralValue /
CASE WHEN ColCoverage.TotalExposuresCovered > 0.1 THEN
ColCoverage.TotalExposuresCovered
ELSE
0.1
END
FROM
#Covers
INNER JOIN (
SELECT
CollateralID,
CollateralTypeID,
SUM (FacilityValue) AS TotalExposuresCovered
FROM
#Covers
GROUP BY
CollateralID,
CollateralTypeID ) ColCoverage
ON #Covers.CollateralID = ColCoverage.CollateralID
AND #Covers.CollateralTypeID = ColCoverage.CollateralTypeID
-- Create a clustered index to prioritize #Covers' rows
CREATE CLUSTERED INDEX IX_#Covers ON #Covers (
CollateralPriority,
CollateralCoverage,
CollateralID,
CollateralTypeID,
FacilityPriority,
ObligorID,
AccountID)
DECLARE@PreviousCovers DECIMAL(18,3),
@AmountToCover DECIMAL(18,3),
@LastCollateralID NVARCHAR (50),
@CollateralUsed DECIMAL (18,3),
@CoveredAmount DECIMAL (18,3),
@FaclilityStore VARCHAR(MAX)
-- Allocate collaterals to the facilities of the current order
SELECT@LastCollateralID = '',
@CollateralUsed = 0,
@FaclilityStore = ''
;WITH tally AS
(
SELECT TOP (20000)
ROW_NUMBER() OVER (ORDER BY t1.ID) AS N
FROM Master.sys.SysColumns t1
CROSS JOIN Master.sys.SysColumns t2
)
SELECT @FaclilityStore = @FaclilityStore + '0000000000000000000000' -- A placeholder
FROM tally
SELECT LEN(@FaclilityStore) AS LEN, DATALENGTH(@FaclilityStore) AS DATALENGTH
BEGIN TRY
UPDATE
#Covers
SET
@PreviousCovers = ISNULL(CAST(SUBSTRING(@FaclilityStore, (FacID - 1) * 22, 22) AS DECIMAL(18,3)), 0),
@AmountToCover =
CASE WHEN FacilityValue > @PreviousCovers THEN
FacilityValue - @PreviousCovers
ELSE
0
END,
@CoveredAmount =
CASE WHEN @LastCollateralID = CollateralID THEN
CASE WHEN CollateralValue - @CollateralUsed < @AmountToCover THEN
CollateralValue - @CollateralUsed
ELSE
@AmountToCover
END
ELSE
CASE WHEN CollateralValue - CollateralUsed < @AmountToCover THEN
CollateralValue - CollateralUsed
ELSE
@AmountToCover
END
END,
@CollateralUsed =
CASE WHEN @LastCollateralID = CollateralID THEN
@CollateralUsed + @CoveredAmount
ELSE
CollateralUsed + @CoveredAmount
END,
@LastCollateralID = CollateralID,
CoveredAmount = @CoveredAmount,
@PreviousCovers = @PreviousCovers + @CoveredAmount,
@FaclilityStore = STUFF(@FaclilityStore, (FacID - 1) * 22, 22, CAST(@PreviousCovers AS CHAR(22))),
CollateralUsed = @CollateralUsed
FROM
#Covers
OPTION (MAXDOP 1)
END TRY
BEGIN CATCH
PRINT '@PreviousCovers = ' + CAST(@PreviousCovers AS NVARCHAR(100))
--PRINT '@FacID = ' + CAST(@FacID AS NVARCHAR(100))
--PRINT 'SUBSTRING(@FaclilityStore, (@FacID - 1) * 22, 22) = "' + SUBSTRING(@FaclilityStore, (@FacID - 1) * 22, 22) + '"'
END CATCH
SELECT TOP 2000 * FROM #Covers
ORDER BY
CollateralPriority,
CollateralCoverage,
CollateralID,
CollateralTypeID,
FacilityPriority,
ObligorID,
AccountID
DROP TABLE #Covers
The above code runs in 16'' (for 19592 rows) but is still slower than the code of post #863007 (10''). For my total sample of 300k rows the above script needs 43' instead of 16' of the initial solution. Your script hasn't returned yet (it runs for 1h 15').
The main reason for the low performance of the two scripts new is the function STUFF that rebuilds the string in every call. Moreover, as the size of the string gets bigger the time cost increases exponentially and I remind that in the production db there are about 1.5mio rows to be treated.
Thanks again,
Elias
February 13, 2010 at 9:51 am
Hi Elias
Thanks again for testing this out. It's a good improvement, but still way short of expectations. Looks like time for plan B :blush:
May I suggest you send me a larger set of data to test with? Your current test set of 21k rows would be ideal. I've PMd you my email address.
Cheers
ChrisM
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply