From: Charlie on 29 Jan 2010 13:37 Hi All. I just trying to understand why a stored procedure call takes more time to response than an ODBC request. My company use JDEdwards with DB2 UDB database server. JDEdwards use ODBC technology to retrieve information from our DB2 server and I created an external application that consume the same data but via Stored Procedures calls. So I noticed that my application consume more time to retrieve the same data comparing with a JDEdwards Application. Both applications JDE and my are fat clients, so from network perspective there is not difference. I even create a Stored Procedure using the same SQL Script that is executed from JDE through ODBC and it keep consuming more time to response. I ran the SP directly from SQL Tool Script (iSeries Navigator) and I have the same result. Below is an example of my SP Script, JDEdwards use its own iSeries database user credentials to connect, and my app use a different user, but I don't see that database user profile could be an issue. Thanks for your help! CREATE PROCEDURE JDEINT.COM_GETSALESORDERHEADER( IN COMPANY VARCHAR(5) , IN ORDERTYPE VARCHAR(3) , IN ORDERNUMBER DOUBLE PRECISION , IN INSTALLATIONID VARCHAR(3) ) DYNAMIC RESULT SETS 1 LANGUAGE SQL SPECIFIC JDEINT.COM_GETSALESORDERHEADER NOT DETERMINISTIC MODIFIES SQL DATA CALLED ON NULL INPUT SET OPTION ALWBLK = *ALLREAD , ALWCPYDTA = *OPTIMIZE , COMMIT = *NONE , DECRESULT = (31, 31, 00) , DFTRDBCOL = *NONE , DYNDFTCOL = *NO , DYNUSRPRF = *USER , SRTSEQ = *HEX BEGIN DECLARE TEMP_CURSOR1 CURSOR WITH HOLD WITH RETURN TO CLIENT FOR SELECT A . SHKCOO AS COMPANY , A . SHDOCO AS ORDERNUMBER , A . SHDCTO AS ORDERTYPE , SHMCU AS BRANCHPLANT , SHOKCO AS ORIGINALORDERCO , SHOORN AS ORIGINALORDERNO , SHOCTO AS ORIGINALORDERTYPE , SHAN8 AS CUSTOMERNUMBER , SHSHAN AS SHIPTOID FROM CRPDTA . F4201 A WHERE A . SHDOCO = ORDERNUMBER AND A . SHDCTO = ORDERTYPE AND A . SHKCOO = COMPANY UNION SELECT A . SHKCOO AS COMPANY , A . SHDOCO AS ORDERNUMBER , A . SHDCTO AS ORDERTYPE , SHMCU AS BRANCHPLANT , SHOKCO AS ORIGINALORDERCO , SHOORN AS ORIGINALORDERNO , SHOCTO AS ORIGINALORDERTYPE , SHAN8 AS CUSTOMERNUMBER , SHSHAN AS SHIPTOID FROM CRPDTA . F42019 A WHERE A . SHDOCO = ORDERNUMBER AND A . SHDCTO = ORDERTYPE AND A . SHKCOO = COMPANY FOR FETCH ONLY ; OPEN TEMP_CURSOR1 ; END ;
From: Kent Milligan on 29 Jan 2010 14:19 Each SQL interface has a different set of attributes and settings that can cause the query optimizer to implement a different access plan. In your case, I'd guess that the optimization goal setting of the SP is the cause. You could try adding this setting to the cursor declaration: OPTIMIZE FOR 10 ROWS To learn more about the settings that impact the optimizer, I'd strongly encourage you to attend the DB2 for i SQL Performance Workshop. http://ibm.com/systems/i/software/db2/db2educ_m.html Charlie wrote: > Hi All. > I just trying to understand why a stored procedure call takes more > time to response than an ODBC request. My company use JDEdwards with > DB2 UDB database server. JDEdwards use ODBC technology to retrieve > information from our DB2 server and I created an external application > that consume the same data but via Stored Procedures calls. So I > noticed that my application consume more time to retrieve the same > data comparing with a JDEdwards Application. Both applications JDE > and my are fat clients, so from network perspective there is not > difference. I even create a Stored Procedure using the same SQL > Script that is executed from JDE through ODBC and it keep consuming > more time to response. I ran the SP directly from SQL Tool Script > (iSeries Navigator) and I have the same result. Below is an example of > my SP Script, JDEdwards use its own iSeries database user credentials > to connect, and my app use a different user, but I don't see that > database user profile could be an issue. > > Thanks for your help! > > CREATE PROCEDURE JDEINT.COM_GETSALESORDERHEADER( > IN COMPANY VARCHAR(5) , > IN ORDERTYPE VARCHAR(3) , > IN ORDERNUMBER DOUBLE PRECISION , > IN INSTALLATIONID VARCHAR(3) ) > DYNAMIC RESULT SETS 1 > LANGUAGE SQL > SPECIFIC JDEINT.COM_GETSALESORDERHEADER > NOT DETERMINISTIC > MODIFIES SQL DATA > CALLED ON NULL INPUT > SET OPTION ALWBLK = *ALLREAD , > ALWCPYDTA = *OPTIMIZE , > COMMIT = *NONE , > DECRESULT = (31, 31, 00) , > DFTRDBCOL = *NONE , > DYNDFTCOL = *NO , > DYNUSRPRF = *USER , > SRTSEQ = *HEX > BEGIN > > DECLARE TEMP_CURSOR1 CURSOR WITH HOLD WITH RETURN TO CLIENT FOR > SELECT > A . SHKCOO AS COMPANY , > A . SHDOCO AS ORDERNUMBER , > A . SHDCTO AS ORDERTYPE , > SHMCU AS BRANCHPLANT , > SHOKCO AS ORIGINALORDERCO , > SHOORN AS ORIGINALORDERNO , > SHOCTO AS ORIGINALORDERTYPE , > SHAN8 AS CUSTOMERNUMBER , > SHSHAN AS SHIPTOID > FROM CRPDTA . F4201 A > WHERE > A . SHDOCO = ORDERNUMBER > AND A . SHDCTO = ORDERTYPE > AND A . SHKCOO = COMPANY > UNION > SELECT > A . SHKCOO AS COMPANY , > A . SHDOCO AS ORDERNUMBER , > A . SHDCTO AS ORDERTYPE , > SHMCU AS BRANCHPLANT , > SHOKCO AS ORIGINALORDERCO , > SHOORN AS ORIGINALORDERNO , > SHOCTO AS ORIGINALORDERTYPE , > SHAN8 AS CUSTOMERNUMBER , > SHSHAN AS SHIPTOID > FROM CRPDTA . F42019 A > WHERE > A . SHDOCO = ORDERNUMBER > AND A . SHDCTO = ORDERTYPE > AND A . SHKCOO = COMPANY > FOR FETCH ONLY ; > > > OPEN TEMP_CURSOR1 ; > > END ; -- Kent Milligan ISV Enablement - System i kmill(a)us.eye-bee-m.com (spam trick) GO HAWKEYES!! >>> ibm.com/iseries/db2 (opinions stated are not necessarily those of my employer)
From: Serge Rielau on 31 Jan 2010 00:11 in addition there is indeed an extra cost. First you execute the CALL statement which then executes the stored procedure which needs some basic initialization which then opens the cursor. In trivial procedures this can be a "feel-able" overhead. For TPC-C we do not use any stored procedures. Instead we use inline functions for encapsulation since they are macro expanded. Cheers Serge -- Serge Rielau SQL Architect DB2 for LUW IBM Toronto Lab
From: Mark A on 31 Jan 2010 12:07 "Serge Rielau" <srielau(a)ca.ibm.com> wrote in message news:7skho3FgomU1(a)mid.individual.net... > in addition there is indeed an extra cost. > First you execute the CALL statement which then executes the stored > procedure which needs some basic initialization which then opens the > cursor. > > In trivial procedures this can be a "feel-able" overhead. > For TPC-C we do not use any stored procedures. > Instead we use inline functions for encapsulation since they are macro > expanded. > > Cheers > Serge > -- > Serge Rielau > SQL Architect DB2 for LUW > IBM Toronto Lab Would IBM be willing to publish specific examples of "inline functions for encapsulation" as used in TPC-C?
From: Hardy on 31 Jan 2010 21:01
"Mark A" <noone(a)nowhere.com> д����Ϣ news:hk4dd8$dt7$1(a)news.eternal-september.org... > "Serge Rielau" <srielau(a)ca.ibm.com> wrote in message > news:7skho3FgomU1(a)mid.individual.net... >> in addition there is indeed an extra cost. >> First you execute the CALL statement which then executes the stored >> procedure which needs some basic initialization which then opens the >> cursor. >> >> In trivial procedures this can be a "feel-able" overhead. >> For TPC-C we do not use any stored procedures. >> Instead we use inline functions for encapsulation since they are macro >> expanded. >> >> Cheers >> Serge >> -- >> Serge Rielau >> SQL Architect DB2 for LUW >> IBM Toronto Lab > > Would IBM be willing to publish specific examples of "inline functions for > encapsulation" as used in TPC-C? > try this article by Serge, http://www.ibm.com/developerworks/db2/library/techarticle/dm-0411rielau/?S_TACT=105AGX52&S_CMP=cn-a-db2 |