Re[2]: Stored procedure taking too long.

This is a multipart message in MIME format --------------InterScan_NT_MIME_Boundary Content-Type: text/plain; charset=ISO-8859-1 Content-transfer-encoding: quoted-printable Whoops, forgot to mention some things=2E We're running Online 7=2E23=2E= UC6=20 on AIX 4=2E2=2E And to answer the second question, we do NOT mean is=20= not=20 null=2E =20 Davin ______________________________ Reply Separator ____________________________= _____ Subject: Re: Stored procedure taking too long=2E Author: MIME {mdstock@informix=2Ecom} at INTERNET Date: 11/05/1998 11:39 davin_harvey@dsti=2Eco=2Euk wrote: >=20 > Hi people, >=20 > We have a stored procedure that takes 80 mins to complete=2E If we=20 > extract the sql from this procedure, it takes about 1 second=2E The > procedure WAS using parameters, but I've replaced these temporarily > with hard-coded values, to remove this as a potential problem for th= e=20 > optimizer=2E >=20 > The statistics for both the procedure and the sql are identical, so=20 > why does the procedure take this long? >=20 > SQL is >=20 > SELECT > pr_secoption=2Eseo_secid, > pr_secoption=2Eseo_deliverydate,=20 > pr_secmast=2Esmctryissue > FROM > pr_secmast,pr_secoption=20 > WHERE > (pr_secoption=2Eseo_secid =3D pr_secmast=2Esmsecid AND=20 > pr_secoption=2Eseo_deliverydate !=3D '' AND > pr_secoption=2Eseo_deliverydate <=3D "1997-3-6 00:00:00=2E= 000"=20 > AND NOT EXISTS ( > select casequence from pr_cmact, pr_cevent=20 > where > pr_secoption=2Eseo_secid =3D pr_cevent=2Ece_smsecid and > pr_cevent=2Ece_exdivdate =3D pr_secoption=2Eseo_deliverydate= and=20 > pr_cevent=2Ece_seq =3D pr_cmact=2Eca_ce_seq and > pr_cmact=2Ecaactioncode =3D "LAPSE"))=20>=20 > Any pointers? =20 Well, a little less detail would help=2E Like what version, engine,=20 platform, etc=2E =20 It is a known fact that SPL is not very quick in version 5=2Ex=2E =20 Also, when you say: =20 pr_secoption=2Eseo_deliverydate !=3D '' =20 Don't you mean: =20 pr_secoption=2Eseo_deliverydate IS NOT NULL=20 ? =20 Cheers, --=20 Mark=2E =20 +----------------------------------------------------------+-----------+=20 |Mark D=2E Stock - Informix SA http://www=2Einformix=2Ecom |////////=20= /|=20 |mailto:mdstock@informix=2Ecom FAQ http://www=2Eiiug=2Eorg |///// /=20= //|=20 | +-----------------------------------+//// / ///|=20 | Tel: +27 11 807 0313 |If it's slow, the users complain=2E |/// / ////|=20 | Fax: +27 11 807 2594 |If it's fast, the users keep quiet=2E|// / /////|=20 |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////|=20 +----------------------+-----------------------------------+-----------+ =20 ********************************************************************** DST International Tel:+44 181 390 5000 www=2Edstinternational=2Eco=2Eu= k=20 This message and any attachments to it is intended only for the=20 individual or company to which it is addressed and may contain=20 information which is privileged, confidential or prohibited from=20 disclosure or unauthorised use=2E If the recipient of this=20 transmission is not the intended recipient, or the employee or agent=20 responsible for delivering such materials to the intended recipient,=20 you are hereby notified that any use, any form of reproduction,=20 dissemination, copying, disclosure, modification, distribution and/or=20 publication of this e-mail message or its attachments other than by=20 its intended recipient is strictly prohibited by the sender=2E =20 If you have received it in error, please notify us immediately by=20 telephone on the number above and destroy the message and all copies=20 in your possession=2E ********************************************************************** --------------InterScan_NT_MIME_Boundary--