| Subject | What is the best way to re-write this Stored Procedure that seems to be SLOW processing? |
|---|---|
| Author | stwizard |
| Post date | 2015-08-13T13:19:35Z |
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 PK
AREA_CODE CHAR(3) COLLATE NONE
PHONE_NO CHAR(8) NOT NULL COLLATE NONE
PER_PHONE:
PERSON_ID INTEGER NOT NULL PK
PHONE_ID INTEGER NOT NULL PK
CREATE_DATE TIMESTAMP NOT NULL DEFAULT 'NOW'
STATUS_CODE CHAR(1) NOT NULL COLLATE NONE
DEFAULT_PHONE SMALL_INT NOT NULL DEFAULT 0
LOCATION 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
END
end