UDF Performance Question

[kenfar]

I've got a UDF that is used on the publishing side of a data warehouse:
when the data is headed out to a mart. It works fine for sets of a few
thousand rows, but really slows for sets of a few million rows: a
query will go from one to thirty minutes due to two calls on each row.
Any suggestions or advise?

-----------------------------------------------------------------------------
-- convert ip from integer format to string format
-- ex: from 3232235621 to 192.168.0.101
-----------------------------------------------------------------------------
create function IP2STRING(IP_BIGINT bigint)
returns varchar(15)
deterministic
no external action
begin atomic
declare node_1 bigint;
declare node_2 bigint;
declare node_3 bigint;
declare node_4 bigint;
SET node_1 = MOD(BIGINT(ip_bigint / 16777216), 256);
SET node_2 = MOD(INTEGER(ip_bigint / 65536), 256);
SET node_3 = MOD(INTEGER(ip_bigint / 256), 256);
SET node_4 = MOD(ip_bigint, 256);
return RTRIM(CHAR(node_1)) || '.' || RTRIM(CHAR(node_2)) || '.' ||
RTRIM(CHAR(node_3)) || '.' || RTRIM(CHAR(node_4));
end

Am I missing any good opportunities to speed this up?
Any faster algorithm or functions to switch to?
Or should I consider a java routine with bitfiddling (yuck)?
Or give up on this approach and move the logic out of the database
(dang)?
Thanks in advance!

Ken

[Serge Rielau]

kenfar wrote:

I've got a UDF that is used on the publishing side of a data warehouse:
when the data is headed out to a mart. It works fine for sets of a few
thousand rows, but really slows for sets of a few million rows: a
query will go from one to thirty minutes due to two calls on each row.
Any suggestions or advise?

-----------------------------------------------------------------------------
-- convert ip from integer format to string format
-- ex: from 3232235621 to 192.168.0.101
-----------------------------------------------------------------------------
create function IP2STRING(IP_BIGINT bigint)
returns varchar(15)
deterministic
no external action
begin atomic
declare node_1 bigint;
declare node_2 bigint;
declare node_3 bigint;
declare node_4 bigint;
SET node_1 = MOD(BIGINT(ip_bigint / 16777216), 256);
SET node_2 = MOD(INTEGER(ip_bigint / 65536), 256);
SET node_3 = MOD(INTEGER(ip_bigint / 256), 256);
SET node_4 = MOD(ip_bigint, 256);
return RTRIM(CHAR(node_1)) || '.' || RTRIM(CHAR(node_2)) || '.' ||
RTRIM(CHAR(node_3)) || '.' || RTRIM(CHAR(node_4));
end

Am I missing any good opportunities to speed this up?
Any faster algorithm or functions to switch to?
Or should I consider a java routine with bitfiddling (yuck)?
Or give up on this approach and move the logic out of the database
(dang)?

Today SQL UDF which include BEGIN ATOMIC are serialized.
In a DPF enviroment (which I presuem you're in) this means that got
yourself a bottlenck. It will also limit the optimization capabilities
of the optimizer

SQL UDF work great when you can reduce them to only RETURN:

create function IP2STRING(IP_BIGINT bigint)
returns varchar(15)
deterministic
no external action
contains sql
return RTRIM(CHAR(MOD(BIGINT(ip_bigint / 16777216), 256)))
|| '.' || RTRIM(CHAR(MOD(INTEGER(ip_bigint / 65536), 256)))
|| '.' || RTRIM(CHAR(MOD(INTEGER(ip_bigint / 256), 256)))
|| '.' || RTRIM(CHAR(MOD(ip_bigint, 256)))

Enjoy
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[kenfar]

Serge,

SQL UDF work great when you can reduce them to only RETURN:
Thanks! That worked perfect.

Today SQL UDF which include BEGIN ATOMIC are serialized.
In a DPF enviroment (which I presuem you're in) this means that got
yourself a bottlenck. It will also limit the optimization capabilities
of the optimizer

Any suggestions for reference material on this subject? Is Paul Yip's
book the best resource?
Ken Farmer

[Serge Rielau]

kenfar wrote:

Serge,

SQL UDF work great when you can reduce them to only RETURN:

Thanks! That worked perfect.

Today SQL UDF which include BEGIN ATOMIC are serialized.
In a DPF enviroment (which I presuem you're in) this means that got
yourself a bottlenck. It will also limit the optimization capabilities
of the optimizer

Any suggestions for reference material on this subject? Is Paul Yip's
book the best resource?

Not sure how much he has on this topic. Assuming your email address
works I just sent you and (aged) DBM Tech presentation.
If you want to get really into things you can read up the patents:
http://patft.uspto.gov/netahtml/search-bool.html

5,987,455 Intelligent compilation of procedural functions for query
processing systems
5,963,934 Intelligent compilation of scripting language for query
processing systems

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab