Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: Odd Stored Procedure Behavior

    I'm seeing some really messed up behavior from a stored procedure. Here is the DDL:

    CREATE PROCEDURE AIM.GetUserIDRetrievals (IN strUserID VARCHAR(64),
    IN decAIMConnectionID DECIMAL(13,0))
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    SPECIFIC AIM.GetUserIDRetrieval
    LANGUAGE SQL
    RESULT SETS 1
    P1: BEGIN
    DECLARE SELECT_CURSOR CURSOR WITH RETURN FOR
    SELECT AIM.AIMRETRIEVAL.AIMRETRIEVALID, AIM.AIMRETRIEVAL.DESCRIPTION, AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID, AIM.ARCHIVERETRIEVAL.STATUSID,
    AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE, AIM.ARCHIVERETRIEVAL.ESTIMATEDRESPONSETIME, AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALRECEIPT, AIM.ARCHIVERETRIEVAL.ITEMCOUNT,
    (SELECT AIM.ARCHIVERETRIEVAL.ITEMCOUNT - COUNT(AIM.AIMRETRIEVEDITEM.AIMRETRIEVEDITEMID) FROM AIM.AIMRETRIEVEDITEM WHERE
    (AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID = AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID) AND
    LENGTH(AIM.AIMRETRIEVEDITEM.IMAGEFRONT) IS NULL)
    AS NUMBERIMAGESAVAILABLE
    FROM
    AIM.AIMCONNECTION,
    AIM.AIMQUERY,
    AIM.AIMRETRIEVAL,
    AIM.ARCHIVERETRIEVAL,
    AIM.AIMRETRIEVEDITEM,
    AIM.ARCHIVETYPE,
    AIM.CHANNEL
    WHERE
    AIM.AIMCONNECTION.AIMCONNECTIONID = AIM.AIMQUERY.AIMCONNECTIONID
    AND AIM.AIMQUERY.AIMQUERYID = AIM.AIMRETRIEVAL.AIMQUERYID
    AND AIM.AIMRETRIEVAL.AIMRETRIEVALID = AIM.ARCHIVERETRIEVAL.AIMRETRIEVALID
    AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID = AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
    AND AIM.ARCHIVERETRIEVAL.ARCHIVEID = AIM.ARCHIVETYPE.ARCHIVEID
    AND (AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE >= DATE(CURRENT TIMESTAMP) OR (AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE IS NULL AND AIM.ARCHIVERETRIEVAL.ESTIMATEDRESPONSETIME >= CURRENT TIMESTAMP))
    AND AIM.ARCHIVETYPE.ARCHIVETYPE = 'D'
    AND AIM.AIMCONNECTION.USERID = strUserID
    AND AIM.CHANNEL.CHANNEL =
    (SELECT CHANNEL FROM AIM.CHANNEL, AIM.CHANNELSESSION, AIM.AIMCONNECTION WHERE
    AIM.CHANNELSESSION.CHANNELSESSIONID = AIM.AIMCONNECTION.CHANNELSESSIONID
    AND AIM.CHANNELSESSION.CHANNELID = AIM.CHANNEL.CHANNELID
    AND AIM.AIMCONNECTION.AIMCONNECTIONID = decAIMConnectionID)
    GROUP BY AIM.AIMRETRIEVAL.AIMRETRIEVALID, AIM.AIMRETRIEVAL.DESCRIPTION, AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID, AIM.ARCHIVERETRIEVAL.STATUSID,
    AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE, AIM.ARCHIVERETRIEVAL.ESTIMATEDRESPONSETIME, AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALRECEIPT, AIM.ARCHIVERETRIEVAL.ITEMCOUNT,
    AIM.ARCHIVERETRIEVAL.ITEMCOUNT
    ORDER BY AIMRETRIEVALID;
    OPEN SELECT_CURSOR;
    END P1

    For some reason this procedure is running absolutely dog slow!!!! If I run the SQL in the procedure ad-hoc (command line, etc.), the query completes sub-second; the procedure takes about three minutes to return the result set...

    Can anyone shed any light on what the issue may be?!? Do stored procedures run in a different memory pool or something? Does the query optimizer need some help?

    Any help would be greatly appreciated!!!

    AMR
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Have you tried Explaiming the statements dynamically and the procedure's package...
    Are they the same ?

    If you have done a RUNSTATS recently, rebind the procedure's package ...

    To know exactly where the problem is in your appl, switch on the statement and transactio event monitors and check how long this query actually takes ... It may be that other bits of your appl are taking longer and not this proc

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •