"Runaway" 4GL Processes

Quoting Cris Mazzocco at Hewlett-Packard (18 Sep 92, Subj: "4GL Problem"): > I've run into a problem on an Informix 4GL program, and was wondering if > anyone else had ever encountered a similar problem, or if anyone had > suggestions. ... There is a function in my 4GL application that looks-up > and copies data from one group of tables to another. When run on a given > small set of data, this function takes about 20 seconds to run. However, > if I run this function in multiple windows on different sets of data, the > function takes hours. In fact, I'm not sure if it ever completes because > I've always had to kill it. I encountered the same problem at this site. Specifically, when I do a lookup that spans more than three tables and there is lots of data to work through, and when there are a lot of WHERE/AND clauses involved, I end up with what I thought was at first a runaway 4GL process. Turns out it wasn't. > Doing a "top 10," I noticed that one SQL process seems to dominate the > CPU, taking up to 95+%, and never (?) completing. On closer examination, a ps -ef reveals that the "first" SQL UNIX process generated by the 4GL application appears to have a broken pipe, e.g., he's lost his "connection" to the fglgo <progname> process, although all other SQL UNIX processes point back to this "first" one. Can you repeat this step and verify that this is occurring at your site? > I've also noticed that when this occurs, one table in the database > is locked. I cannot even access it through ISQL, even to just check > its status. I *hadn't* noticed that, but then I never went looking for it, either. > I'm unsure as to how to find the cause of this problem, and > am still unsure if it is Informix or UN*X system related. **************************************************************************** INFORMIX: Is this a known bug? What causes this problem? It "feels" like an engine problem . . . If it isn't a known problem, anyone at Informix may E-Mail or phone me direct & I will work with you to pin it down as a repeatable process (which I find easy to do) and help you diagnose/kill it. **************************************************************************** *I* don't know what causes it, but I did develop a workaround: I create a temporary table to hold the output from the lookup so I can access it directly with a FOREACH to a SINGLE table. To get the data from the multiple tables to the single table I do: INSERT INTO <temp table> SELECT <statement> Of course, with this methodology, you can't use the ORDER BY clause, but doing an ORDER BY from a single table (even when it's unindexed) with a limited number of rows is pretty fast. Unfortunately, it's about twice as slow as it should be, because two SELECTs must be executed instead of one. However, it does have the distinct advantage of *working*! :-) If anyone else has encountered this problem, I'd be most interested to hear about it. If anyone has narrowed down the cause, I'd be even more interested! |-:) TSgt Bill Williams Decision Support System (DSS) Programmer/Analyst Standard Systems Center Gunter AFB, AL 36114-6346 (205) 416-1256/5961 bwilliam@ssplan.ssc.af.mil -OR- dss@ssplan.ssc.af.mil STANDARD DISCLAIMER: --------------------------------------------------------------------------- My judgment is my own and must not be misperceived as being that of the US Air Force, Department of Defense, or the US Government - after all, I can only think for myself and that keeps me busy enough as it is! ---------------------------------------------------------------------------