UDF的次优性能 [英] Sub-optimal performance of a UDF

问题描述

嗨


我最近在SQl中编写了一个用户定义的函数，在db2中运行它和

工作正常。但我似乎得到一个警告说，性能是次优的。$ / b

这是警告信息：


SQL0437W此复杂查询的性能可能不是最佳的。原因

代码：

" 2"。 SQLSTATE = 01602


我已执行运行统计操作但仍然收到

警告。


有没有我需要查看的一系列内容获得最佳好处

表现？


谢谢

Mahesh

Hi

I recently wrote a user defined function in SQl to run in db2 and it
works fine. But I seem to get a warning saying that the performance is
sub-optimal.

Here is the warning message:

SQL0437W Performance of this complex query may be sub-optimal. Reason
code:
"2". SQLSTATE=01602

I have performed run statistics operation but am still getting the
warning.

Is there a list of things that I need to look into gain best
performance?

Thanks
Mahesh

推荐答案

我正在制作UDF代码，因为它可以帮助你们指出

我做错了什么。这是UDF代码：


CREATE FUNCTION RISK_EQUATION_1（@patient_id VARCHAR（36））

返回双倍

--- -------------------------------------------------- -------------------

- SQL UDF（标量）

-------- -------------------------------------------------- --------------

F1：BEGIN ATOMIC

DECLARE @x varchar（36）;

DECLARE @z char;

DECLARE @hdl，@ family_history，@ smoke，@ waist double;

DECLARE x1，x2，x3，x4，x5，x6，GmaxScore，回答双倍;

DECLARE start_time，end_time，diff_time时间戳;


FOR myrow1 AS选择PATIENT_VISIT_ID来自

HEALTHCAREDB.PATIENT_VISIT_INFO_VIEW其中PATIENT_ID = @patient_id DO

SET @x = myrow1.PATIENT_VISIT_ID;

END FOR;


FOR myrow2 AS从HEALTHCAREDB中选择RC_7_13_1_DFH .FAMILY_HISTORY

其中PATIENT_ID = @patient_id DO

SET @z = myrow2.RC_7_13_1_DFH;

结束;


FOR myrow3 AS从HEALTHCAREDB.PATIENT_VISIT_INFO_VIEW中选择RCO_4_01_0_2_W，RLD_5_06_3_HDLCL，

RL_14_02_1_SS

PATIENT_VISIT_ID = @x DO

SET @waist = myrow3.RCO_4_01_0_2_W;

SET @hdl = myrow3.RLD_5_06_3_HDLCL;

SET @smoke = myrow3.RL_14_02_1_SS ;

结束;


如果@z =''Y''

那么设置@family_history = 1;

ELSE SET @family_history = 0;

END IF;


set x1 = @family_history;

设置x2 = @family_history;

设置x3 = @smoke;

设置x4 = 0;

设置x5 = 0;


设置x2 = x3 - x2;

设置x1 = HEALTHCAREDB.MAXIMUM（x1，x2）;

设置x2 = @hdl;

设置x1 = HEALTHCAREDB.MAXIMUM（x1，x2）;


设置x2 = 1.735981;

设置x3 = @family_history;

设置x2 = x3 - x2;


如果@waist = 104.0那么

设置x3 = 1;

其他se t x3 = 0;

结束如果;


设置x4 = @smoke;

如果@waist = 103则

设置x5 = 1;

否则设置x5 = 0;

结束if;


设置x6 = 1.15732;

如果x6> = x5那么

设置x5 = 1;

否则设置x5 = 0;

结束if;


设置x4 = HEALTHCAREDB.MAXIMUM（x4，x5）;

设置x3 = x4 - x3;

设置x2 = x3 - x2;


if（x1！= 0）然后

设置x1 = x2 / x1;

否则设置x1 = 1;

结束if;


if（@waist = 103.0）then

set x2 = 1;

否则设置x2 = 0;

结束if;


设置x1 = x2 - x1;

设置x2 = @hdl;


设置x1 = x2 - x1;


if（@waist = 104）then

设置x2 = 1;

否则设置x2 = 0;

结束if;


设置x3 = 1.374318;


if（@waist = 104）然后

设置x4 = 1;

else set x4 = 0;

结束if;

设置x3 = x3 + x4;

