Major performance differences

We have a job that runs on the weekend. A file is submitted to the server. This job takes the file and takes the rows one at a time and queries the table for a unique row. If it finds a unique row already in the table, it updates the row. Otherwise it adds the values in the row to the table. This job has run anywhere from over six hours to about 20 minutes using roughly the same number of rows. Is there anything I can be looking at on the Informix side of things to see why this job might have run for over six hours after it has run for 20 minutes? Any help would be appreciated. Thanks. ______________________ Keith Schleicher DBA - ACN Tech Services Phone: 920-405-7955 NETS: 8-270-7955 email: kschleicher@acnielsen.com

Yes. Perform the update without finding the row first. If the count of affected rows is zero (it's not an error to update zero rows) then do the insert. This will be faster unless at least 70% of transactions are inserts, then you want to try the insert first and update if it fails on a unique/primary key constraint/unique index violation. Art S. Kagel ----- Original Message ----- From: .... Schleicher <KSchleicher@ACNielsen.com> At: 12/ 7 11:58 We have a job that runs on the weekend. A file is submitted to the server. This job takes the file and takes the rows one at a time and queries the table for a unique row. If it finds a unique row already in the table, it updates the row. Otherwise it adds the values in the row to the table. This job has run anywhere from over six hours to about 20 minutes using roughly the same number of rows. Is there anything I can be looking at on the Informix side of things to see why this job might have run for over six hours after it has run for 20 minutes? Any help would be appreciated. Thanks. ______________________ Keith Schleicher DBA - ACN Tech Services Phone: 920-405-7955 NETS: 8-270-7955 email: kschleicher@acnielsen.com

Hi, It could be one of many reasons, 1. May be the size of the table grew fast and update stats has not run yet. 2. May be the update stats did not run. Check and run update stats. 3. May be the indexes are now corrupted and the query is doing a sequential scan each time. If thats the case then rebuild the indexes. 4. May be the table is now heaviley fragmented. If thats the case reorg the table. 5. If this happened only once then it could be network related. 6. If this is happening continuouly then there could be other new processes that was introduced recently that coule be waiting for the same resources. 7. You should have your dba check the table and the database while the problem is occuring to get a better picture. 8. Also run system checks such as sar, mpstat, top, vmsta, prstat, iostat -xne etc to examine any systems level resource contentions. Hope this helps. Ravi. "Schleicher,...." <KSchleicher@ACNielsen.com> wrote: We have a job that runs on the weekend. A file is submitted to the server. This job takes the file and takes the rows one at a time and queries the table for a unique row. If it finds a unique row already in the table, it updates the row. Otherwise it adds the values in the row to the table. This job has run anywhere from over six hours to about 20 minutes using roughly the same number of rows. Is there anything I can be looking at on the Informix side of things to see why this job might have run for over six hours after it has run for 20 minutes? Any help would be appreciated. Thanks. ______________________ Keith Schleicher DBA - ACN Tech Services Phone: 920-405-7955 NETS: 8-270-7955 email: kschleicher@acnielsen.com --------------------------------- Yahoo! DSL Something to write home about. Just $16.99/mo. or less

Schleicher,.... said: > > We have a job that runs on the weekend. A file is submitted to the > server. This job takes the file and takes the rows one at a time and > queries the table for a unique row. If it finds a unique row already in > the table, it updates the row. Otherwise it adds the values in the row > to the table. This job has run anywhere from over six hours to about 20 > minutes using roughly the same number of rows. Is there anything I can > be looking at on the Informix side of things to see why this job might > have run for over six hours after it has run for 20 minutes? UPDATE STATISTICS? -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien ` dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do an insert into the parent table with the primary key value (everything else null), and voila, relational integrity is preserved. but this is, admittedly, a bit controversial among modellers. --r937, dbforums.com

I faced a similar problem several years back, the (legacy) job took 10 hours to run (consistently). I re-wrote the silly singleton row lookup thing into a hash join and the job started taking 20 minutes. You can find a write up of this at: http://www-128.ibm.com/developerworks/db2/zones/informix/library/techarticle /parker/0502parker.html Otherwise, Art, Obie-one, and Ravi are right on (as usual). cheers j. -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On Behalf Of Schleicher,.... Sent: Wednesday, December 07, 2005 11:45 AM To: ids@iiug.org Subject: Major performance differences [6077] We have a job that runs on the weekend. A file is submitted to the server. This job takes the file and takes the rows one at a time and queries the table for a unique row. If it finds a unique row already in the table, it updates the row. Otherwise it adds the values in the row to the table. This job has run anywhere from over six hours to about 20 minutes using roughly the same number of rows. Is there anything I can be looking at on the Informix side of things to see why this job might have run for over six hours after it has run for 20 minutes? Any help would be appreciated. Thanks. ______________________ Keith Schleicher DBA - ACN Tech Services Phone: 920-405-7955 NETS: 8-270-7955 email: kschleicher@acnielsen.com