Re[3]: Stored procedure taking too long.
Davin,
Drop the procedure and recreate it with set explain on as the first
line. This will give you the query execution plan. The plan used for
your extracted SQL can be different to that used in the procedure
because in the procedure you use variables.
Regards
David Ashby
______________________________ Reply Separator _________________________________
Subject: Re[2]: Stored procedure taking too long.
Author: <davin_harvey@dsti.co.uk> at WCA-INET
Date: 11/5/98 12:29 PM
Whoops, forgot to mention some things. We're running Online 7.23.UC6
on AIX 4.2. And to answer the second question, we do NOT mean is not
null.
Davin
______________________________ Reply Separator _________________________________
Subject: Re: Stored procedure taking too long.
Author: MIME {mdstock@informix.com} at INTERNET
Date: 11/05/1998 11:39
davin_harvey@dsti.co.uk wrote:
>
> Hi people,
>
> We have a stored procedure that takes 80 mins to complete. If we
> extract the sql from this procedure, it takes about 1 second. The
> procedure WAS using parameters, but I've replaced these temporarily
> with hard-coded values, to remove this as a potential problem for the
> optimizer.
>
> The statistics for both the procedure and the sql are identical, so
> why does the procedure take this long?
>
> SQL is
>
> SELECT
> pr_secoption.seo_secid,
> pr_secoption.seo_deliverydate,
> pr_secmast.smctryissue
> FROM
> pr_secmast,pr_secoption
> WHERE
> (pr_secoption.seo_secid = pr_secmast.smsecid AND
> pr_secoption.seo_deliverydate != '' AND
> pr_secoption.seo_deliverydate <= "1997-3-6 00:00:00.000"
> AND NOT EXISTS (
> select casequence from pr_cmact, pr_cevent
> where
> pr_secoption.seo_secid = pr_cevent.ce_smsecid and
> pr_cevent.ce_exdivdate = pr_secoption.seo_deliverydate and
> pr_cevent.ce_seq = pr_cmact.ca_ce_seq and
> pr_cmact.caactioncode = "LAPSE"))>
> Any pointers?
Well, a little less detail would help. Like what version, engine,
platform, etc.
It is a known fact that SPL is not very quick in version 5.x.
Also, when you say:
pr_secoption.seo_deliverydate != ''
Don't you mean:
pr_secoption.seo_deliverydate IS NOT NULL
?
Cheers,
--
Mark.
+----------------------------------------------------------+-----------+
|Mark D. Stock - Informix SA http://www.informix.com |//////// /|
|mailto:mdstock@informix.com FAQ http://www.iiug.org |///// / //|
| +-----------------------------------+//// / ///|
| Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////|
| Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////|
|Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////|
+----------------------+-----------------------------------+-----------+
**********************************************************************
DST International Tel:+44 181 390 5000 www.dstinternational.co.uk
This message and any attachments to it is intended only for the
individual or company to which it is addressed and may contain
information which is privileged, confidential or prohibited from
disclosure or unauthorised use. If the recipient of this
transmission is not the intended recipient, or the employee or agent
responsible for delivering such materials to the intended recipient,
you are hereby notified that any use, any form of reproduction,
dissemination, copying, disclosure, modification, distribution and/or
publication of this e-mail message or its attachments other than by
its intended recipient is strictly prohibited by the sender.
If you have received it in error, please notify us immediately by
telephone on the number above and destroy the message and all copies
in your possession.
**********************************************************************