April 16, 2009 at 2:58 pm
This cursor isn't terribly complicated, but it's a step in a long query that is a pain to run and has to be run a few times a month.
We think the speed will improve from 6min for this code (well the real code with real data we run) if we can use a Set Based approach. I put some thought into it and I don't see how it's possible without using a cursor/loop or just running the code in many statements.
What we have is a widget and in each record the widget can be worth a different amount. Widgets are categorized into categories (1,2,3,4,etc) which each have meanings. Each Client has a MaxAmount they can portion out for Widgets.
So using this information for each widget we need to find out if each widget can charge it's full amount and place that amount into AmountTrimmed. Each item from Category 1 would get first dibs on the aportionment, then Category 2, etc.
Basically each widget needs to check to see if it can apply all or a portion of it's Amount by finding the sum of all AmountTrimmed for the Client and store that in AmountTrimmed - either the full amount or the remaining amount. After category 1 grabs it's apportionment, category 2 can try to take some amount. It doesn't matter which widget within a category gains the amount, just that the next category cannot claim an amount until the current category has used all of their amount and has not exceeded the MaxAmount.
This is using SS2000.
IF object_id('tempdb..#WidgetNumbers') IS NOT NULL
BEGIN
DROP TABLE #WidgetNumbers
END
CREATE TABLE #WidgetNumbers (
IdINT
,ClientIdINT
,WidgetIdINT
,AmountINT
,MaxAmount INT
,Category INT
,AmountTrimmed INT
)
INSERT INTO #WidgetNumbers
SELECT 1 ,1,1,5,10,1,NULL
UNION ALL SELECT 2 ,1,2,3,10,2,NULL
UNION ALL SELECT 3 ,1,3,2,10,1,NULL
UNION ALL SELECT 4 ,1,4,5,10,3,NULL
UNION ALL SELECT 5 ,2,6,5,25,1,NULL
UNION ALL SELECT 6 ,2,3,5,25,2,NULL
UNION ALL SELECT 7 ,2,4,5,25,2,NULL
UNION ALL SELECT 8 ,2,2,5,25,3,NULL
UNION ALL SELECT 9 ,2,1,5,25,4,NULL
UNION ALL SELECT 10,3,1,5,3 ,1,NULL
UNION ALL SELECT 11,3,3,5,3 ,1,NULL
UNION ALL SELECT 12,3,2,5,3 ,1,NULL
UNION ALL SELECT 13,3,9,5,3 ,1,NULL
UNION ALL SELECT 14,3,8,5,3 ,1,NULL
UNION ALL SELECT 15,3,7,5,3 ,1,NULL
DECLARE @ID INT, @Cat INT, @ClientId INT, @Amount INT, @MaxAmount INT
DECLARE @SumAmmountForWidget INT
DECLARE WidgetNumber_Cursor CURSOR FOR
SELECT WN.id, WN.category, WN.ClientId, WN.Amount, WN.MaxAmount
FROM #WidgetNumbers WN
ORDER BY ClientId, Category, WidgetId
OPEN WidgetNumber_Cursor
FETCH NEXT FROM WidgetNumber_Cursor
INTO @ID , @Cat , @ClientId , @Amount , @MaxAmount
WHILE @@FETCH_STATUS = 0
BEGIN
-- Find the number of hours already claimed for this Student/Month
SELECT @SumAmmountForWidget =
(SELECT SUM(ISNULL(SSC.AmountTrimmed, 0)) FROM #WidgetNumbers SSC
WHERE SSC.ClientId = @ClientId)
IF (@SumAmmountForWidget + @Amount <= @MaxAmount)
BEGIN
UPDATE #WidgetNumbers SET AmountTrimmed = @Amount WHERE Id = @ID
END
ELSE
BEGIN
UPDATE #WidgetNumbers SET AmountTrimmed = @MaxAmount - @SumAmmountForWidget WHERE Id = @ID
END
-- Reset defaults and grab next record
SELECT @SumAmmountForWidget = 0
FETCH NEXT FROM WidgetNumber_Cursor
INTO @ID , @Cat , @ClientId , @Amount , @MaxAmount
END
CLOSE WidgetNumber_Cursor
DEALLOCATE WidgetNumber_Cursor
SELECT * FROM #WidgetNumbers
[/CODE]
I attempted to do this as an update statement, but hit two snags:
1. I need to get the values for each widgets AmountTrimmed within the client and it's not being set until a widget is processed. So Widget 2 for Client 1 would need to access a sum of all AmountTrimmed for the client to see whats been used and what amount is left. This doesn't work in an update because the whole transaction is applied after everything is done, so all the values will be 0 (zero).
2. This needs to be performed in an order. Category 1 needs to apply all their widgets before category 2 tries. This is where I *could* hard code this by running an update once for categories 1-100, but thats no guarantee that it will cover all the categories (could have thousands in theory). This is where a loop could run through them.
#1 is usually my normal goto for a cursor, when I need to access the calculated fields I've updated in further fields. Well sometimes I can do this as a Set followed by another set using the previous set's calculated fields.
So if anyone has some advice on how to find the calculated results of a field while mid update and how to order the update, I would be very grateful. grateful both in saving a little time running this and more importantly in learning some new Set Based technique!
April 20, 2009 at 4:52 pm
This just looks like a variation of a running totals problem.
Take a look at this; I believe it can be adapted to your purposes.
If running total equals or exceeds max then trim = 0.
Reset counts at client breaks.
http://www.sqlservercentral.com/Files/Solving%20running%20total%20and%20ordinal%20rank.sql/337.sql
Note: There may be issues you should be aware of when you finally get to SQL2k5 or 2008.
Watch this space:
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
[/url]
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 20, 2009 at 5:35 pm
Wow, I'm glad I'd asked. I didn't realize you could use variables in this way, let alone what seems to be assigning the results to multiple containers.
I do run SS2005 as well, but this issue was on an old application securely rooted in 2000 for the next few months so I wanted to see if there was a 2000 solution for this problem. I'll take a look at that other page when working on similar tasks in 2005.
Thank you very much for pointing me at this! I'll post back if I"m having any issues, but I think I'm fine with this
April 21, 2009 at 8:07 am
You're very welcome.
Besides the link to the article Jeff Moden wrote, there is another article out there by Lynn Pettis which concerns running totals over partitioned tables. I'm sure you can find it now that you know what to search for, but I forgot to mention it last time.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply