Re[4]: Q: Do Stored Procedures actually degrade performance?

David I finally got the SQL EXPLAIN to work. BTW my environment is HP-UX 10.20 and Informix 7.23.UC3. Guess I should have mentioned that earlier. What finally made it work was setting my OPTCOMPIND to 2. We maintain and enhance a system here where lot of the code was written for version 5.x so for compatibility we use OPTCOMPIND=0. We spoke to Informix as well and they said that what you suggested should work for us too. My theory is that since OPTCOMPIND=1 makes the optimizer behave like version 5, and SET EXPLAINs for SPs were not supported for version 5, this may have been left off in the optimizer code for OPTCOMPIND=0. Any insider comments, anyone? In any case we found the EXPLAIN PLANs were clean in the SPs. We obtained some (around 10%) gains by moving SPL global variables into the local variables for SPLs. We are investigating the problem further and will let this group know if we uncover anything. Thanks for all your help. Sujit ______________________________ Reply Separator _________________________________ Subject: Re[3]: Q: Do Stored Procedures actually degrade performance? Author: david.ashby@workcover.nsw.gov.au at Internet Date: 4/17/98 9:56 AM Sujit, Yes that is what I do. I drop procedure, set explain and create procedure, and I get an sqexplain file like this: ---------- Procedure: informix.spl_rpt_cre609_1b select x2.polkey ,x2.termcommencedte ,x1.termcommencedte ,x1.empipno ,x1.prempayl from "informix" .geodimsn x0 ,"informix".poltermfact x1 ,"informix".poltermdimsn x2 where ((((((((x0.addrno = x1. addrno ) AND (x1.polkey = x2.polkey ) ) AND ((? IS NULL ) OR ((x0.postcode >= ? ) AND (x0.postcod e <= ? ) ) ) ) AND ((? = 0 ) OR (x0.wcadistrictno = ? ) ) ) AND ((? = 0 ) OR (x0.wcaregnno = ? ) ) ) AND ((x2.termcommencedte >= ? ) AND (x2.termcommencedte <= ? ) ) ) AND (x0.agglvl = 1 ) ) AND (x2.agglvl = 1 ) ) QUERY: ------ Estimated Cost: 13 Estimated # of Rows Returned: 1 1) informix.poltermdimsn: INDEX PATH Filters: informix.poltermdimsn.agglvl = 1 (1) Index Keys: termcommencedte Lower Index Filter: informix.poltermdimsn.termcommencedte >= '<VAR>' Upper Index Filter: informix.poltermdimsn.termcommencedte <= '<VAR>' 2) informix.poltermfact: INDEX PATH (1) Index Keys: polkey Lower Index Filter: informix.poltermfact.polkey = informix.poltermdimsn.polkey 3) informix.geodimsn: INDEX PATH Filters: (('<VAR>' IS NULL OR (informix.geodimsn.postcode >= '<VAR>' AND informix.geodimsn.po stcode <= '<VAR>' ) ) AND (('<VAR>' = 0 OR informix.geodimsn.wcadistrictno = '<VAR>' ) AND (('<VA R>' = 0 OR informix.geodimsn.wcaregnno = '<VAR>' ) AND informix.geodimsn.agglvl = 1 ) ) ) (1) Index Keys: addrno Lower Index Filter: informix.geodimsn.addrno = informix.poltermfact.addrno ---------- Procedure: informix.spl_rpt_cre609_1b etc. I am using v7.23fc1 on DEC 3.2g. I am also creating the procedure from the shell like dbaccess DB <SPL. David ______________________________ Reply Separator _________________________________ Subject: Re[2]: Q: Do Stored Procedures actually degrade performance? Author: <Sujit.Pal@alltel.com> at WCA-INET Date: 16/4/98 1:10 PM David I tried putting the set explain before creating the procedure like so: SET EXPLAIN ON; CREATE PROCEDURE "informix".procname ... END PROCEDURE; But no sqexplain.out file was generated. Is this what you meant or am I doing something wrong? Sujit ______________________________ Reply Separator _________________________________ Subject: Re: Q: Do Stored Procedures actually degrade performance? Author: david.ashby@workcover.nsw.gov.au at Internet Date: 4/16/98 8:50 AM Sujit, Run set explain on before creating the SPL. Make sure that the access path being used for each SQL statement is the one you want. You may need to run update statistics high for problem tables. The SQL in the SPL will not have values so the optimizer may pick the wrong path. Regards David Ashby ______________________________ Reply Separator _________________________________ Subject: Q: Do Stored Procedures actually degrade performance? Author: <Sujit.Pal@alltel.com> at WCA-INET Date: 15/4/98 3:21 PM Hello All We just implemented a module in our application with Stored Procedures. The module was originally written in 4GL with SQLs embedded in the code. All the SQLs have been checked with SET EXPLAIN ON and all the sequential scans we found were for tables with very small number of rows. In an effort to move the database calls to a seperate access layer, we moved all the SQLs (as is, so the optimizer access paths are the same) to some stored procedures which become 4GL callable database services. There are 29 Stored Procedures for this module (we have a total of 60 stored procedures in the entire system). Each stored procedure has 40 - 1500 lines of SPL code. Doing a before and after we found that performance actually degraded with the stored procedures by around 20%. The primary cause seems to be larger number of buffer reads, isam reads and lock requests on the larger tables in the system. Although we had expected lock requests to go up on sysprocedures, it went up only slightly. The bulk of the lock requests went up on the application tables. Our PC_POOLSIZE value is set to 60. All SPs have had UPDATE STATISTICS FOR PROCEDURE run on them. Does anyone have any ideas as to what could be wrong here? Conventional wisdom suggests that SPs should perform better than regular 4GL if the SPs contain 3-4 SQLs each, thereby combining the results from the server. Is there a chance that optimizer access paths actually change inside a SP? If so, is there a way I could view the access path? Thanks in advance for any insights into this problem. Sujit Pal