December 1, 2010 at 5:40 pm
Hi All,
I have to update table A based on some criteria for every record of table B. I am attaching the sample codes that i have developed using the two approaches (set based and While Loop).
The approach using WHILE Loop is much simpler and easy to understad even for a novice. However SET based approach especially in this case as it is groupby multiplication factor is complex.
EveryOne suggests shifting to SET Based solutions. However what is the best approach in this case?
The dilutionfactor table in my example probably has a max of 5 records as per the business. If anyone has an alternate better solution that you can point it will be great.
WHILE LOOP Solution
DECLARE @X Table
(
DateID INT,
Value Float,
UpdatedValue Float
)
INSERT INTO @x
(
DateID,
Value
)
SELECT
20100101,
1
union all
SELECT
20100102,
1.2
union all
SELECT
20100103,
10
union all
SELECT
20100104,
5
union all
SELECT
20100105,
5.1
--SELECT * FROM @x
UPDATE @X
SET
UpdatedValue = Value
FROM
@x
DECLARE @DilutionFactor TABLE
(
ID INT IDENTITY(1,1) NOT NULL,
DateID INT,
DilutionFactor Float
)
INSERT INTO @DilutionFactor
(
DateID,
DilutionFactor
)
SELECT
20100103,
10
UNION ALL
SELECT
20100104,
0.5
DECLARE @RecordCount INT
SELECT @RecordCount = COUNT(*) FROM @DilutionFactor
DECLARE @int int
set @int = 1
SELECT 'Before UPdate'
SELECT * FROM @X
SELECT * FROM @DilutionFactor
WHILE @int <= @RecordCount
BEGIN
DECLARE @DateID INT
SELECT @DateID = DateID FROM @DilutionFactor
WHERE ID = @int
DECLARE @DFactor FLOAT
SELECT
@DFactor = D.DilutionFactor
FROM
@DilutionFactor D
WHERE
ID = @int
UPDATE @x
SET
x.UpdatedValue = UpdatedValue * @DFactor
FROM
@x x
WHERE
X.DateID < @DateID
SET @int = @int + 1
END
SELECT 'After Update'
SELECT * FROM @x
[/Code]
SETBased Approach
[Code]
DECLARE @X Table
(
DateID INT,
Value Float,
UpdatedValue Float
)
INSERT INTO @x
(
DateID,
Value
)
SELECT
20100101,
1
union all
SELECT
20100102,
1.2
union all
SELECT
20100103,
10
union all
SELECT
20100104,
5
union all
SELECT
20100105,
5.1
--SELECT * FROM @x
UPDATE @X
SET
UpdatedValue = Value
FROM
@x
DECLARE @DilutionFactor TABLE
(
ID INT IDENTITY(1,1) NOT NULL,
DateID INT,
DilutionFactor Float
)
INSERT INTO @DilutionFactor
(
DateID,
DilutionFactor
)
SELECT
20100103,
10
UNION ALL
SELECT
20100104,
0.5
SELECT * FROM @x
SELECT * FROM @DilutionFactor
SELECT
DateID,
Value * EXP(SUM(LOG(DilutionFactor))) AS UpdatedValue
FROM
(
SELECT
DateID,
Value,
DilutionFactor
FROM
(
SELECT x.*,
CASE
WHEN x.DateID < D.DateID THEN D.DateID
END AS DilutionDate,
D.DilutionFactor
FROM
@X x
LEFT JOIN
@DilutionFactor D
ON1=1
WHERE
X.DateID <= (SELECT MAX(DateID) FROM @DilutionFactor)
)UpdatedValue
WHERE DilutionDate IS NOT NULL
)X
GROUP BY
DateID,
Value
[/Code]
December 2, 2010 at 2:43 am
Not going into whats better or worse. But your SET based version feels... overkill.
update x set UpdatedValue = UpdatedValue * IsNull(Factor, 1)
from @x x
cross apply (select EXP(SUM(LOG(DilutionFactor))) Factor
from @DilutionFactor d
where d.DateID > x.DateID) y
This gives me the same result as yours. Well to be honest there is a difference in the last decimal (like 10th position) but im guessing thats because of Float. Changing the type to Decimal gives me the exact same result in all the different versions.
Also this works
update x
set UpdatedValue = UpdatedValue * IsNull((select EXP(SUM(LOG(DilutionFactor))) Factor
from @DilutionFactor d
where d.DateID > x.DateID), 1)
from @x x
Also have a look at http://www.sqlservercentral.com/articles/T-SQL/61539/ since the solutions above could be nasty if there are lots of rows in both tables.
/T
December 2, 2010 at 3:59 pm
TommyH,
Thanks for your response and solution. I will test your solution with more data on the @x table and see if Cross Apply effects the performance.
Cheers
Aparna
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply