SQL statement performance bad in 4gl, flies in isq

Okay, don't laugh, but the version is 7.24.UC5 Standard Engine. Woo hoo! A blast from your past. It's running on a variety of AIX versions from 4.2 (again, woo hoo!) to 5.2. So, I've got an SQL statement that looks like this in my 4GL program: INSERT INTO <sometable> SELECT <various fields> FROM <various tables> WHERE <various conditions> It takes about 30 minutes when this is run from a 4GL program, but if I run the same exact SQL statements from isql, it's practically instantaneous. It inserts about 7,000 rows, so it's not huge or anything. I'm sure everyone's first instinct will be why the heck are you still using such an old version of Informix, and that's a question I asked when I interviewed for this job too. Basically, it's because it works for us (although in this case not very well) and they're scared to fix what ain't broke (although it's debatable what the definition of "broke" is). As a secondary question, it was put to me and another co-worker to convince them that we need to upgrade for reasons other than it would be a supported version. We haven't had support for about 6 years, so I'm looking for actual advantages. So far, I've run into 1 stumbling block from using 7.20 c4gl, which is we have to prepare SQL statements in 4gl which use concatenation operators, although this doesn't seem like a very convincing reason to spend thousands of dollars to upgrade. It's my gut feeling that we'd see LOTS of performance improvements from upgrading and I don't think it would be that big of a deal as far as compatibility of our source code or database administration goes, but there is some resistance to this idea from people who think it would be a big deal. I welcome any ideas on improving the performance of my INSERT statement and any ammunition I can use to get us on a more current version of Informix. Thanks in advance, --------------------------------- Yahoo! Photos Ring in the New Year with Photo Calendars. Add photos, events, holidays, whatever.

How big is the FROM table ? Is it possible the when being run from the 4GL its performing a sequential scan e.g. incorrect definition of a local variable - should be a char but defined as an integer so not using an index. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Danny Wright Sent: 11 January 2006 07:41 To: ids@iiug.org Subject: SQL statement performance bad in 4gl, flies in.... [6195] Okay, don't laugh, but the version is 7.24.UC5 Standard Engine. Woo hoo! A blast from your past. It's running on a variety of AIX versions from 4.2 (again, woo hoo!) to 5.2. So, I've got an SQL statement that looks like this in my 4GL program: INSERT INTO <sometable> SELECT <various fields> FROM <various tables> WHERE <various conditions> It takes about 30 minutes when this is run from a 4GL program, but if I run the same exact SQL statements from isql, it's practically instantaneous. It inserts about 7,000 rows, so it's not huge or anything. I'm sure everyone's first instinct will be why the heck are you still using such an old version of Informix, and that's a question I asked when I interviewed for this job too. Basically, it's because it works for us (although in this case not very well) and they're scared to fix what ain't broke (although it's debatable what the definition of "broke" is). As a secondary question, it was put to me and another co-worker to convince them that we need to upgrade for reasons other than it would be a supported version. We haven't had support for about 6 years, so I'm looking for actual advantages. So far, I've run into 1 stumbling block from using 7.20 c4gl, which is we have to prepare SQL statements in 4gl which use concatenation operators, although this doesn't seem like a very convincing reason to spend thousands of dollars to upgrade. It's my gut feeling that we'd see LOTS of performance improvements from upgrading and I don't think it would be that big of a deal as far as compatibility of our source code or database administration goes, but there is some resistance to this idea from people who think it would be a big deal. I welcome any ideas on improving the performance of my INSERT statement and any ammunition I can use to get us on a more current version of Informix. Thanks in advance, --------------------------------- Yahoo! Photos Ring in the New Year with Photo Calendars. Add photos, events, holidays, whatever. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ********************************************************************** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com **********************************************************************

Danny If the sql runs fine in isql, then it cannot be the sql or the database. I have never seen an sql do anything different in an application to what it does in isql / dbaccess. It will be something else in the application. Just to confirm, can you set explain on and run your program. That will give you the explain plan, and may lead you somewhere else for the problem. MW -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Danny Wright Sent: Wednesday, 11 January 2006 8:41 p.m. To: ids@iiug.org Subject: SQL statement performance bad in 4gl, flies in.... [6195] Okay, don't laugh, but the version is 7.24.UC5 Standard Engine. Woo hoo! A blast from your past. It's running on a variety of AIX versions from 4.2 (again, woo hoo!) to 5.2. So, I've got an SQL statement that looks like this in my 4GL program: INSERT INTO <sometable> SELECT <various fields> FROM <various tables> WHERE <various conditions> It takes about 30 minutes when this is run from a 4GL program, but if I run the same exact SQL statements from isql, it's practically instantaneous. It inserts about 7,000 rows, so it's not huge or anything. I'm sure everyone's first instinct will be why the heck are you still using such an old version of Informix, and that's a question I asked when I interviewed for this job too. Basically, it's because it works for us (although in this case not very well) and they're scared to fix what ain't broke (although it's debatable what the definition of "broke" is). As a secondary question, it was put to me and another co-worker to convince them that we need to upgrade for reasons other than it would be a supported version. We haven't had support for about 6 years, so I'm looking for actual advantages. So far, I've run into 1 stumbling block from using 7.20 c4gl, which is we have to prepare SQL statements in 4gl which use concatenation operators, although this doesn't seem like a very convincing reason to spend thousands of dollars to upgrade. It's my gut feeling that we'd see LOTS of performance improvements from upgrading and I don't think it would be that big of a deal as far as compatibility of our source code or database administration goes, but there is some resistance to this idea from people who think it would be a big deal. I welcome any ideas on improving the performance of my INSERT statement and any ammunition I can use to get us on a more current version of Informix. Thanks in advance, --------------------------------- Yahoo! Photos Ring in the New Year with Photo Calendars. Add photos, events, holidays, whatever. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

On 1/11/06, ifxmaillist.... <ifxmaillist@quanta.co.nz> wrote: > If the sql runs fine in isql, then it cannot be the sql or the database. I > have never seen an sql do anything different in an application to what it > does in isql / dbaccess. It will be something else in the application. > > Just to confirm, can you set explain on and run your program. That will > give you the explain plan, and may lead you somewhere else for the problem. I second Murray's view (I hope I've guessed MW correctly). Please provide the SET EXPLAIN output from both the ISQL (or DB-Access) and the I4GL code. If I had to guess at the difference, the I4GL is using one or more placeholders for values, but the ISQL is (of necessity) not. Somehow, the query plans are radically different - an index is unused or something. With SE, this is less critical, but did you ever run UPDATE STATISTICS? If you did, it probably isn't the issue - but there's no harm in asking. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Danny > Wright > Sent: Wednesday, 11 January 2006 8:41 p.m. > To: ids@iiug.org > Subject: SQL statement performance bad in 4gl, flies in.... [6195] > > Okay, don't laugh, but the version is 7.24.UC5 Standard Engine. Woo hoo! A > blast from your past. > > It's running on a variety of AIX versions from 4.2 (again, woo hoo!) to 5.2. > > So, I've got an SQL statement that looks like this in my 4GL program: > > INSERT INTO <sometable> > SELECT <various fields> > FROM <various tables> > WHERE <various conditions> > > It takes about 30 minutes when this is run from a 4GL program, but if I run > the same exact SQL statements from isql, it's practically instantaneous. > > It inserts about 7,000 rows, so it's not huge or anything. > > I'm sure everyone's first instinct will be why the heck are you still using > such an old version of Informix, and that's a question I asked when I > interviewed for this job too. > > Basically, it's because it works for us (although in this case not very well) > and they're scared to fix what ain't broke (although it's debatable what the > definition of "broke" is). > > As a secondary question, it was put to me and another co-worker to convince > them that we need to upgrade for reasons other than it would be a supported > version. We haven't had support for about 6 years, so I'm looking for actual > advantages. > > So far, I've run into 1 stumbling block from using 7.20 c4gl, which is we have > to prepare SQL statements in 4gl which use concatenation operators, although > this doesn't seem like a very convincing reason to spend thousands of > dollars to upgrade. So you *are* using I4GL 7.20 and have to prepare concatenation, but upgrading to 7.3x (x = 2 for preference) would mean that's no longer necessary? There are advantages to prepared SQL statements, done carefully. 'Carefully' does not, in my book, mean prepare everything when the session starts - it means prepare once at the point when you first use them. > It's my gut feeling that we'd see LOTS of performance improvements from > upgrading and I don't think it would be that big of a deal as far as > compatibility of our source code or database administration goes, but there > is > some resistance to this idea from people who think it would be a big deal. > > I welcome any ideas on improving the performance of my INSERT statement and > any ammunition I can use to get us on a more current version of Informix. SE 7.24 and SE 7.26 are mostly the same - you'd not see much in the way of performance change. Similarly, I4GL 7.32 is not going to perform radically differently from I4GL 7.20. However, if you have Tech Support, it is a lot easier to get help on problems like this. And to have Tech Support, you also need more or less current versions of software (including o/s). -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/