470,600 Members | 1,489 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,600 developers. It's quick & easy.

UDF Performance Question

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

Jan 24 '06 #1
3 1983
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
Jan 24 '06 #2
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

Jan 24 '06 #3
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
Jan 24 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

115 posts views Thread by Mark Shelor | last post: by
4 posts views Thread by Martin | last post: by
13 posts views Thread by bjarne | last post: by
6 posts views Thread by Mike | last post: by
18 posts views Thread by Rune B | last post: by
5 posts views Thread by Varangian | last post: by
30 posts views Thread by galiorenye | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.