October 25, 2016 at 9:18 pm
Please see my sample code below. I am trying to write a query to update the Value#number#_Check column. I have 4 update statements to update Value1_Check, Value2_Check, Value3_Check, and Value4_Check.
If I were to have many records in the #DataTableName table, the performance is not so great. Is there a way to write a single query to update all values? or any other ways to make it perform better?
Thanks!
--1 create tables
IF OBJECT_ID('tempdb..#DataTableName') IS NOT NULL
DROP TABLE #DataTableName
IF OBJECT_ID('tempdb..#Join') IS NOT NULL
DROP TABLE #Join
CREATE TABLE #DataTableName (
ID INT IDENTITY(1,1),
Value1 FLOAT,
Value1_Pct FLOAT,
Value1_Check VARCHAR(16),
Value2 FLOAT,
Value2_Pct FLOAT,
Value2_Check VARCHAR(16),
Value3 FLOAT,
Value3_Pct FLOAT,
Value3_Check VARCHAR(16),
Value4 FLOAT,
Value4_Pct FLOAT,
Value4_Check VARCHAR(16)
)
CREATE TABLE #Join (
ID INT IDENTITY(1,1),
CheckName VARCHAR(16),
Low FLOAT,
High FLOAT
)
--2 populate data
INSERT #DataTableName (Value1, Value1_Pct, Value2, Value2_Pct, Value3, Value3_Pct, Value4, Value4_Pct)
SELECT 100, -0.15, 200, 0.11, 300, -0.05, 400, 0.15 UNION ALL
SELECT 100, 0.11, 200, 0.09, 300, 0.15, 400, -0.05 UNION ALL
SELECT 100, 0.02, 200, 0.01, 300, -0.05, 400, 0.15 UNION ALL
SELECT 100, -0.05, 200, NULL, 300, 0.15, 400, -0.05
INSERT #Join (CheckName, Low, High)
SELECT 'A', 0.1, NULL UNION ALL
SELECT 'B', 0, 0.1 UNION ALL
SELECT 'C', -0.1, 0 UNION ALL
SELECT 'D', NULL, -0.1
--3 data sample
SELECT * FROM #DataTableName
SELECT * FROM #Join
--4 update Value?_Check values
--better performance choice??
UPDATE D
SET Value1_Check = J.CheckName
FROM #DataTableName D
JOIN #Join J
ON (Value1_Pct >= J.Low OR J.Low IS NULL)
AND (Value1_Pct <= J.High OR J.High IS NULL)
UPDATE D
SET Value2_Check = J.CheckName
FROM #DataTableName D
JOIN #Join J
ON (Value2_Pct >= J.Low OR J.Low IS NULL)
AND (Value2_Pct <= J.High OR J.High IS NULL)
UPDATE D
SET Value3_Check = J.CheckName
FROM #DataTableName D
JOIN #Join J
ON (Value3_Pct >= J.Low OR J.Low IS NULL)
AND (Value3_Pct <= J.High OR J.High IS NULL)
UPDATE D
SET Value4_Check = J.CheckName
FROM #DataTableName D
JOIN #Join J
ON (Value4_Pct >= J.Low OR J.Low IS NULL)
AND (Value4_Pct <= J.High OR J.High IS NULL)
SELECT * FROM #DataTableName
October 25, 2016 at 9:31 pm
I tried below and it seems much faster.
UPDATE R
SET Value1_Check = P.CheckName
,Value2_Check = P2.CheckName
,Value3_Check = P3.CheckName
,Value4_Check = P4.CheckName
FROM #DataTableName R
LEFT JOIN #Join P ON (ROUND(R.Value1_Pct,4) >= P.Low OR P.Low IS NULL)
AND (ROUND(R.Value1_Pct,4) <= P.High OR P.High IS NULL)
LEFT JOIN #Join P2 ON (ROUND(R.Value2_Pct,4) >= P2.Low OR P2.Low IS NULL)
AND (ROUND(R.Value2_Pct,4) <= P2.High OR P2.High IS NULL)
LEFT JOIN #Join P3 ON (ROUND(R.Value3_Pct,4) >= P3.Low OR P3.Low IS NULL)
AND (ROUND(R.Value3_Pct,4) <= P3.High OR P3.High IS NULL)
LEFT JOIN #Join P4 ON (ROUND(R.Value4_Pct,4) >= P4.Low OR P4.Low IS NULL)
AND (ROUND(R.Value4_Pct,4) <= P4.High OR P4.High IS NULL)
October 26, 2016 at 12:35 am
ocean3300 (10/25/2016)
Is there a way to write a single query to update all values? or any other ways to make it perform better?
1. Normalise your data.
Here I do normalisation on-fly, but you better implement it in the table design:
SELECT ID, 1, Value1, Value1_Pct
FROM #DataTableName
UNION
SELECT ID, 2, Value2, Value2_Pct
FROM #DataTableName
UNION
SELECT ID, 3, Value3, Value3_Pct
FROM #DataTableName
UNION
SELECT ID, 4, Value4, Value4_Pct
FROM #DataTableName
2. Get rid of UPDATE altogether.
Storing CheckNAme in the base table won't add much, as indexing would be ineffective, and I suspect you have those values updated quite often, so you need to refresh those checks quite regularly.
Make it into a view, or use as a derived table (aka CTE) in your queries:
SELECT T.ID, T.ValueID, T.Value, T.Value_PCT, J.CheckName
FROM (
SELECT ID, 1, Value1, Value1_Pct
FROM #DataTableName
UNION
SELECT ID, 2, Value2, Value2_Pct
FROM #DataTableName
UNION
SELECT ID, 3, Value3, Value3_Pct
FROM #DataTableName
UNION
SELECT ID, 4, Value4, Value4_Pct
FROM #DataTableName
) T (ID, ValueID, Value, Value_PCT)
LEFT JOIN #Join J ON (Value_Pct >= J.Low OR J.Low IS NULL)
AND (Value_Pct <= J.High OR J.High IS NULL)
As you can see, data normalisation reduces the amount of necessary code by 4 times.
Comes quite useful at times. 🙂
_____________
Code for TallyGenerator
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply