473,233 Members | 1,376 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,233 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 1876
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Bruce Hendry | last post by:
In a nutshell, I have a stored proc that takes appx 15 minutes when it uses a read-only database, and about 2 minutes when the database is not read-only. Details: SQL Server 7 The stored...
8
by: Jack | last post by:
I have a test database that I have built in a 3 partition (and 3 node) environment. I have defined all the tables so they have the same partition key. The tables (7 of them) form a hierarchical...
6
by: AC Slater | last post by:
Hi All, Out of nowhere my udb system (v8) performance has went terrible. Its gotten about 10x worse, (some tests that used to take 2 seconds to run now take 20)... I'm not sure what happened. ...
3
by: Peter Arrenbrecht | last post by:
Hi all I while ago I posted a suggestion to the DB2 newsgroup: ...
9
by: wdwedw | last post by:
I have included all the source codes in the attached MyTest.zip (http://www.codeguru.com/forum/attachment.php?attachmentid=11218) There are three projects: VBTestCOM project is a apartment...
10
by: db2udbgirl | last post by:
I have coded a stored procedure which is planned to run only once in production to process historic data from DB2 8.2 database. It accesses data from a partitioned table and based on some...
6
by: teddysnips | last post by:
In a system I'm maintaining there is a Stored Procedure called dbo.MyStoredProcedure. I didn't create this - it was created by a developer who has now left. I don't know how the object came by...
0
by: ravindrag | last post by:
Hi, I am getting error SQL1131N during sqlj.install_jar(...). There is no useful message in the diag.log (even with diag level 4). I am giving the diag.log entries at the end of this posting...
0
by: mirandacascade | last post by:
Questions toward the bottom of the post. Situation is this: 1) Access 97 2) SQL Server 2000 3) The Access app: a) sets up pass-thru query b) .SQL property of querydef is a string, the...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

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.