Re: Stored procedure taking too long.
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"!|/ ////////|
+----------------------+-----------------------------------+-----------+