Home » SQL & PL/SQL » SQL & PL/SQL » Very time consuming query or procedure (Oracle 9i(R1), SCO UNIX)
icon8.gif  Very time consuming query or procedure [message #280397] Tue, 13 November 2007 07:09 Go to next message
somen
Messages: 9
Registered: May 2007
Location: KOLKATA
Junior Member
Dear friends,
I am in a problem to find out the number of waybills which are endorsed and issued between '01-NOV-05' and '31-OCT-06'. Each waybill is identified by any of 'ENDRS.WBILL_SNO', 'FNTN.FROM_NO' and 'FNTN.TO_NO' fields.
If there is a record in the 'ENDRS' table then that waybill is considered as endorsed.
Issue date can be checked by the 'FNTN.ISSUE_DT' field.

Two tables are to be considered as follows:
SQL> DESC FNTN;
 Name                            Null?    Type
 ------------------------------- -------- ----
 FROM_NO                         NOT NULL NUMBER(11)
 TO_NO                           NOT NULL NUMBER(11)
 ISSUE_DT                        NOT NULL DATE

SQL> SELECT COUNT(*) FROM FNTN;

 COUNT(*)
---------
    78197

SQL> DESC ENDRS;
 Name                            Null?    Type
 ------------------------------- -------- ----
 WBILL_SNO                       NOT NULL NUMBER(15)

SQL> SELECT COUNT(*) FROM ENDRS;

 COUNT(*)
---------
  8758694


I tried this problem as:

1)
SELECT COUNT(*)
FROM   (SELECT from_no
        FROM   (SELECT TRIM(DISTINCT a.wbill_sno)
                FROM   endorsement a, m_issue_reg b
                WHERE  LTRIM(a.wbill_sno) = b.from_no
                OR     LTRIM(RTRIM(a.wbill_sno)) = b.to_no)
        UNION
        SELECT to_no
        FROM   (SELECT LTRIM(RTRIM(a.wbill_sno))
                FROM   endorsement a, m_issue_reg b
                WHERE  LTRIM(RTRIM(a.wbill_sno)) = b.to_no
                OR     LTRIM(RTRIM(a.wbill_sno)) = b.to_no));

This query had a problem as it excludes the 'ENDRS.WBILL_SNO's for which there is no entry in either of the 'FNTN.FROM_NO' and 'FNTN.TO_NO' fields. Suppose a row in 'FNTN' has '0071', '0081' ,'12-AUG-06' as the value of the 'FNTN.FROM_NO','FNTN.TO_NO' and 'FNTN.ISSUE_DT' fields-- which yields the fact that there are waybills with nos. '0072'-'0080'. But if 'ENDRS.WBILL_SNO' has a value '0073' then this will not be considered in this case.

2)
CREATE PROCEDURE PP131107(P OUT NUMBER)
AS
   CURSOR C1
   IS
      SELECT TRIM(WBILL_SNO) SL
      FROM   ENDRS;

   CURSOR C2
   IS
      SELECT FROM_NO
           , TO_NO
      FROM   FNTN;

   CNT   NUMBER DEFAULT 0;
BEGIN
   FOR V1 IN C1
   LOOP
      FOR V2 IN C2
      LOOP
         FOR R IN V2.FROM_NO .. V2.TO_NO
         LOOP                                       --to check the intermediate
            IF (V1.SL <> R)
            THEN
               CNT    := CNT + 1;
            END IF;
         END LOOP;
      END LOOP;
   END LOOP;

   P    := CNT;
END PP131107;


After this, at the Sql prompt I executed this. But within 5 hrs, I could not
get the result.

Additional information:

Database: Oracle9i(R1).
OS: SCO Unix

I'll be highly obliged if any one can help me.

With thanks and regards,
Somen Choudhury.

[Updated on: Tue, 13 November 2007 07:11] by Moderator

Report message to a moderator

Re: Very time consuming query or procedure [message #280401 is a reply to message #280397] Tue, 13 November 2007 07:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) If you have performances problems then start to remove stupid thing just like rtrim/ltrim on numbers.

2) What is the purpose of this procedure? Do you want us to reverse engineer your code?

Also read How to Identify Performance Problem and Bottleneck .

Regards
Michel
Re: Very time consuming query or procedure [message #280490 is a reply to message #280401] Tue, 13 November 2007 19:58 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Start with this query:
SELECT b.issue_dt, a.wbill_sno
FROM   endorsement a, m_issue_reg b
WHERE  a.wbill_sno BETWEEN b.from_no AND b.to_no


It should give you the results you need fairly efficiently. I detect that you have dumbed-down the requirement for us (you table-defs dont match the SQLs), so add whatever other WHERE clauses you need.

Once you have the results you want, you may want to tune the SQL. This article explains some technique especially for tuning range joins.

Ross Leishman
Previous Topic: Index question
Next Topic: AUTHID CURRENT_USER
Goto Forum:
  


Current Time: Sun Dec 04 20:34:17 CST 2016

Total time taken to generate the page: 0.08000 seconds