设置x2 = x2 + x3;

设置x1 = HEALTHCAREDB.MAXIMUM（x1，x2）;


设定GmaxScore = x1;


设定x1 = -0.4324524 * GmaxScore + 3.230582;


SET答案=（1 /（1 + x1））;


返回（答案）;


结束

I am producing the UDF code as it may help you guys in pointing out
what I am doing wrong. Here is the UDF code:

CREATE FUNCTION RISK_EQUATION_1( @patient_id VARCHAR(36) )
RETURNS double
------------------------------------------------------------------------
-- SQL UDF (Scalar)
------------------------------------------------------------------------
F1: BEGIN ATOMIC
DECLARE @x varchar(36);
DECLARE @z char;
DECLARE @hdl, @family_history, @smoke, @waist double;
DECLARE x1, x2, x3, x4, x5, x6, GmaxScore, answer double;
DECLARE start_time, end_time, diff_time timestamp ;

FOR myrow1 AS select PATIENT_VISIT_ID from
HEALTHCAREDB.PATIENT_VISIT_INFO_VIEW where PATIENT_ID = @patient_id DO
SET @x = myrow1.PATIENT_VISIT_ID;
END FOR;

FOR myrow2 AS select RC_7_13_1_DFH from HEALTHCAREDB.FAMILY_HISTORY
where PATIENT_ID = @patient_id DO
SET @z = myrow2.RC_7_13_1_DFH;
END FOR;

FOR myrow3 AS select RCO_4_01_0_2_W, RLD_5_06_3_HDLCL,
RL_14_02_1_SS from HEALTHCAREDB.PATIENT_VISIT_INFO_VIEW where
PATIENT_VISIT_ID = @x DO
SET @waist = myrow3.RCO_4_01_0_2_W;
SET @hdl = myrow3.RLD_5_06_3_HDLCL;
SET @smoke = myrow3.RL_14_02_1_SS;
END FOR;

IF @z = ''Y''
THEN SET @family_history = 1;
ELSE SET @family_history = 0;
END IF;

set x1 = @family_history;
set x2 = @family_history;
set x3 = @smoke;
set x4 = 0;
set x5 = 0;

set x2 = x3 - x2;
set x1 = HEALTHCAREDB.MAXIMUM(x1, x2);
set x2 = @hdl;
set x1 = HEALTHCAREDB.MAXIMUM(x1, x2);

set x2 = 1.735981;
set x3 = @family_history;
set x2 = x3 - x2;

if @waist = 104.0 then
set x3 = 1;
else set x3 = 0;
end if;

set x4 = @smoke;
if @waist = 103 then
set x5 = 1;
else set x5 = 0;
end if;

set x6 = 1.15732;
if x6 >= x5 then
set x5 = 1;
else set x5 = 0;
end if;

set x4 = HEALTHCAREDB.MAXIMUM(x4, x5);
set x3 = x4 - x3;
set x2 = x3 - x2;

if (x1 != 0) then
set x1 = x2 / x1;
else set x1 = 1;
end if;

if (@waist = 103.0) then
set x2 = 1;
else set x2 = 0;
end if;

set x1 = x2 - x1;
set x2 = @hdl;

set x1 = x2 - x1;

if (@waist = 104) then
set x2 = 1;
else set x2 = 0;
end if;

set x3 = 1.374318;

if (@waist = 104) then
set x4 = 1;
else set x4 = 0;
end if;
set x3 = x3 + x4;
set x2 = x2 + x3;
set x1 = HEALTHCAREDB.MAXIMUM(x1, x2);

set GmaxScore = x1;

set x1 = -0.4324524 * GmaxScore + 3.230582;

SET answer = ( 1 / (1 + x1));

return ( answer );

END

Mahesh S写道：

Mahesh S wrote:

我正在制作UDF代码，因为它可以帮助你们指出

我做错了什么。这是UDF代码：


CREATE FUNCTION RISK_EQUATION_1（@patient_id VARCHAR（36））

返回双倍

--- -------------------------------------------------- -------------------

- SQL UDF（标量）

-------- -------------------------------------------------- --------------

F1：BEGIN ATOMIC

DECLARE @x varchar（36）;

DECLARE @z char;

DECLARE @hdl，@ family_history，@ smoke，@ waist double;

DECLARE x1，x2，x3，x4，x5，x6，GmaxScore，回答双倍;

DECLARE start_time，end_time，diff_time时间戳;


FOR myrow1 AS选择PATIENT_VISIT_ID来自

HEALTHCAREDB.PATIENT_VISIT_INFO_VIEW其中PATIENT_ID = @patient_id DO

SET @x = myrow1.PATIENT_VISIT_ID;

END FOR;

I am producing the UDF code as it may help you guys in pointing out
what I am doing wrong. Here is the UDF code:

CREATE FUNCTION RISK_EQUATION_1( @patient_id VARCHAR(36) )
RETURNS double
------------------------------------------------------------------------
-- SQL UDF (Scalar)
------------------------------------------------------------------------
F1: BEGIN ATOMIC
DECLARE @x varchar(36);
DECLARE @z char;
DECLARE @hdl, @family_history, @smoke, @waist double;
DECLARE x1, x2, x3, x4, x5, x6, GmaxScore, answer double;
DECLARE start_time, end_time, diff_time timestamp ;

FOR myrow1 AS select PATIENT_VISIT_ID from
HEALTHCAREDB.PATIENT_VISIT_INFO_VIEW where PATIENT_ID = @patient_id DO
SET @x = myrow1.PATIENT_VISIT_ID;
END FOR;

愚蠢的问题：你为什么在这里使用循环？一个简单的


SET @x =（SELECT ... FROM ... WHERE ... FETCH FIRST 1 ROWS only）


会完成与循环相同的任务。

Stupid question: why do you use a loop here? A simple

SET @x = ( SELECT ... FROM ... WHERE ... FETCH FIRST 1 ROWS ONLY )

would accomplish the same task as your loop.

FOR myrow2 AS从HEALTHCAREDB.FAMILY_HISTORY中选择RC_7_13_1_DFH

其中PATIENT_ID = @patient_id DO

SET @z = myrow2.RC_7_13_1_DFH;

END FOR;

FOR myrow2 AS select RC_7_13_1_DFH from HEALTHCAREDB.FAMILY_HISTORY
where PATIENT_ID = @patient_id DO
SET @z = myrow2.RC_7_13_1_DFH;
END FOR;

见上文。

see above.

FOR myrow3 AS选择RCO_4_01_0_2_W，RLD_5_06_3_HDLCL，

来自HEALTHCAREDB.PATIENT_VISIT_INFO_VIEW的RL_14_02_1_SS

PATIENT_VISIT_ID = @x DO

SET @waist = myrow3.RCO_4_01_0_2_W;

SET @hdl = myrow3。 RLD_5_06_3_HDLCL;

SET @smoke = myrow3.RL_14_02_1_SS;

END FOR;

FOR myrow3 AS select RCO_4_01_0_2_W, RLD_5_06_3_HDLCL,
RL_14_02_1_SS from HEALTHCAREDB.PATIENT_VISIT_INFO_VIEW where
PATIENT_VISIT_ID = @x DO
SET @waist = myrow3.RCO_4_01_0_2_W;
SET @hdl = myrow3.RLD_5_06_3_HDLCL;
SET @smoke = myrow3.RL_14_02_1_SS;
END FOR;

见上文。

see above.

IF @z =''Y''

THEN SET @family_history = 1;

ELSE SET @family_history = 0;

END IF;


set x1 = @family_history;

set x2 = @family_history;

set x3 = @smoke;

set x4 = 0;

设置x5 = 0;


设置x2 = x3 - x2;

设置x1 = HEALTHCAREDB.MAXIMUM（x1，x2）;

set x2 = @hdl;

set x1 = HEALTHCAREDB.MAXIMUM（x1，x2）;

IF @z = ''Y''
THEN SET @family_history = 1;
ELSE SET @family_history = 0;
END IF;

set x1 = @family_history;
set x2 = @family_history;
set x3 = @smoke;
set x4 = 0;
set x5 = 0;

set x2 = x3 - x2;
set x1 = HEALTHCAREDB.MAXIMUM(x1, x2);
set x2 = @hdl;
set x1 = HEALTHCAREDB.MAXIMUM(x1, x2);

函数HEALTHCAREDB.MAXIMUM究竟是如何实现的？

How exactly is the function HEALTHCAREDB.MAXIMUM implemented?

set x2 = 1.735981;

设置x3 = @family_history;

设置x2 = x3 - x2;


如果@waist = 104.0那么

设置x3 = 1;

否则设置x3 = 0;

结束if;

set x2 = 1.735981;
set x3 = @family_history;
set x2 = x3 - x2;

if @waist = 104.0 then
set x3 = 1;
else set x3 = 0;
end if;

您应该考虑使用CASE表达式（此处和其他一些地方）：


SET x3 = CASE

WHEN @waist = 104.0

那么1

ELSE 0

END;

也许你可以使用NULL而不是0（如果有意义的话）。

You should consider using CASE expressions (here and in a few other places):

SET x3 = CASE
WHEN @waist = 104.0
THEN 1
ELSE 0
END;
Maybe you could use NULLs instead of 0 (if it makes sense).

set x4 = @smoke;

if @waist = 103 then

设置x5 = 1;

否则设置x5 = 0;

结束if;


设置x6 = 1.15732;

如果x6> = x5那么

设置x5 = 1;

否则设置x5 = 0;

结束如果;


设置x4 = HEALTHCAREDB.MAXIMUM（x4，x5）;

设置x3 = x4 - x3;

设置x2 = x3 - x2;


如果（x1！= 0）那么

设置x1 = x2 / x1;

else set x1 = 1;

end if;


if（@waist = 103.0）then

set x2 = 1;

else set x2 = 0;

end if;


设置x1 = x2 - x1;

设置x2 = @hdl;


设置x1 = x2 - x1;


if（@waist = 104）then

set x2 = 1;

else set x2 = 0;

结束if;


set x3 = 1.374318;


if（@waist = 104）then

设置x4 = 1;

否则设置x4 = 0;

结束if;


设置x3 = x3 + x4;

设置x2 = x2 + x3;

设置x1 = HEALTHCAREDB.MAXIMUM（x1，x2）;


设置GmaxScore = x1;


设置x1 = -0.4324524 * GmaxScore + 3.230582;


SET答案=（1 /（1 + x1）） ;


返回（回答）;


结束

set x4 = @smoke;
if @waist = 103 then
set x5 = 1;
else set x5 = 0;
end if;

set x6 = 1.15732;
if x6 >= x5 then
set x5 = 1;
else set x5 = 0;
end if;

set x4 = HEALTHCAREDB.MAXIMUM(x4, x5);
set x3 = x4 - x3;
set x2 = x3 - x2;

if (x1 != 0) then
set x1 = x2 / x1;
else set x1 = 1;
end if;

if (@waist = 103.0) then
set x2 = 1;
else set x2 = 0;
end if;

set x1 = x2 - x1;
set x2 = @hdl;

set x1 = x2 - x1;

if (@waist = 104) then
set x2 = 1;
else set x2 = 0;
end if;

set x3 = 1.374318;

if (@waist = 104) then
set x4 = 1;
else set x4 = 0;
end if;
set x3 = x3 + x4;
set x2 = x2 + x3;
set x1 = HEALTHCAREDB.MAXIMUM(x1, x2);

set GmaxScore = x1;

set x1 = -0.4324524 * GmaxScore + 3.230582;

SET answer = ( 1 / (1 + x1));

return ( answer );

END

- < br $>
Knut Stolze

DB2信息集成开发

IBM德国

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Knut Stolze写道：

Knut Stolze wrote:

Mahesh S写道：

Mahesh S wrote:

>我正在制作UDF代码，因为它可以帮助你们指出我做错了什么。这是UDF代码：

CREATE FUNCTION RISK_EQUATION_1（@patient_id VARCHAR（36））
RETURNS double
--------------- -------------------------------------------------- -------
- SQL UDF（Scalar）
---------------------------- --------------------------------------------
F1： BEGIN ATOMIC
DECLARE @x varchar（36）;
DECLARE @z char;
DECLARE @hdl，@ family_history，@ smoke，@ waist double;
DECLARE x1，x2， x3，x4，x5，x6，GmaxScore，回答双倍;
DECLARE start_time，end_time，diff_time timestamp;

