Tweet
Our company is developing portal applications for the first time. The portal applications are written using Java Struts classes and the portal resides on a server that is not an AS400. The SQL stored procedures I wrote resides on the AS400. My problem is that I have one stored procedure that is performing worse than SQL written in a prepared statement in Java on the portal server. I've reviewed my compile options and timed and called the stored procedure from green screen interactive SQL to verify that its fast. From green screen SQL it runs in about 2 seconds, however, from the portal it takes a minute. From Websphere running on my PC it takes 40 seconds. However, what really stumps me is why the stored procedure call does not beat an identical SQL prepared statement created from the client application and using the same indexes. What other factors that effect the performance of stored procedure calls from a portal application ?
-
-
PROBLEM WITH SLOW SQL STORED PROCEDURE
I have had similar issues with sql statements run over an ODBC connection. I have never gotten a great answer, but it appears that the access plan that the statement uses gets corrupted. A few comments of what we have tried - 1) duplicate the QAQQINI file and add REBUILD_ACCESS_PLAN to the end, then set the ODBC connection to use this QAQQINI (IBM's suggestion) . 2) We found that if we recompiled the logicals that were used, then the access plan would have to be rebuilt - this was a bandaid. 3) Change the sql statement to optimize for one row (may not be practical for you). 4) The thing that finally helped us was that we figured out that the problem occurs when an sql statement like this 'select name from patmas where mrn=123456789012345' is issued and mrn is only 10 long, but the number being checked is longer.....this causes every record in patmas to be checked for this statement and every subsequent statement. We have been golden since we fixed the code where this could not happen. HTH, Joe
Comment