471,354 Members | 1,035 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,354 software developers and data experts.

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 1790
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 XIAOLAOHU | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.