| Author |
 Topic  |
|
|
Marko299
Starting Member
6 Posts |
 Posted - 07/13/2012 : 20:56:31
|
I need to do complex mathematical calculation per each row of table and set result in it.
I am doing it using fast forward cursor, but it is still slow;
while @@FETCH_STATUS = 0
begin
;get all data from a row
;result = Fcomplex(data from row)
;update that row with result
fetch next...
end;
Can it be done using per Set operation, instead Cursor.
Help please |
Edited by - Marko299 on 07/13/2012 20:56:58
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 07/13/2012 : 21:40:46
|
you have to do it inside the Fcomplex function. Can you show us what does FComplex() do ?
KH
Time is always against us
|
 |
|
|
Marko299
Starting Member
6 Posts |
Posted - 07/13/2012 : 22:39:48
|
It does Euclidean distance
I think i figured it out
UPDATE #CalculationTable
SET distance = SQRT(SQUARE(col1-@col1) + SQUARE(col2-@col2) + ... + SQUARE(coln-@coln)
+CASE WHEN @colDescrete1 != colDescrete1 THEN 1.0 ELSE 0.0 END
+CASE WHEN @colDescrete2 != colDescrete2 THEN 1.0 ELSE 0.0 END
+...+
+CASE WHEN @colDescreten != colDescreten THEN 1.0 ELSE 0.0 END
);
It calculates distances of each row from vector (@col1, @col2,..., @coln, @colDescrete1,...,@colDescreten)
It is at least 10x faster then when using Cursor or WHILE loop.
When it does calculation only one core is used. Can it be done on all cores. I have SQL Server 2008 R2 Developer.
It looks fast for continuous part(col1,...,coln) but CASE-WHEN-THEN-END slows it down dramatically. It looks like there are going to be several days of calculation.
|
Edited by - Marko299 on 07/15/2012 08:12:08 |
|
|
|
Marko299
Starting Member
6 Posts |
Posted - 07/15/2012 : 08:05:39
|
Just one question. :)
UPDATE #CalculationTable
SET distance = square(@c2-c2)
+ CASE WHEN @cd3 != cd3 THEN 1.0 ELSE 0.0 END
+ CASE WHEN @cd4 != cd4 THEN 1.0 ELSE 0.0 END
+ CASE WHEN @cd5 != cd5 THEN 1.0 ELSE 0.0 END
+ square(@c6-c6)
+ square(@c7-c7)
+ square(@c8-c8)
+ square(@c9-c9)
+ square(@c10-c10)
+ square(@c11-c11)+square(@c12-c12)+square(@c13-c13)+square(@c14-c14)+square(@c15-c15)+square(@c16-c16)+square(@c17-c17)+square(@c18-c18)+square(@c19-c19)+square(@c20-c20)
+ square(@c21-c21)+square(@c22-c22)+square(@c23-c23)+square(@c24-c24)+square(@c25-c25)+square(@c26-c26)+square(@c27-c27)+square(@c28-c28)+square(@c29-c29)+square(@c30-c30)
+ square(@c31-c31)
This CASE-WHEN-THEN-ELSE-END part slows it down a lot. Can it be done with some boolean->double precision conversion?
UPDATE #CalculationTable
SET distance = square(@c2-c2)
+ (double precision) (@cd3 != cd3)
+ (double precision) (@cd3 != cd3)
+ (double precision) (@cd3 != cd3)
+ square(@c6-c6)
+ square(@c7-c7)
+ square(@c8-c8)
+ square(@c9-c9)
+ square(@c10-c10)
+ square(@c11-c11)+square(@c12-c12)+square(@c13-c13)+square(@c14-c14)+square(@c15-c15)+square(@c16-c16)+square(@c17-c17)+square(@c18-c18)+square(@c19-c19)+square(@c20-c20)
+ square(@c21-c21)+square(@c22-c22)+square(@c23-c23)+square(@c24-c24)+square(@c25-c25)+square(@c26-c26)+square(@c27-c27)+square(@c28-c28)+square(@c29-c29)+square(@c30-c30)
+ square(@c31-c31)
|
Edited by - Marko299 on 07/15/2012 08:07:03 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 07/15/2012 : 09:14:16
|
Make the calculation as an inline table valued function and you will be able to get parallellism.
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 07/15/2012 : 09:15:54
|
@cd3 != cd3
ABS(SIGN(@cd3 - cd3))
Don't know if it's faster, though...
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 07/15/2012 : 09:19:54
|
Or this?UPDATE tgt
SET tgt.Distance = SQRT(f.Distance)
FROM dbo.Table1 AS tgt
CROSS APPLY (
SELECT SUM(SQUARE(delta) + ABS(SIGN(Delta)))
FROM (
VALUES (a2 - @a2),
(a3 - @a3),
...
(an - @an)
) AS d(Delta)
) AS f(Distance)
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
Marko299
Starting Member
6 Posts |
Posted - 07/15/2012 : 09:59:32
|
Thanks for reply, didnt know about CROSS APPLY so i needed time to study.
1.@cd3 != cd3
ABS(SIGN(@cd3 - cd3))
@cd3 and cd3 are varchar(12) discrete values, sorry for not saying that.
I will try this CROSS APPLY. |
|
|
| |
Topic |
|
Per Set instead of Cursor
