Insert disappear without any error?

Posted in 2006

Summary

A user on IDS 7.31 (AIX 4.3.3) reported an INSERT INTO ... SELECT that ran about two hours and then vanished with no error. Testing showed the SELECT alone never finished either (5+ hours). SET EXPLAIN revealed an enormous cost, caused mainly by a correlated EXISTS sub-query (with a pointless GROUP BY inside) executed per row. Jonathan Leffler suggested flattening the sub-query tables into the main FROM/WHERE joins and replacing the metacharacter-free LIKE with '='; the rewritten query returned data in seconds. Why the original INSERT silently disappeared was never explained.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: General Discussion

Guo, Denny — Tue, 20 Jun 2006 20:42:34 — source: IIUG Forums & Mailing Lists

Hi Gurus, I have a insert statement, it runs around 2 hours, then just disappear? Any ideas? Aix 4.3.3.0 Informix: Version 7.31.UD Thanks, Denny ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques .

↪ replying to Guo, Denny

Yunyao (Frank) Qu — Tue, 20 Jun 2006 20:55:54 — source: IIUG Forums & Mailing Lists

You need to provide the insert statement and the table definition (dbschema -d db -t tab -ss) Frank Guo, Denny wrote: >Hi Gurus, > >I have a insert statement, it runs around 2 hours, then just disappear? >Any ideas? > >Aix 4.3.3.0 >Informix: Version 7.31.UD > >Thanks, >Denny > >******************************************* >The information contained in this e-mail message may >contain privileged and confidential information. >If you are not the intended recipient, you are >hereby notified that any review, dissemination, >distribution or duplication of this communication >is strictly prohibited. If you have received this >message in error, please notify the sender by return >e-mail, delete this message and destroy any copies. >Internet e- mail is not guaranteed to be secure or >error-free. Messages could be intercepted, corrupted, >lost, arrive late or contain viruses. >The sender will not be liable for >these risks. > >******************************************* >Ce message electronique pourrait contenir des >informations privilegiees et confidentielles. Si vous >n'en etes pas le recipiendaire prevu, nous vous >signalons qu'il est strictement interdit d'examiner, >de diffuser, de distribuer et de reproduire le >present message. Si vous l'avez recu par erreur, >veuillez prevenir l'expediteur par courriel, puis >effacer ce message et en detruire toute copie. >Le courrier electronique n'est pas garanti >securitaire ni exempt d'erreurs. Les messages >pourraient etre interceptes, corrompus, egares, >retardes ou contamines par des virus. >L'exp'editeur n'est pas >responsable de ces risques . > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > -- Yunyao "Frank" Qu Computer Sciences Corporation(CSC) NOAA/CLASS, (301)817-4696

↪ replying to Yunyao (Frank) Qu

Guo, Denny — Wed, 21 Jun 2006 12:15:06 — source: IIUG Forums & Mailing Lists

Below is the statement. CREATE TABLE t1514 (acctsecurno INTEGER NOT NULL, transno INTEGER NOT NULL, approveddate CHAR(20) NOT NULL, purchaseorder1 CHAR(15) NOT NULL, vendorname CHAR(25) NOT NULL, totb3vfd FLOAT NOT NULL, totb3duty FLOAT NOT NULL, totb3gst FLOAT NOT NULL, liibrchno INTEGER NOT NULL,liirefno INTEGER NOT NULL); insert into t1514 select distinct b3.acctsecurno, b3.transno, b3.approveddate, b3.purchaseorder1, b3.vendorname, b3.totb3vfd, b3.totb3duty, b3.totb3gst, liibrchno, liirefno from srch_crit_batch, b3 where ( srch_crit_batch.tablename = 't1514' ) and ( srch_crit_batch.useriid = 1144 ) and ( srch_crit_batch.liiclientno = b3.liiclientno ) and ( srch_crit_batch.liiaccountno = b3.liiaccountno ) and ( b3.status between 505 and 508 ) and exists ( select b3_subheader.b3iid from b3_subheader, b3_line, b3_recap_details where ( b3_subheader.b3iid = b3.b3iid ) and ( b3_subheader.b3subiid = b3_line.b3subiid ) and ( b3_line.b3lineiid = b3_recap_details.b3lineiid ) and ( b3_recap_details.proddesc like '371293-405' ) group by b3_subheader.b3iid ); Thanks for any advice. Denny -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Yunyao (Fra.... Sent: June 20, 2006 4:56 PM To: ids@iiug.org Subject: Re: Insert disappear without any error? [7005] You need to provide the insert statement and the table definition (dbschema -d db -t tab -ss) Frank Guo, Denny wrote: >Hi Gurus, > >I have a insert statement, it runs around 2 hours, then just disappear? >Any ideas? > >Aix 4.3.3.0 >Informix: Version 7.31.UD > >Thanks, >Denny > >******************************************* >The information contained in this e-mail message may contain privileged >and confidential information. >If you are not the intended recipient, you are hereby notified that any >review, dissemination, distribution or duplication of this >communication is strictly prohibited. If you have received this message >in error, please notify the sender by return e-mail, delete this >message and destroy any copies. >Internet e- mail is not guaranteed to be secure or error-free. Messages >could be intercepted, corrupted, lost, arrive late or contain viruses. >The sender will not be liable for >these risks. > >******************************************* >Ce message electronique pourrait contenir des informations privilegiees >et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous >vous signalons qu'il est strictement interdit d'examiner, de diffuser, >de distribuer et de reproduire le present message. Si vous l'avez recu >par erreur, veuillez prevenir l'expediteur par courriel, puis effacer >ce message et en detruire toute copie. >Le courrier electronique n'est pas garanti securitaire ni exempt >d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, >retardes ou contamines par des virus. >L'exp'editeur n'est pas >responsable de ces risques . > > >*********************************************************************** >******** Forum Note: Use "Reply" to post a response in the discussion >forum. > > > > -- Yunyao "Frank" Qu Computer Sciences Corporation(CSC) NOAA/CLASS, (301)817-4696 ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques .

↪ replying to Guo, Denny

ChrisSalch — Wed, 21 Jun 2006 13:25:47 — source: IIUG Forums & Mailing Lists

Do you get any data from the select statment if you run it by itself? Guo, Denny wrote: > Below is the statement. > > CREATE TABLE t1514 > (acctsecurno INTEGER NOT NULL, > transno INTEGER NOT NULL, > approveddate CHAR(20) NOT NULL, > purchaseorder1 CHAR(15) NOT NULL, > vendorname CHAR(25) NOT NULL, > totb3vfd FLOAT NOT NULL, > totb3duty FLOAT NOT NULL, > totb3gst FLOAT NOT NULL, > liibrchno INTEGER NOT NULL,> liirefno INTEGER NOT NULL); > > insert into t1514 > select distinct b3.acctsecurno, b3.transno,> > b3.approveddate, b3.purchaseorder1, b3.vendorname, b3.totb3vfd, > > b3.totb3duty, b3.totb3gst, liibrchno, liirefno > from srch_crit_batch, b3 > where ( srch_crit_batch.tablename = 't1514' ) and ( > > srch_crit_batch.useriid = 1144 ) and ( srch_crit_batch.liiclientno = > > b3.liiclientno ) and ( srch_crit_batch.liiaccountno = > b3.liiaccountno ) > > and ( b3.status between 505 and 508 ) and exists ( select > > b3_subheader.b3iid from b3_subheader, b3_line, b3_recap_details > where ( > > b3_subheader.b3iid = b3.b3iid ) and ( b3_subheader.b3subiid = > > b3_line.b3subiid ) and ( b3_line.b3lineiid = > b3_recap_details.b3lineiid ) > > and ( b3_recap_details.proddesc like '371293-405' ) group by > > b3_subheader.b3iid ); > > Thanks for any advice. > Denny > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Yunyao (Fra.... > Sent: June 20, 2006 4:56 PM > To: ids@iiug.org > Subject: Re: Insert disappear without any error? [7005] > > You need to provide the insert statement and the table definition > (dbschema -d db -t tab -ss) Frank > > Guo, Denny wrote: > > >> Hi Gurus, >> >> I have a insert statement, it runs around 2 hours, then just disappear? >> > > >> Any ideas? >> >> Aix 4.3.3.0 >> Informix: Version 7.31.UD >> >> Thanks, >> Denny >> >> ******************************************* >> The information contained in this e-mail message may contain privileged >> > > >> and confidential information. >> If you are not the intended recipient, you are hereby notified that any >> > > >> review, dissemination, distribution or duplication of this >> communication is strictly prohibited. If you have received this message >> > > >> in error, please notify the sender by return e-mail, delete this >> message and destroy any copies. >> Internet e- mail is not guaranteed to be secure or error-free. Messages >> > > >> could be intercepted, corrupted, lost, arrive late or contain viruses. >> The sender will not be liable for >> these risks. >> >> ******************************************* >> Ce message electronique pourrait contenir des informations privilegiees >> > > >> et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous >> vous signalons qu'il est strictement interdit d'examiner, de diffuser, >> de distribuer et de reproduire le present message. Si vous l'avez recu >> par erreur, veuillez prevenir l'expediteur par courriel, puis effacer >> ce message et en detruire toute copie. >> Le courrier electronique n'est pas garanti securitaire ni exempt >> d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, >> > > >> retardes ou contamines par des virus. >> L'exp'editeur n'est pas >> responsable de ces risques . >> >> >> > > >> *********************************************************************** >> ******** Forum Note: Use "Reply" to post a response in the discussion >> forum. >> >> >> >> >> > >

↪ replying to ChrisSalch

Guo, Denny — Wed, 21 Jun 2006 13:42:58 — source: IIUG Forums & Mailing Lists

I run the "select" statement by using dbaccess. I wail for more than 2 hours without any data returns, I have to cancel the job. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ChrisSalch Sent: June 21, 2006 9:26 AM To: ids@iiug.org Subject: Re: Insert disappear without any error? [7014] Do you get any data from the select statment if you run it by itself? Guo, Denny wrote: > Below is the statement. > > CREATE TABLE t1514 > (acctsecurno INTEGER NOT NULL, > transno INTEGER NOT NULL, > approveddate CHAR(20) NOT NULL, > purchaseorder1 CHAR(15) NOT NULL, > vendorname CHAR(25) NOT NULL, > totb3vfd FLOAT NOT NULL, > totb3duty FLOAT NOT NULL, > totb3gst FLOAT NOT NULL, > liibrchno INTEGER NOT NULL,> liirefno INTEGER NOT NULL); > > insert into t1514 > select distinct b3.acctsecurno, b3.transno,> > b3.approveddate, b3.purchaseorder1, b3.vendorname, b3.totb3vfd, > > b3.totb3duty, b3.totb3gst, liibrchno, liirefno from srch_crit_batch, > b3 where ( srch_crit_batch.tablename = 't1514' ) and ( > > srch_crit_batch.useriid = 1144 ) and ( srch_crit_batch.liiclientno = > > b3.liiclientno ) and ( srch_crit_batch.liiaccountno = b3.liiaccountno > ) > > and ( b3.status between 505 and 508 ) and exists ( select > > b3_subheader.b3iid from b3_subheader, b3_line, b3_recap_details where > ( > > b3_subheader.b3iid = b3.b3iid ) and ( b3_subheader.b3subiid = > > b3_line.b3subiid ) and ( b3_line.b3lineiid = > b3_recap_details.b3lineiid ) > > and ( b3_recap_details.proddesc like '371293-405' ) group by > > b3_subheader.b3iid ); > > Thanks for any advice. > Denny > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Yunyao (Fra.... > Sent: June 20, 2006 4:56 PM > To: ids@iiug.org > Subject: Re: Insert disappear without any error? [7005] > > You need to provide the insert statement and the table definition > (dbschema -d db -t tab -ss) Frank > > Guo, Denny wrote: > > >> Hi Gurus, >> >> I have a insert statement, it runs around 2 hours, then just disappear? >> > > >> Any ideas? >> >> Aix 4.3.3.0 >> Informix: Version 7.31.UD >> >> Thanks, >> Denny >> >> ******************************************* >> The information contained in this e-mail message may contain >> privileged >> > > >> and confidential information. >> If you are not the intended recipient, you are hereby notified that >> any >> > > >> review, dissemination, distribution or duplication of this >> communication is strictly prohibited. If you have received this >> message >> > > >> in error, please notify the sender by return e-mail, delete this >> message and destroy any copies. >> Internet e- mail is not guaranteed to be secure or error-free. >> Messages >> > > >> could be intercepted, corrupted, lost, arrive late or contain viruses. >> The sender will not be liable for >> these risks. >> >> ******************************************* >> Ce message electronique pourrait contenir des informations >> privilegiees >> > > >> et confidentielles. Si vous n'en etes pas le recipiendaire prevu, >> nous vous signalons qu'il est strictement interdit d'examiner, de >> diffuser, de distribuer et de reproduire le present message. Si vous >> l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, >> puis effacer ce message et en detruire toute copie. >> Le courrier electronique n'est pas garanti securitaire ni exempt >> d'erreurs. Les messages pourraient etre interceptes, corrompus, >> egares, >> > > >> retardes ou contamines par des virus. >> L'exp'editeur n'est pas >> responsable de ces risques . >> >> >> > > >> ********************************************************************* >> ** >> ******** Forum Note: Use "Reply" to post a response in the discussion >> forum. >> >> >> >> >> > > ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques .

↪ replying to Guo, Denny

Jonathan Leffler — Fri, 23 Jun 2006 05:47:02 — source: IIUG Forums & Mailing Lists

On 6/20/06, Guo, Denny <DGuo@livingstonintl.com> wrote: > I have a insert statement, it runs around 2 hours, then just disappear? > Any ideas? > > Aix 4.3.3.0 > Informix: Version 7.31.UD Your machine is running an o/s that wants to be retired? What's the digit after the D? Is the statement rolled back? Is it a MODE ANSI database? Do you have a long transaction? What form does the INSERT statement have? Presumably, it is INSERT INTO x SELECT ....? -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

↪ replying to Guo, Denny

Jonathan Leffler — Fri, 23 Jun 2006 05:57:39 — source: IIUG Forums & Mailing Lists

On 6/21/06, Guo, Denny <DGuo@livingstonintl.com> wrote: > I run the "select" statement by using dbaccess. > I wail for more than 2 hours without any data returns, I have to cancel > the job. You don't have to wail while you wait :-) So, start the query again, but include SET EXPLAIN ON before hand. Look at the query plan - it will probably explain why the query takes so long. Have you run UPDATE STATISTICS? If the query alone doesn't complete in two hours, the INSERT won't either. And if the query returns no data, the INSERT won't do anything useful either. > -----Original Message----- > From: And please - everyone - clean out this sort of junk! > > > >> ********************************************************************* > >> ** > >> ******** Forum Note: Use "Reply" to post a response in the discussion > > >> forum. > >> > >> > >> > >> > >> > > > > > > ************************************************************************ > ******* > Forum Note: Use "Reply" to post a response in the discussion forum. > > ******************************************* > The information contained in this e-mail message may > contain privileged and confidential information. > If you are not the intended recipient, you are > hereby notified that any review, dissemination, > distribution or duplication of this communication > is strictly prohibited. If you have received this > message in error, please notify the sender by return > e-mail, delete this message and destroy any copies. > Internet e- mail is not guaranteed to be secure or > error-free. Messages could be intercepted, corrupted, > lost, arrive late or contain viruses. > The sender will not be liable for > these risks. > > ******************************************* > Ce message electronique pourrait contenir des > informations privilegiees et confidentielles. Si vous > n'en etes pas le recipiendaire prevu, nous vous > signalons qu'il est strictement interdit d'examiner, > de diffuser, de distribuer et de reproduire le > present message. Si vous l'avez recu par erreur, > veuillez prevenir l'expediteur par courriel, puis > effacer ce message et en detruire toute copie. > Le courrier electronique n'est pas garanti > securitaire ni exempt d'erreurs. Les messages > pourraient etre interceptes, corrompus, egares, > retardes ou contamines par des virus. > L'exp'editeur n'est pas > responsable de ces risques . > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

↪ replying to Jonathan Leffler

Guo, Denny — Fri, 23 Jun 2006 13:13:38 — source: IIUG Forums & Mailing Lists

Thanks Jonathan, I try to run the query again. It takes even 5 hours and does not complete. Below are the explain output. QUERY: ------ select distinct b3.acctsecurno, b3.transno, b3.approveddate, b3.purchaseorder1, b3.vendorname, b3.totb3vfd, b3.totb3duty, b3.totb3gst, liibrchno, liirefno from srch_crit_batch, b3 where ( srch_crit_batch.useriid = 1144) and ( srch_crit_batch.liiclientno = b3.liiclientno ) and ( srch_crit_batch.liiaccountno = b3.liiaccountno ) and ( b3.status between 505 and 508 ) and exists ( select b3_subheader.b3iid from b3_subheader, b3_line, b3_recap_details where ( b3_subheader.b3iid = b3.b3iid ) and ( b3_subheader.b3subiid = b3_line.b3subiid ) and ( b3_line.b3lineiid = b3_recap_details.b3lineiid ) and ( b3_recap_details.proddesc like '371293-405' ) group by b3_subheader.b3iid ) Estimated Cost: 17724470 Estimated # of Rows Returned: 1465299 1) informix.b3: INDEX PATH Filters: EXISTS <subquery> (1) Index Keys: status (Serial, fragments: ALL) Lower Index Filter: informix.b3.status >= 505 Upper Index Filter: informix.b3.status <= 508 2) informix.srch_crit_batch: INDEX PATH (1) Index Keys: useriid Lower Index Filter: informix.srch_crit_batch.useriid = 1144 DYNAMIC HASH JOIN Dynamic Hash Filters: (informix.b3.liiaccountno = informix.srch_crit_batch.liiaccountno AND informix.b3.liiclientno = informix.srch_crit_batch.liiclientno ) Subquery: --------- Estimated Cost: 9 Estimated # of Rows Returned: 1 Temporary Files Required For: Group By 1) informix.b3_recap_details: INDEX PATH (1) Index Keys: proddesc (Serial, fragments: ALL) Lower Index Filter: informix.b3_recap_details.proddesc = '371293-405' 2) informix.b3_line: INDEX PATH (1) Index Keys: b3lineiid Lower Index Filter: informix.b3_line.b3lineiid = informix.b3_recap_details.b3lineiid NESTED LOOP JOIN 3) informix.b3_subheader: INDEX PATH Filters: informix.b3_subheader.b3iid = informix.b3.b3iid (1) Index Keys: b3subiid Lower Index Filter: informix.b3_subheader.b3subiid = informix.b3_line.b3subiid NESTED LOOP JOIN -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jonathan Le.... Sent: June 23, 2006 1:58 AM To: ids@iiug.org Subject: Re: Insert disappear without any error? [7040] On 6/21/06, Guo, Denny <DGuo@livingstonintl.com> wrote: > I run the "select" statement by using dbaccess. > I wail for more than 2 hours without any data returns, I have to > cancel the job. You don't have to wail while you wait :-) So, start the query again, but include SET EXPLAIN ON before hand. Look at the query plan - it will probably explain why the query takes so long. Have you run UPDATE STATISTICS? If the query alone doesn't complete in two hours, the INSERT won't either. And if the query returns no data, the INSERT won't do anything useful either. ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques .

↪ replying to Guo, Denny

Jonathan Leffler — Sat, 24 Jun 2006 05:39:00 — source: IIUG Forums & Mailing Lists

On 6/23/06, Guo, Denny <DGuo@livingstonintl.com> wrote: > > Thanks Jonathan, > I try to run the query again. It takes even 5 hours and does not > complete. > Below are the explain output. > > QUERY: > ------ > select distinct b3.acctsecurno, b3.transno, b3.approveddate, > b3.purchaseorder1, b3.vendorname, b3.totb3vfd,> b3.totb3duty, b3.totb3gst, liibrchno, liirefno > from srch_crit_batch, b3 > where ( srch_crit_batch.useriid = 1144) > and ( srch_crit_batch.liiclientno = b3.liiclientno ) > and ( srch_crit_batch.liiaccountno = b3.liiaccountno ) > and ( b3.status between 505 and 508 ) > and exists ( select b3_subheader.b3iid > from b3_subheader, b3_line, b3_recap_details > where ( b3_subheader.b3iid = b3.b3iid ) > and ( b3_subheader.b3subiid = b3_line.b3subiid ) > and ( b3_line.b3lineiid = b3_recap_details.b3lineiid ) > and ( b3_recap_details.proddesc like '371293-405' ) > group by b3_subheader.b3iid ) > > Estimated Cost: 17724470 > Estimated # of Rows Returned: 1465299 Those are astronomical costs. A major part of the trouble is the correlated sub-query in the EXISTS clause. Another problem is likely to be the GROUP BY within that sub-query; I can see no reason to include that other than to slow the processing down. Another problem could have been the use of LIKE with no metacharacters; fortunately, the optimizer translates it into '=' as it should do. Did you run UPDATE STATISTICS on the tables - correctly? Can you rewrite the query? A correlated sub-query is run once per row - which is fiendishly expensive. It looks like you should simply combine the EXISTS tables into the main FROM clause: select distinct b3.acctsecurno, b3.transno, b3.approveddate, b3.purchaseorder1, b3.vendorname, b3.totb3vfd, b3.totb3duty, b3.totb3gst, srch_crit_batch.liibrchno, srch_crit_batch.liirefno from srch_crit_batch, b3, b3_subheader, b3_line, b3_recap_details where ( srch_crit_batch.useriid = 1144) and ( srch_crit_batch.liiclientno = b3.liiclientno ) and ( srch_crit_batch.liiaccountno = b3.liiaccountno ) and ( b3.status between 505 and 508 ) and ( b3_subheader.b3iid = b3.b3iid ) and ( b3_subheader.b3subiid = b3_line.b3subiid ) and ( b3_line.b3lineiid = b3_recap_details.b3lineiid ) and ( b3_recap_details.proddesc = '371293-405' ) I took a guess at which table contains liirefno and librchno - fix as required. If this doesn't start producing results after an hour or so, kill it, and rerun with SET EXPLAIN ON again, and resubmit the information. > 1) informix.b3: INDEX PATH > > Filters: EXISTS <subquery> > > (1) Index Keys: status (Serial, fragments: ALL) > > Lower Index Filter: informix.b3.status >= 505 > > Upper Index Filter: informix.b3.status <= 508 > > 2) informix.srch_crit_batch: INDEX PATH > > (1) Index Keys: useriid > > Lower Index Filter: informix.srch_crit_batch.useriid = 1144 > > DYNAMIC HASH JOIN > > Dynamic Hash Filters: (informix.b3.liiaccountno = > informix.srch_crit_batch.liiaccountno AND informix.b3.liiclientno = > informix.srch_crit_batch.liiclientno ) > > Subquery: > > --------- > > Estimated Cost: 9 > > Estimated # of Rows Returned: 1 > > Temporary Files Required For: Group By > > 1) informix.b3_recap_details: INDEX PATH > > (1) Index Keys: proddesc (Serial, fragments: ALL) > > Lower Index Filter: informix.b3_recap_details.proddesc = > '371293-405' > > 2) informix.b3_line: INDEX PATH > > (1) Index Keys: b3lineiid > > Lower Index Filter: informix.b3_line.b3lineiid = > informix.b3_recap_details.b3lineiid > NESTED LOOP JOIN > > 3) informix.b3_subheader: INDEX PATH > > Filters: informix.b3_subheader.b3iid = informix.b3.b3iid > > (1) Index Keys: b3subiid > > Lower Index Filter: informix.b3_subheader.b3subiid = > informix.b3_line.b3subiid > NESTED LOOP JOIN > > -----Original Message----- > From: ids-bounces@iiug.org On Behalf Of Jonathan Leffler > Sent: June 23, 2006 1:58 AM > > On 6/21/06, Guo, Denny <DGuo@livingstonintl.com> wrote: > > I run the "select" statement by using dbaccess. > > I wail for more than 2 hours without any data returns, I have to > > cancel the job. > > You don't have to wail while you wait :-) > > So, start the query again, but include SET EXPLAIN ON before hand. > Look at the query plan - it will probably explain why the query takes so > long. Have you run UPDATE STATISTICS? If the query alone doesn't > complete in two hours, the INSERT won't either. And if the query returns > no data, the INSERT won't do anything useful either. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

↪ replying to Jonathan Leffler

Guo, Denny — Mon, 26 Jun 2006 13:11:40 — source: IIUG Forums & Mailing Lists

Thanks Jonathan, With the modified SQL statement, we can get data in few seconds. We run UPDATE STATISTICS on these tables every night. I know the problem is "exists" and "group by" clause. This is performance issue. But does it cause the "insert" statement to disappear without error? Denny ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques .

↪ replying to Guo, Denny

Jonathan Leffler — Tue, 27 Jun 2006 07:18:13 — source: IIUG Forums & Mailing Lists

On 6/26/06, Guo, Denny <DGuo@livingstonintl.com> wrote: > Thanks Jonathan, > > With the modified SQL statement, we can get data in few seconds. OK - so a major part of the problem is the correlated sub-query, as I suggested (eventually). > We run UPDATE STATISTICS on these tables every night. If the tables change sufficiently radically each day, that is appropriate. > I know the problem is "exists" and "group by" clause. This is > performance issue. Partly - also partly application design, where I mean the design of the SQL statement. Question: what does the version with (a correlated sub-query and) EXISTS and GROUP BY do for you that the version with simple regular joins (plus the DISTINCT that was already there) does not do? Why do you think it better to make the DBMS work overtime for hours on end when you can get the equivalent results in seconds? No - IDS is not clever enough to optimize away an unnecessary EXISTS with correlated sub-query plus a GROUP BY clause within that. Nor do I expect it do so. Why do you think it should be able to do so (if, indeed, you do think so). > But does it cause the "insert" statement to disappear without error? I don't have a good answer to this. There should be an error, but I'd need to see the code and all sorts of other things to know what is going on. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

↪ replying to Jonathan Leffler

Guo, Denny — Tue, 27 Jun 2006 13:50:35 — source: IIUG Forums & Mailing Lists

Hi Jonathan, Many thanks to your kindly help. Best Regards, Denny ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques .