Hi,
First show us query plan for every select from this proc. This tell us what is
wrong.
Regards,Karol Bieniaszewski
-------- Oryginalna wiadomość --------
Od: "'stwizard' stwiz...@att.net [firebird-support]"
<firebird-support@yahoogroups.com>
Data: 13.08.2015 15:19 (GMT+01:00)
Do: firebird-support@yahoogroups.com
Temat: [firebird-support] What is the best way to re-write this Stored
Procedure that seems to be SLOW processing?
Greetings All, First off, I would like to thank all members of this list
for taking the time to reply on this list. Much appreciated… Really need some
help on this one folks. Not sure if this is related to IN, NOT IN vs EXISTS,
NOT EXISTS or not. I have a main stored procedure (not this one) that currently
fetches and processes approximately 42000 records in either 6 minutes or 2
hours and 45 minutes depending on if another secondary stored procedure is
called. This main stored procedure will pull all people that need to be sent
out in an export for skip tracing purposes. I need help with the secondary
stored procedure that is painfully SLOW and includes IN and NOT IN. Currently
this is in a Firebird v1.5.3 database, but I’m currently in the process of
preparing to move to v2.5.4. First I’ll provide the basic table structures. I
will not include any fields that are not relevant here. PERSON: PERSON_ID
INTEGER NOT NULL PK PHONE:PHONE_ID
INTEGER NOT NULL PKAREA_CODE
CHAR(3) COLLATE
NONEPHONE_NO CHAR(8) NOT NULL
COLLATE NONE PER_PHONE:PERSON_ID INTEGER
NOT NULL PKPHONE_ID INTEGER NOT
NULL PKCREATE_DATE TIMESTAMP NOT NULL
DEFAULT 'NOW' STATUS_CODE CHAR(1)
NOT NULL COLLATE NONEDEFAULT_PHONE SMALL_INT NOT
NULL DEFAULT 0LOCATION VARCHAR(25)
COLLATE NONE Purpose of this secondary stored
procedure is to:1) Find a default verifying or good home phone or
alternately a non-default good or verify home phone and plug it into position
1. 2) Then find a default verifying or good work phone or alternately a
non-default good or verify work phone and plug it into position 2. 3)
After that fill in the balance of up to 10 phone numbers with good or verifying
phones in date created descending order that are not equal to iPhoneID1 or
iPhoneID2. 4) Lastly fill in the balance of up to 10 phone numbers with
non(good or verifying) phones in date created descending order that are not
equal to iPhoneID1 or iPhoneID2. First off as mentioned above if this
secondary stored procedure has to be called, the time it takes to pull 42000
records jumps from 6 minutes or 2 hours and 45 minutes. WOW!!! I experimented a
bit with this stored procedure and commented out all but pulling the first
phone number. Time jumped from 6 minutes to 36 minutes. Then I tried
commenting out all but pulling the first two phone numbers. Time jumped from 6
minutes to (Well over 78 minutes so far, as it is still running). Please note
that I can run this stored procedure in Database Workbench where I plug in a
person ID I know will return the maximum of ten phone numbers and it return
results instantly with no pause or delay at all, so this has me baffled. Here
is the store procedure. Is there a better way to do this? Thanks to all,Mike
/* Author : Michael G. Tuttle Date : 12.19.12 Purpose : Phone 1 =
Home Phone (If available) Phone 2 = Work Phone (If available)
Phone 3 - 10 = Any Good or Verifying Phones*/DECLARE VARIABLE iPhoneID1
INTEGER;DECLARE VARIABLE iPhoneID2 INTEGER;DECLARE VARIABLE sAreaCode
CHAR(3);DECLARE VARIABLE sPhoneNo CHAR(7);begin iPhoneID1 = 0; iPhoneID2 = 0;
/* Try to find a default good or verifying home 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 = 'Home' AND
PP.STATUS_CODE IN ('G','V') INTO :iPhoneID1, :AREA_CODE1, :PHONE_NO1; IF
(PHONE_NO1 IS NULL) THEN BEGIN /* If no default home phone, then try to
find a non-default good or verifying home 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 INTO :iPhoneID1, :AREA_CODE1, :PHONE_NO1; END
/* Try to find a default good or verifying work 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') INTO :iPhoneID2, :AREA_CODE2, :PHONE_NO2; IF
(PHONE_NO2 IS NULL) THEN BEGIN /* If no default work phone, then look
for a non-default good or verifying work 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 INTO :iPhoneID2, :AREA_CODE2, :PHONE_NO2; END
/* Now fill in the balance with good or verifying phones in date descending
order */ FOR 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 INTO
:sAreaCode, :sPhoneNo DO BEGIN IF (PHONE_NO1 IS NULL) THEN BEGIN
AREA_CODE1 = sAreaCode; PHONE_NO1 = sPhoneNo; END
ELSE IF (PHONE_NO2 IS NULL) THEN BEGIN AREA_CODE2 = sAreaCode;
PHONE_NO2 = sPhoneNo; END ELSE IF (PHONE_NO3 IS NULL) THEN
BEGIN AREA_CODE3 = sAreaCode; PHONE_NO3 = sPhoneNo;
END ELSE IF (PHONE_NO4 IS NULL) THEN BEGIN AREA_CODE4 =
sAreaCode; PHONE_NO4 = sPhoneNo; END ELSE IF (PHONE_NO5 IS
NULL) THEN BEGIN AREA_CODE5 = sAreaCode; PHONE_NO5 =
sPhoneNo; END ELSE IF (PHONE_NO6 IS NULL) THEN BEGIN
AREA_CODE6 = sAreaCode; PHONE_NO6 = sPhoneNo; END ELSE IF
(PHONE_NO7 IS NULL) THEN BEGIN AREA_CODE7 = sAreaCode;
PHONE_NO7 = sPhoneNo; END ELSE IF (PHONE_NO8 IS NULL) THEN
BEGIN AREA_CODE8 = sAreaCode; PHONE_NO8 = sPhoneNo;
END ELSE IF (PHONE_NO9 IS NULL) THEN BEGIN AREA_CODE9 =
sAreaCode; PHONE_NO9 = sPhoneNo; END ELSE IF (PHONE_NO10
IS NULL) THEN BEGIN AREA_CODE10 = sAreaCode;
PHONE_NO10 = sPhoneNo; END END /* Now fill in the balance with any
phone that is NOT good or verifying in date descending order */ FOR 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 INTO :sAreaCode, :sPhoneNo DO
BEGIN IF (PHONE_NO1 IS NULL) THEN BEGIN AREA_CODE1 =
sAreaCode; PHONE_NO1 = sPhoneNo; END ELSE IF (PHONE_NO2 IS
NULL) THEN BEGIN AREA_CODE2 = sAreaCode; PHONE_NO2 =
sPhoneNo; END ELSE IF (PHONE_NO3 IS NULL) THEN BEGIN
AREA_CODE3 = sAreaCode; PHONE_NO3 = sPhoneNo; END ELSE IF
(PHONE_NO4 IS NULL) THEN BEGIN AREA_CODE4 = sAreaCode;
PHONE_NO4 = sPhoneNo; END ELSE IF (PHONE_NO5 IS NULL) THEN
BEGIN AREA_CODE5 = sAreaCode; PHONE_NO5 = sPhoneNo;
END ELSE IF (PHONE_NO6 IS NULL) THEN BEGIN AREA_CODE6 =
sAreaCode; PHONE_NO6 = sPhoneNo; END ELSE IF (PHONE_NO7 IS
NULL) THEN BEGIN AREA_CODE7 = sAreaCode; PHONE_NO7 =
sPhoneNo; END ELSE IF (PHONE_NO8 IS NULL) THEN BEGIN
AREA_CODE8 = sAreaCode; PHONE_NO8 = sPhoneNo; END ELSE IF
(PHONE_NO9 IS NULL) THEN BEGIN AREA_CODE9 = sAreaCode;
PHONE_NO9 = sPhoneNo; END ELSE IF (PHONE_NO10 IS NULL) THEN
BEGIN AREA_CODE10 = sAreaCode; PHONE_NO10 = sPhoneNo;
END ENDend
