468,770 Members | 2,129 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Performance improvement for Stored Proc resolution

Hello IBM

I think that one could improve the performance of DB2 UDB v7.2's
stored procedure resolution. Here's what DB2 normally does:

SELECT A.PROCSCHEMA, A.PROCNAME, A.PARMNAME, A.TYPESCHEMA, A.TYPENAME,
A.LENGTH,
A.SCALE, A.PARM_MODE, A.ORDINAL, A.CODEPAGE FROM "SYSIBM".SYSPROCPARMS
A,
"SYSIBM".SYSPROCEDURES B WHERE A.PROCSCHEMA = 'UMBNT'AND A.PROCNAME =
'GETBYNAME_NUMBERCYCLE' AND A.SPECIFICNAME = B.SPECIFICNAME AND
A.PROCSCHEMA =
B.PROCSCHEMA AND B.PARM_COUNT = 2 ORDER BY 1, 2, 9

If you explain this statement, however, you will see that it results
in a scan of the table SYSPROCPARMS. Simply rephrasing the query as
follows will eliminate the scan (note how the name now restricts
SYSPROCEDURES so the index is used):

SELECT
B.PROCSCHEMA
, B.PROCNAME
, A.PARMNAME
, A.TYPESCHEMA
, A.TYPENAME
, A.LENGTH
, A.SCALE
, A.PARM_MODE
, A.ORDINAL
, A.CODEPAGE
FROM "SYSIBM".SYSPROCPARMS A
, "SYSIBM".SYSPROCEDURES B
WHERE B.PROCSCHEMA = 'UMBNT'
AND B.PROCNAME = 'GETBYNAME_NUMBERCYCLE'
AND A.SPECIFICNAME = B.SPECIFICNAME
AND A.PROCSCHEMA = B.PROCSCHEMA
AND B.PARM_COUNT = 2
ORDER BY 1, 2, 9

While on most systems, the actual performance improvement will be
negligible, it does matter on systems with lots of procedures.

It also does not artificially degrade the ratio of rows read vs rows
selected, which improves this value as a health indicator.

Regards,
Peter Arrenbrecht
Opus Software AG
Nov 12 '05 #1
1 1712
In v8 CALL is a compiled statement, and we no longer require a table
scan per call.

Peter Arrenbrecht Opus wrote:
Hello IBM

I think that one could improve the performance of DB2 UDB v7.2's
stored procedure resolution. Here's what DB2 normally does:

SELECT A.PROCSCHEMA, A.PROCNAME, A.PARMNAME, A.TYPESCHEMA, A.TYPENAME,
A.LENGTH,
A.SCALE, A.PARM_MODE, A.ORDINAL, A.CODEPAGE FROM "SYSIBM".SYSPROCPARMS
A,
"SYSIBM".SYSPROCEDURES B WHERE A.PROCSCHEMA = 'UMBNT'AND A.PROCNAME =
'GETBYNAME_NUMBERCYCLE' AND A.SPECIFICNAME = B.SPECIFICNAME AND
A.PROCSCHEMA =
B.PROCSCHEMA AND B.PARM_COUNT = 2 ORDER BY 1, 2, 9

If you explain this statement, however, you will see that it results
in a scan of the table SYSPROCPARMS. Simply rephrasing the query as
follows will eliminate the scan (note how the name now restricts
SYSPROCEDURES so the index is used):

SELECT
B.PROCSCHEMA
, B.PROCNAME
, A.PARMNAME
, A.TYPESCHEMA
, A.TYPENAME
, A.LENGTH
, A.SCALE
, A.PARM_MODE
, A.ORDINAL
, A.CODEPAGE
FROM "SYSIBM".SYSPROCPARMS A
, "SYSIBM".SYSPROCEDURES B
WHERE B.PROCSCHEMA = 'UMBNT'
AND B.PROCNAME = 'GETBYNAME_NUMBERCYCLE'
AND A.SPECIFICNAME = B.SPECIFICNAME
AND A.PROCSCHEMA = B.PROCSCHEMA
AND B.PARM_COUNT = 2
ORDER BY 1, 2, 9

While on most systems, the actual performance improvement will be
negligible, it does matter on systems with lots of procedures.

It also does not artificially degrade the ratio of rows read vs rows
selected, which improves this value as a health indicator.

Regards,
Peter Arrenbrecht
Opus Software AG

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Bruce Hendry | last post: by
6 posts views Thread by AC Slater | last post: by
3 posts views Thread by Peter Arrenbrecht | last post: by
10 posts views Thread by db2udbgirl | last post: by
reply views Thread by mirandacascade | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.