Per Set instead of Cursor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Per Set instead of Cursor

Author

Topic

Marko299
Starting Member

6 Posts

Posted - 2012-07-13 : 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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-13 : 21:40:46

you have to do it inside the Fcomplex function. Can you show us what does FComplex() do ?

KH
[spoiler]Time is always against us[/spoiler]

Marko299
Starting Member

6 Posts

Posted - 2012-07-13 : 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.

Marko299
Starting Member

6 Posts

Posted - 2012-07-15 : 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)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-15 : 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

30421 Posts

Posted - 2012-07-15 : 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

30421 Posts

Posted - 2012-07-15 : 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 - 2012-07-15 : 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources