CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Hitesh Joshi
Hitesh Joshi

Reputation: 85

Optimizing a simple procedure

Below is my procedure, takes 51 sec to execute, I want to return cursor only if one count is found, in case anything else will return message and cursor as null. In case cursor is found message as null..

I am first taking count by query and populating data by the same query later but only in case of count being one.

is their anyway in which this could be optimized in terms of time.?

create or replace PROCEDURE sp_cp_getcrnnofrmmobdob(P_MobileNo    IN VARCHAR2,
                                                    P_Dob         IN VARCHAR2,
                                                    p_Output      out SYS_REFCURSOR,
                                                    p_Message     OUT VARCHAR2) IS

  vCRN      Varchar2(50) := '';
  vCustid   varchar2(50) := '';
  vMobno    varchar2(50) := '';
  vCustname varchar2(400) := '';
  vCustDob  varchar2(50) := '';
  vcount    int := 0;

BEGIN
  p_Message := '';

  OPEN p_Output FOR
    select 1 from dual;

  Select count(*)
    into vcount
    FROM (select distinct(C.fw_customer_id_c) crn,
                C.Cust_Id_n custid,
                c.customername custname,
                c.dob custdob,
                A.MOBILE mobileno
                from      FCH_CASTRANSACTION.NBFC_CUSTOMER_M C,
                                                FCH_CASMASTER.nbfc_address_m A
                where   A.BPID = C.Cust_Id_n and
                                                A.mobile = P_MobileNo and
                                                TO_CHAR(TO_DATE(C.DOB, 'DD-MON-YY'),'DD-MON-YY')=TO_CHAR(TO_DATE(P_Dob,'DD/MM/YYYY'),'DD-MON-YY'));

  if (vcount = 1) then

    select B.crn,
           B.custid,
           B.mobileno,
           B.custname,
           B.custdob
      into vCRN, vCustid, vMobno, vCustname, vCustDob
      from (select distinct(C.fw_customer_id_c) crn,
                                                C.Cust_Id_n custid,
                                                c.customername custname,
                                                c.dob custdob,
                                                A.MOBILE mobileno
                                                from      FCH_CASTRANSACTION.NBFC_CUSTOMER_M C,
                                                          FCH_CASMASTER.nbfc_address_m A
                                                where   A.BPID = C.Cust_Id_n and
                                                        A.mobile = P_MobileNo and
                                                        TO_CHAR(TO_DATE(C.DOB, 'DD-MON-YY'),'DD-MON-YY')=TO_CHAR(TO_DATE(P_Dob,'DD/MM/YYYY'),'DD-MON-YY')) B;

    if ((vCRN = '') OR (vCRN IS Null)) then
      p_Message := 'No data found for entered details';

    else
      if ((vMobno <> P_MobileNo) OR (vMobno IS Null)) then
        p_Message := 'Entered mobile number is not registered with us.Please contact customer care.';

      else
        if ((vCustDob <> TO_CHAR(TO_DATE(P_Dob,'DD/MM/YYYY'),'DD-MON-YY')) OR (vCustDob IS Null)) then
          p_Message := 'Entered date of birth is not registered with us.Please contact customer care.';

        else
          OPEN p_Output FOR
            select vCRN as "CrnNum", vCustid as "CustId", vMobno as "MobNo", vCustname as "CustName", vCustDob as "CustDob"
              from dual;
        End if;
      End if;
    End if;
  else
    p_Message := 'Inconsistent details for entered data found. Please contact customer care';
  End if;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    p_Message := 'Unable to process your request.Please contact customer care.';
    OPEN p_Output FOR
      SELECT 1 FROM dual;
END;

Would really appreciate if someone can help.

Upvotes: 0

Views: 38

Answers (2)

unleashed
unleashed

Reputation: 771

Looking at TO_CHAR(TO_DATE(C.DOB, 'DD-MON-YY'),'DD-MON-YY')=TO_CHAR(TO_DATE(P_Dob,'DD/MM/YYYY'),'DD-MON-YY')) in both of your queries, I suggest.

  1. Based on this logic, you are storing the date of birth (dob) as a string. It is a shame if you do, it should be in the database as a DATE.
  2. You are converting the strings to a date and then back to a string. In Oracle, you can compare dates, so only convert from column to DATE, not again back to a string. Such as TO_DATE(column,'column format')=TO_DATE(variable,'variable format')
  3. Or, better yet, for your data model, consider just converting the variable input date string to match the column string format. Like column = TO_CHAR(TO_DATE(variable, 'variable format'),'column format'). There are two possible advantages here. First, the conversion will only happen once for the supplied value, but the query never has to perform a function on the column value. If the table is big. Also, since there is no function to be performed on the column value, IF there is an index on this value, the optimizer can use it (although this may not help your example based on my guess on your data model). Performance improvement here would depend on how the dob is used by your query. If Oracle is lookup up records by mobile number, then filter by dob, it shouldn't make much difference, but if it goes the other way around, looking up by dob then filtering on mobile number, this could help immensely.

tl;dr Store dates as dates, compare dates as dates, avoid functions on the column values when possible

Upvotes: 0

MT0
MT0

Reputation: 167867

  • You can just use one SELECT ... INTO ... and catch the exception TOO_MANY_ROWS.
  • '' and NULL are the same thing.
  • It will not return a row if the mobile number does not match (or is null) so that check is redundant.
  • Same for the date of birth check.
  • DISTINCT is NOT a function - it is a keyword that applies to all the rows.
  • You assigning a cursor to p_output twice. Also, some systems may not like that the function can return different numbers of columns to your cursor.

So, something like this:

create or replace PROCEDURE sp_cp_getcrnnofrmmobdob(
  P_MobileNo    IN VARCHAR2,
  P_Dob         IN VARCHAR2,
  p_Output      out SYS_REFCURSOR,
  p_Message     OUT VARCHAR2
)
IS
  v_dob     DATE := TO_DATE( p_dob, 'DD/MM/YYYY' );
  vCRN      FCH_CASTRANSACTION.NBFC_CUSTOMER_M.fw_customer_id_c%TYPE;
  vCustid   FCH_CASTRANSACTION.NBFC_CUSTOMER_M.Cust_Id_n%TYPE;
  vMobno    FCH_CASMASTER.nbfc_address_m.MOBILE%TYPE;
  vCustname FCH_CASTRANSACTION.NBFC_CUSTOMER_M.customername%TYPE;
  vCustDob  FCH_CASTRANSACTION.NBFC_CUSTOMER_M.dob%TYPE;
BEGIN
  p_Message := '';

  select distinct
         C.fw_customer_id_c,
         C.Cust_Id_n,
         c.customername,
         c.dob,
         A.MOBILE
  into   vCRN, vCustid, vMobno, vCustname, vCustDob
  from   FCH_CASTRANSACTION.NBFC_CUSTOMER_M C
         INNER JOIN FCH_CASMASTER.nbfc_address_m A
         ON ( A.BPID = C.Cust_Id_n )
  WHERE  A.mobile = P_MobileNo
  AND    TO_DATE( C.DOB, 'DD-MON-YY') = v_dob;

  IF vCRN IS NULL THEN
    p_Message := 'No data found for entered details';
    OPEN p_Output FOR
      select 1 from dual;
    RETURN;
  END IF;

  OPEN p_Output FOR
    select vCRN as "CrnNum", vCustid as "CustId", vMobno as "MobNo", vCustname as "CustName", vCustDob as "CustDob"
    from dual;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    p_Message := 'Unable to process your request.Please contact customer care.';
    OPEN p_Output FOR
      SELECT 1 FROM dual;
  WHEN TOO_MANY_ROWS THEN
    p_Message := 'Inconsistent details for entered data found. Please contact customer care';
    OPEN p_Output FOR
      SELECT 1 FROM dual;
END;

Upvotes: 1

Related Questions