RE: Stored Procedure Performance

The other posts are probably on the right track - looking at checkpoints... but having dealt with lots of SPLs, it sounds almost like periodically, something is causing the SPL to re-optimize. I can't think how to check for that off-hand... but if you exhaust all other possibilities, then this may be a factor... The only things that come to mind about re-optimizing are when: SPL permissions are changed (grant, revoke) SPL is reinstalled, or dropped child SPL is reinstalled or dropped or permissions changed Table in SPL is structurally changed (create table, create index, alter table, drop index, rename, etc) Table in SPL has permissions changed (grant, revoke) Another thought jumps to mind that I see in my installation, my schema has several very large tables with very similar indices. I've seen the same application use the wrong index sometimes, then use the better index other times (yes, even with pretty decent update-statistics). For those conditions, we use optimizer directives in the SQL to hint the correct index. You can get stats by partition id, and detached indices will get their own partition id. In my case, when a program is using the wrong index it usually shows up clearly on the partition statistics. -----Original Message----- From: Joe Pacelli [mailto:joepacelli@earthlink.net] Sent: Tuesday, July 01, 2003 9:22 AM To: informix-list@iiug.org Subject: Stored Procedure Performance I've got a stored procedure which is called from a VB application on the clients machine. This SPL takes about 2-3 seconds to run. I can run this SPL over and over and recieve 2-3 second results. Then all of sudden it will go to 40+ seconds and stay this way for 1-2 minutes. It will then eventually return back to the normal 2-3 seconds. The client has 2 databases on this instance. One a live and one a test. The live has no problems and does not see this issue. While running this on test I've ran onstat and found no exclusive locks, yet it just sits there. There is no one else hitting this test base other than myself. Any ideas of what to look for. I've put set explain with stored procedure and it revealed nothing. Thanks, Joe P Senior Software Engineer ********************************************************************** Privileged/Confidential information may be contained in this message. If you are not an addressee indicated in this message (or responsible for delivery of the message to such person[s]), you may not copy or deliver this message to anyone. If you have received this message in error, you should destroy and delete it from your computer and notify the sender by reply email. Opinions, conclusions and other information in this message that do not relate to the official business of Diversified Collection Services shall be understood as neither given nor endorsed by the company. Accordingly, Diversified Collection Services disclaims all responsibility and accepts no responsibility for the consequences of any person(s) acting, or refraining from acting, on such information prior to the receipt by that person(s) of subsequent written confirmation. ********************************************************************** sending to informix-list