FOR myrow1 AS从
HEALTHCAREDB.PATIENT_VISIT_INFO_VIEW中选择PATIENT_VISIT_ID，其中PATIENT_ID = @ patient_id DO
SET @x = myrow1.PATIENT_VISIT_ID;
END FOR;

>I am producing the UDF code as it may help you guys in pointing out
what I am doing wrong. Here is the UDF code:

CREATE FUNCTION RISK_EQUATION_1( @patient_id VARCHAR(36) )
RETURNS double
------------------------------------------------------------------------
-- SQL UDF (Scalar)
------------------------------------------------------------------------
F1: BEGIN ATOMIC
DECLARE @x varchar(36);
DECLARE @z char;
DECLARE @hdl, @family_history, @smoke, @waist double;
DECLARE x1, x2, x3, x4, x5, x6, GmaxScore, answer double;
DECLARE start_time, end_time, diff_time timestamp ;

FOR myrow1 AS select PATIENT_VISIT_ID from
HEALTHCAREDB.PATIENT_VISIT_INFO_VIEW where PATIENT_ID = @patient_id DO
SET @x = myrow1.PATIENT_VISIT_ID;
END FOR;

愚蠢的问题：你为什么在这里使用循环？一个简单的


SET @x =（SELECT ... FROM ... WHERE ... FETCH FIRST 1 ROWS only）


会完成与循环相同的任务。

Stupid question: why do you use a loop here? A simple

SET @x = ( SELECT ... FROM ... WHERE ... FETCH FIRST 1 ROWS ONLY )

would accomplish the same task as your loop.

> FOR myrow2 AS从HEALTHCAREDB.FAMILY_HISTORY中选择RC_7_13_1_DFH
其中PATIENT_ID = @patient_id DO
SET @z = myrow2.RC_7_13_1_DFH;
END FOR;

> FOR myrow2 AS select RC_7_13_1_DFH from HEALTHCAREDB.FAMILY_HISTORY
where PATIENT_ID = @patient_id DO
SET @z = myrow2.RC_7_13_1_DFH;
END FOR;

见上文。

see above.

> FOR myrow3 AS从HEALTHCAREDB.PATIENT_VISIT_INFO_VIEW中选择RCO_4_01_0_2_W，RLD_5_06_3_HDLCL，
RL_14_02_1_SS
PATIENT_VISIT_ID = @x DO
SET @waist = myrow3.RCO_4_01_0_2_W;
SET @hdl = myrow3.RLD_5_06_3_HDLCL ;
SET @smoke = myrow3.RL_14_02_1_SS;
END FOR;

> FOR myrow3 AS select RCO_4_01_0_2_W, RLD_5_06_3_HDLCL,
RL_14_02_1_SS from HEALTHCAREDB.PATIENT_VISIT_INFO_VIEW where
PATIENT_VISIT_ID = @x DO
SET @waist = myrow3.RCO_4_01_0_2_W;
SET @hdl = myrow3.RLD_5_06_3_HDLCL;
SET @smoke = myrow3.RL_14_02_1_SS;
END FOR;

见上文。

see above.

>如果@z =''Y''
那么设置@family_history = 1;
ELSE SET @family_history = 0;
END IF;

> IF @z = ''Y''
THEN SET @family_history = 1;
ELSE SET @family_history = 0;
END IF;

，因为Knut注意到

as Knut notes

>> ;
设置x1 = @family_history;
设置x2 = @family_history;
设置x3 = @smoke;
设置x4 = 0;
设置x5 = 0;

>>
set x1 = @family_history;
set x2 = @family_history;
set x3 = @smoke;
set x4 = 0;
set x5 = 0;

SET x1 = @family_history，

x2 = @family_history，

x3 = @吸烟，

x4 = 0，

x5 = 0;


请注意，DB2表示性能可能不是最理想的。

它没有说它。


一般情况下尽量避免在SQL UDF或TRIGGERS中使用。

如果你的逻辑很复杂使用存储过程并从UDF中调用它。

请记住SQL UDF（和触发器）内联到执行中

计划。

我的经验法则是，任何不适合屏幕的逻辑东西都是属于存储过程。


干杯

Serge

-

Serge Rielau

DB2解决方案开发

IBM多伦多实验室


WAIUG会议
http://www.iiug.org/waiug/present/Fo...For um2006.html

这篇关于UDF的次优性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！