March 21, 2012 at 6:49 am
Sometimes there is no avoiding cursors. I have yet to see how allocation can be done properly without using a cursor. I would be happy to use some sort of set-based allocation method, but haven't been able to find one that works as well as a cursor based method. And there is nothing wrong with cursors when there is no good alternative.
March 21, 2012 at 8:00 am
"And there is nothing wrong with cursors when there is no good alternative."
At least we agree on that 🙂
From the Oracle community I also get the impression that the main "problem with cursors" is not cursors per se, but SQL Server's incredibly inefficient implementation of them, as compared with Oracle's. When cursor performance isn't an issue then I would personally go for whatever is easiest to read, write and maintain.
March 21, 2012 at 8:10 am
I remember about a million years ago attending Cobol training when I started with Arthur Andersen. At that time, we were instructed that GOTO statements were the most evil construct ever devised and we were not to use them.
However, it turns out that the problem with GOTO statements was that improperly used, they turn a program into spaghetti while properly used, they make a program more maintainble. So, in Cobol, it is just fine to use a GOTO statement to pass control to the end of a paragraph. It is bad practice to use Cobol statements to jump all over the place.
And in SQL scripts, I use GOTO statements often, but I use them to execute a detailed section of SQL and then control comes right back to a label following the GOTO statement.
My point here is that CURSORS, like GOTO statements, are not good if used inappropriately and they are fine when used appropriately. However, over time, the message is simplified to "CURSORS bad, GOTO statements bad" with no exceptions.
March 21, 2012 at 8:15 am
dwain.c (3/21/2012)
Mr. edobrzel ,When i run with your MNO I get this result set which adds to 100:
key1key2WeightAllocAmtMNO116.0015.00
MNO216.0017.00
MNO316.0017.00
MNO416.0017.00
MNO516.0017.00
MNO616.0017.00
Oops! I guess my morning eyes were tricking me. Sorry about that. The only thing different that my procedure did was to distribute the difference, so it would be 16, 16, 17, 17, 17, 17. But as was said before, the CTE approach is generally good enough and not really incorrect. Thanks for keeping me honest.
March 21, 2012 at 9:07 am
To Tom-John and Janus Lin, sorry for the late reply on this but I believe I can address your "Top-N Fudge Rounding" approach with the sample code below. In this case, where N=3 in effect what it does is allocates to 3 sets:
- Set 1: All records not in Top-N by largest weight
- Set 2: Records 2 to N it allocates based on the weights of the subset and what remains after removing the Allocated Amt from Set 1
- Set 3: Total Alloc Amt - SUM(Alloc Amt of Set 1, Alloc Amt of Set 2)
DECLARE @Hdr TABLE
(
key1 VARCHAR(20),
Amount Money
)
DECLARE @Dtl TABLE
(
key1 VARCHAR(20),
key2 VARCHAR(20),
Weight DECIMAL(6,2)
)
INSERT INTO @Hdr
SELECT 'ABC' AS Key1, 100 As Amount
UNION ALL SELECT 'DEF', 501
UNION ALL SELECT 'GHI', 251
UNION ALL SELECT 'JKL', 151
UNION ALL SELECT 'AAA', 77
UNION ALL SELECT 'AAB', 50
INSERT INTO @Dtl (key1, key2, Weight)
SELECT 'ABC' AS key1, '1' AS Key2, 33 AS Weight
UNION ALL SELECT 'ABC', '2', 33
UNION ALL SELECT 'ABC', '3', 33
UNION ALL SELECT 'AAB', '1', 99
UNION ALL SELECT 'DEF', '2', 25
UNION ALL SELECT 'DEF', '3', 34
UNION ALL SELECT 'DEF', '4', 0
UNION ALL SELECT 'GHI', '1', 100
UNION ALL SELECT 'GHI', '2', 100
UNION ALL SELECT 'GHI', '3', 50
UNION ALL SELECT 'JKL', '1', 0
UNION ALL SELECT 'JKL', '2', 50
UNION ALL SELECT 'JKL', '3', 50
UNION ALL SELECT 'JKL', '4', 50
UNION ALL SELECT 'AAA', '01', 45
UNION ALL SELECT 'AAA', '02', 20
UNION ALL SELECT 'AAA', '03', 20
UNION ALL SELECT 'AAA', '04', 1
UNION ALL SELECT 'AAA', '05', 1
UNION ALL SELECT 'AAA', '06', 1
UNION ALL SELECT 'AAA', '07', 1
UNION ALL SELECT 'AAA', '08', 1
UNION ALL SELECT 'AAA', '09', 1
UNION ALL SELECT 'AAA', '10', 1
UNION ALL SELECT 'AAA', '11', 1
;WITH
-- Assign a row number to each key1 group based on descending weight
d AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY key1 ORDER BY key1, Weight DESC) AS SeqNo FROM @Dtl),
-- If weight is denormalized (no total on header) find the total
w1 AS (SELECT key1, SUM(Weight) AS Weight FROM d GROUP BY key1),
w2 AS (SELECT key1, SUM(Weight) AS Weight FROM d WHERE SeqNo BETWEEN 1 and 3 GROUP BY key1),
-- Calculate the rounded, allocated amount
a AS (SELECT d.key1, d.key2, d.Weight, d.SeqNo
,ROUND(CAST(Amount * d.Weight / w.Weight AS Money), 0) AllocAmt
FROM d INNER JOIN @hdr h ON h.key1 = d.key1 INNER JOIN w1 w ON h.key1 = w.key1),
-- Calculate the total of all rows not "fudge" rounded
b AS (SELECT key1, SUM(AllocAmt) As AllocAmt FROM a WHERE SeqNo <> 1 GROUP BY key1),
-- Retrieve all but the "fudge" rounded rows
c AS (SELECT a.key1, a.key2, a.Weight, a.AllocAmt FROM a WHERE SeqNo > 3),
c2 AS (SELECT d.key1, d.key2, d.Weight, d.SeqNo
,ROUND(CAST((Amount - ISNULL((SELECT SUM(AllocAmt) FROM c WHERE h.key1 = c.key1), 0)) * d.Weight / w.Weight AS Money), 0) As AllocAmt
FROM d INNER JOIN @hdr h ON h.key1 = d.key1 INNER JOIN w2 w ON h.key1 = w.key1 WHERE SeqNo BETWEEN 2 and 3)
-- Retrieve all but the "fudge" rounded rows
SELECT key1, key2, Weight, AllocAmt FROM c
UNION ALL
SELECT key1, key2, Weight, AllocAmt FROM c2
UNION ALL
-- Retrieve the "fudge" rounded rows
SELECT a.key1, a.key2, a.Weight, h.Amount -
(ISNULL((SELECT SUM(AllocAmt) FROM c WHERE h.key1 = c.key1), 0) + ISNULL((SELECT SUM(AllocAmt) FROM c2 WHERE h.key1 = c2.key1), 0))
FROM a INNER JOIN @Hdr h ON h.key1 = a.key1
WHERE SeqNo = 1
ORDER BY key1, key2
Nasty piece of work that, but here's the result set with a couple of cases added for illustration:
key1key2WeightAllocAmt
AAA0145.0037.00
AAA0220.0016.00
AAA0320.0016.00
AAA041.001.00
AAA051.001.00
AAA061.001.00
AAA071.001.00
AAA081.001.00
AAA091.001.00
AAA101.001.00
AAA111.001.00
AAB199.0050.00
ABC133.0034.00
ABC233.0033.00
ABC333.0033.00
DEF225.00212.00
DEF334.00289.00
DEF40.000.00
GHI1100.00101.00
GHI2100.00100.00
GHI350.0050.00
JKL10.000.00
JKL250.0051.00
JKL350.0050.00
JKL450.0050.00
Edited: I thought it would add additional illustration if I showed the new setup data run through the original FR SQL (fudging only the row with highest weight). Note the difference in the AA result set.
key1key2WeightAllocAmt
AAA0145.0035.00
AAA0220.0017.00
AAA0320.0017.00
AAA041.001.00
AAA051.001.00
AAA061.001.00
AAA071.001.00
AAA081.001.00
AAA091.001.00
AAA101.001.00
AAA111.001.00
AAB199.0050.00
ABC133.0034.00
ABC233.0033.00
ABC333.0033.00
DEF225.00212.00
DEF334.00289.00
DEF40.000.00
GHI1100.00101.00
GHI2100.00100.00
GHI350.0050.00
JKL10.000.00
JKL250.0051.00
JKL350.0050.00
JKL450.0050.00
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 21, 2012 at 5:12 pm
" a million years ago attending Cobol training"
I am from the same era, but with a Fortran background. That might explain our different attitudes towards "fudging the numbers".
Pretty much the first thing that we were taught is that rounding is a fact of life, so understand it, and its consequences, and get used to it. The next thing was to be very suspicious of numbers that do not seem to have been affected by rounding issues (e.g. because everything adds up too nicely) because it is a sure sign of fraudulent data.
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply