Thanks Karol,
The following are the query plans in sequential order for the SQL statements in
the stored procedure:
SELECT P.PHONE_ID,
P.AREA_CODE,
F_STRIPSTRING(P.PHONE_NO,'-') AS PHONE_NO
FROM PER_PHONE PP
JOIN PHONE P ON P.PHONE_ID = PP.PHONE_ID
WHERE PP.PERSON_ID = :V_PERSON_ID
AND PP.DEFAULT_PHONE = 1
AND PP.LOCATION = 'Home'
AND PP.STATUS_CODE IN ('G','V')
PLAN JOIN (PP INDEX (REFPERSON15,IX_PP_STATUS_CODE2,IX_PP_STATUS_CODE2),P INDEX
(PK_PHONE))
SELECT FIRST 1
P.PHONE_ID,
P.AREA_CODE,
F_STRIPSTRING(P.PHONE_NO,'-') AS PHONE_NO
FROM PER_PHONE PP
JOIN PHONE P ON P.PHONE_ID = PP.PHONE_ID
WHERE PP.PERSON_ID = :V_PERSON_ID
AND PP.LOCATION = 'Home'
AND PP.STATUS_CODE IN ('G','V')
ORDER BY PP.STATUS_CODE
PLAN JOIN (PP ORDER IX_PP_STATUS_CODE2,P INDEX (PK_PHONE))
SELECT P.PHONE_ID,
P.AREA_CODE,
F_STRIPSTRING(P.PHONE_NO,'-') AS PHONE_NO
FROM PER_PHONE PP
JOIN PHONE P ON P.PHONE_ID = PP.PHONE_ID
WHERE PP.PERSON_ID = :V_PERSON_ID
AND PP.DEFAULT_PHONE = 1
AND PP.LOCATION = 'Work'
AND PP.STATUS_CODE IN ('G','V')
PLAN JOIN (PP INDEX (REFPERSON15,IX_PP_STATUS_CODE2,IX_PP_STATUS_CODE2),P INDEX
(PK_PHONE))
SELECT FIRST 1
P.PHONE_ID,
P.AREA_CODE,
F_STRIPSTRING(P.PHONE_NO,'-') AS PHONE_NO
FROM PER_PHONE PP
JOIN PHONE P ON P.PHONE_ID = PP.PHONE_ID
WHERE PP.PERSON_ID = :V_PERSON_ID
AND PP.LOCATION = 'Work'
AND PP.STATUS_CODE IN ('G','V')
ORDER BY PP.STATUS_CODE
PLAN JOIN (PP ORDER IX_PP_STATUS_CODE2,P INDEX (PK_PHONE))
SELECT FIRST 10 P.AREA_CODE,
F_STRIPSTRING(P.PHONE_NO,'-') AS PHONE_NO
FROM PER_PHONE PP
JOIN PHONE P ON P.PHONE_ID = PP.PHONE_ID
WHERE PP.PERSON_ID = :V_PERSON_ID
AND PP.STATUS_CODE IN ('G','V')
AND P.PHONE_ID NOT IN (:iPhoneID1, :iPhoneID2)
ORDER BY PP.CREATE_DATE DESC, PP.STATUS_CODE
PLAN SORT (JOIN (PP INDEX (REFPERSON15,IX_PP_STATUS_CODE2,IX_PP_STATUS_CODE2),P
INDEX (PK_PHONE)))
SELECT FIRST 10 P.AREA_CODE,
F_STRIPSTRING(P.PHONE_NO,'-') AS PHONE_NO
FROM PER_PHONE PP
JOIN PHONE P ON P.PHONE_ID = PP.PHONE_ID
WHERE PP.PERSON_ID = :V_PERSON_ID
AND PP.STATUS_CODE NOT IN ('G','V')
AND P.PHONE_ID NOT IN (:iPhoneID1, :iPhoneID2)
ORDER BY PP.CREATE_DATE DESC, PP.STATUS_CODE
PLAN SORT (JOIN (PP INDEX (REFPERSON15),P INDEX (PK_PHONE)))
From: [email protected]
[mailto:[email protected]]
Sent: Thursday, August 13, 2015 9:38 AM
To: [email protected]
Subject: ODP: [firebird-support] What is the best way to re-write this Stored
Procedure that seems to be SLOW processing?
Hi,
First show us query plan for every select from this proc. This tell us what is
wrong.
Regards,
Karol Bieniaszewski
