multiple threads using Informix 4gl

Ok, people - first post here. First off - I'm not Informix knowledgeable in anyway - I seek the advice of you, the experts...I'm actually a software development team lead trying to keep an older Informix middle-tier alive a little longer. So here's the setup (sorry for the length): We have a 4gl program which performs an interative process - say summing up year to date taxes collected from a number of checks for an individual (but imagine something a bit more complex). The current program fetches a list of people, then goes through the list one by one, performing the necessary calculations, going to the next person, and so on. What I've asked to be done is to break the list apart, and calculate details for several people at once, since one person's totals aren't linked in any way to anothers, the purpose being to get the job done quicker. The "program" is actually several 4ge's - the first determines the list of people, then launches several copies of the primary program, passing it parameters so each copy will know what it's slice of the pie is. They have done this, with some interesting results...it seems that if we break the list into two equal parts, the overall process is only slightly faster. We've tried breaking it out into 2, 4,6,12, and 16 separate processes - with 12 separate programs taking about 40% less time. The CPU and disk I/O on the box doing the work are ok - more than 70% free. So the question is - has anyone done anything like this? And if so, how come with 3 "threads", we don't see a more significant reduction in processing time? If it helps, we're running this on an RS6000 running AIX with about 2 gig RAM (guessing, but at least a gig), and 4 CPU's. The database is actually a separate box, similarly equipped.

There are a few things to consider, firstly I/O, with each of these applications hitting the same tables, they need to share time. secondly CPU, four processors, these things have to run somewhere, whats the load averages look like? secondly the object, are they built shared or static linked, we just had our box grind to a halt because of a 4ge which was build for shared (traced that one to the filesystem cache). Thirdly, I see you reference a database on another box, check the network speed on that thing and the amount of cache allocated to the network. If Informix is the only real service running on it aside from the O/S, push the resources its way brett > The "program" is actually several 4ge's - the first determines the list of > people, then launches several copies of the primary program, passing it > parameters so each copy will know what it's slice of the pie is. > > They have done this, with some interesting results...it seems that if we > break the list into two equal parts, the overall process is only slightly > faster. We've tried breaking it out into 2, 4,6,12, and 16 separate > processes - with 12 separate programs taking about 40% less time. The CPU > and disk I/O on the box doing the work are ok - more than 70% free. > > So the question is - has anyone done anything like this? And if so, how > come with 3 "threads", we don't see a more significant reduction in > processing time? > > If it helps, we're running this on an RS6000 running AIX with about 2 gig > RAM (guessing, but at least a gig), and 4 CPU's. The database is actually a > separate box, similarly equipped.

Make sure all the tables are using ROW MODE locking not PAGE MODE locking which will block more. That is the likely cultprit. Art S. Kagel Eric Fontaine wrote: > > Ok, people - first post here. > > First off - I'm not Informix knowledgeable in anyway - I seek the advice of > you, the experts...I'm actually a software development team lead trying to > keep an older Informix middle-tier alive a little longer. > > So here's the setup (sorry for the length): > > We have a 4gl program which performs an interative process - say summing up > year to date taxes collected from a number of checks for an individual (but > imagine something a bit more complex). The current program fetches a list > of people, then goes through the list one by one, performing the necessary > calculations, going to the next person, and so on. > > What I've asked to be done is to break the list apart, and calculate details > for several people at once, since one person's totals aren't linked in any > way to anothers, the purpose being to get the job done quicker. > > The "program" is actually several 4ge's - the first determines the list of > people, then launches several copies of the primary program, passing it > parameters so each copy will know what it's slice of the pie is. > > They have done this, with some interesting results...it seems that if we > break the list into two equal parts, the overall process is only slightly > faster. We've tried breaking it out into 2, 4,6,12, and 16 separate > processes - with 12 separate programs taking about 40% less time. The CPU > and disk I/O on the box doing the work are ok - more than 70% free. > > So the question is - has anyone done anything like this? And if so, how > come with 3 "threads", we don't see a more significant reduction in > processing time? > > If it helps, we're running this on an RS6000 running AIX with about 2 gig > RAM (guessing, but at least a gig), and 4 CPU's. The database is actually a > separate box, similarly equipped.

I was somewhat sent into the land of confusion when you talked about multiple threading 4GL. Multiple threads are different to that of multiple processes. A process in essence is a program that is executing and a process can run many threads, if coded in such a way. If I remember rightly, 4GL offers single thread development and unless you drop down to ESQL/C to add extensions. You have to be careful when running multiple processes due to the context switching that occurs, which is very costly. Although the CPU might not be busy, the OS is having to switch in and out each process in turn of it's scheduled time slice and that takes time. Have you tried upping the process priority within the OS? Have you considered fragmenting the table being queried/updated to reduce disk contention and then have each process you execute handle the data on each fragment. You'll need a fragmentation strategy, then redesign and re-map your data. Has the 4GL code been optimised using dynamic SQL (preparing/declaring all repetitive SQL)? Is ESQL/C an option, if so, rewriting the code in ESQL/C alone CAN give a boost in performance, all depends on how well it is written and how much work is taken away from the engine. The best thing to do is to have the code analysed first to see what performance tuning can be achieved. "Eric Fontaine" <Efontaine@houston.rr.com> wrote in message news:MtGr5.23826$C5.463924@typhoon.austin.rr.com... > Ok, people - first post here. > > First off - I'm not Informix knowledgeable in anyway - I seek the advice of > you, the experts...I'm actually a software development team lead trying to > keep an older Informix middle-tier alive a little longer. > > So here's the setup (sorry for the length): > > We have a 4gl program which performs an interative process - say summing up > year to date taxes collected from a number of checks for an individual (but > imagine something a bit more complex). The current program fetches a list > of people, then goes through the list one by one, performing the necessary > calculations, going to the next person, and so on. > > What I've asked to be done is to break the list apart, and calculate details > for several people at once, since one person's totals aren't linked in any > way to anothers, the purpose being to get the job done quicker. > > The "program" is actually several 4ge's - the first determines the list of > people, then launches several copies of the primary program, passing it > parameters so each copy will know what it's slice of the pie is. > > They have done this, with some interesting results...it seems that if we > break the list into two equal parts, the overall process is only slightly > faster. We've tried breaking it out into 2, 4,6,12, and 16 separate > processes - with 12 separate programs taking about 40% less time. The CPU > and disk I/O on the box doing the work are ok - more than 70% free. > > So the question is - has anyone done anything like this? And if so, how > come with 3 "threads", we don't see a more significant reduction in > processing time? > > If it helps, we're running this on an RS6000 running AIX with about 2 gig > RAM (guessing, but at least a gig), and 4 CPU's. The database is actually a > separate box, similarly equipped. > >