asked on

how to run the stored procedure in batch mode or in run it in parallel processing

We are iterating 100k+ records from global temporary table.below stored procedure will iterate all records from glogal temp table one by one and has to process below 3-steps.

1)to see whether product is exists or not 2)to see whether product inside the assets are having the 'category' or not. 3)to see whether the assets are having file names starts with '%pdf%' or not.

So each record has to prcoess these 3-steps and final document names will be stored in the table for the successful record.if any error comes in any of the steps then error message will be stored for that record.

Below stored procedure is taking long time to process Because its processing sequentially.

1)Is there any way to make this process faster in the stored procedure itself by doing batch process? 2)If its not possible in stored procedure then Can we change this code into Java and run this code in multi threaded mode like creating 10 threads and each thread will take 1 record concurrently and process this code.I would be happy if somebody gis some pseudo code.

which approach is going to suggest?

DECLARE
V_NODE_ID  VARCHAR2(20);
V_FILENAME VARCHAR2(100);
V_CATEGORY_COUNT INTEGER :=0;  
FINAL_FILNAME VARCHAR2(2000);
V_FINAL_ERRORMESSAGE VARCHAR2(2000);


CURSOR C1 IS
SELECT isbn FROM GT_ADD_ISBNS GT;

CURSOR C2(v_isbn in varchar2) IS
SELECT ANP.NODE_ID NODE_ID
        FROM 
        table1 ANP,
        table2 ANPP,
        table3 AN
        WHERE 
      ANP.NODE_ID=AN.ID AND
    ANPP.NODE_ID=ANP.NODE_ID AND
    AN.NAME_ID =26 AND
    ANP.CATEORGY='category' AND
    ANP.QNAME_ID='categories'  AND
        ANP.NODE_ID IN(SELECT CHILD_NODE_ID 
                  FROM TABLE_ASSOC START WITH PARENT_NODE_ID IN(v_isbn) 
                      CONNECT BY PRIOR CHILD_NODE_ID = PARENT_NODE_ID);


BEGIN
--Iterating all Products
FOR R1 IN C1 
LOOP

FINAL_FILNAME :='';
BEGIN


--To check whether Product is exists or not
SELECT AN.ID INTO V_NODE_ID 
FROM TABLE1 AN,
TABLE2 ANP
WHERE
AN.ID=ANP.NODE_ID AND
ANP.VALUE in(R1.ISBN);


V_CATEGORY_COUNT :=0;
V_FINAL_ERRORMESSAGE :='';

--To check Whether Product inside the assets are having the 'category' is applied or not
FOR R2 IN C2(R1.ISBN) 
LOOP

V_CATEGORY_COUNT := V_CATEGORY_COUNT+1;  

BEGIN
--In this Logic Product inside the assets have applied the 'category' But those assets are having documents LIKE '%pdf%' or not
SELECT ANP.STRING_VALUE  into V_FILENAME
        FROM 
        table1 ANP,
        table2 ANPP,
        table3 ACD
        WHERE 
       ANP.QNAME_ID=21  AND 
       ACD.ID=ANPP.LONG_VALUE 
       ANP.NODE_ID=ANPP.NODE_ID AND
       ANPP.QNAME_ID=36 AND
       ANP.STRING_VALUE LIKE '%pdf%'  AND 
       ANP.NODE_ID=R2.NODE_ID; 

    FINAL_FILNAME := FINAL_FILNAME  || V_FILENAME ||',';

   EXCEPTION WHEN
     NO_DATA_FOUND THEN
     V_FINAL_ERRORMESSAGE:=V_FINAL_ERRORMESSAGE|| 'Category is applied for this Product But for the asset:'||  R2.NODE_ID || ':Documents[LIKE %pdf%] were not found ;';
     UPDATE GT_ADD_ISBNS SET ERROR_MESSAGE=  V_FINAL_ERRORMESSAGE  WHERE ISBN= R1.ISBN;


     END;--Iterating for each NODEID

END LOOP;--Iterating the assets[Nodes] for each product of catgeory

  --  DBMS_OUTPUT.PUT_LINE('R1.ISBN:' || R1.ISBN ||'::V_CATEGORY_COUNT:' || V_CATEGORY_COUNT);

 IF(V_CATEGORY_COUNT  = 0) THEN
     UPDATE GT_ADD_ISBNS SET ERROR_MESSAGE=  'Category is not applied to none of the Assets for this Product'  WHERE ISBN= R1.ISBN;
   END IF;  


EXCEPTION WHEN
NO_DATA_FOUND THEN
      UPDATE GT_ADD_ISBNS SET ERROR_MESSAGE=   'Product is not Found:' WHERE ISBN= R1.ISBN;
END;

  -- DBMS_OUTPUT.PUT_LINE( R1.ISBN || 'Final documents:'||FINAL_FILNAME);
      UPDATE GT_ADD_ISBNS SET FILENAME=FINAL_FILNAME WHERE ISBN= R1.ISBN;

COMMIT;
END LOOP;--looping gt_isbns
END;

Open in new window

Geert G

i'll give it a pseudo attempt

dunno, if you have access to livesql.oracle.com ?
https://livesql.oracle.com/apex/livesql/s/gav3j57wi5x7yptfm74eebe9e

I created the tables based on the code.

you have a qname_id which you use with a text 'categories' and an integer 26
very odd

it would be nice to upload a representative sample of your data to work with

chaituu

ASKER

I dont have oracle login.I think only access to those guys who are working in oracle.I will try to give sample data to all the tables.If possible can you paste the code here..

Geert G

create table statements:

create table gt_add_isbns ( 
  isbn varchar2(50 char) , 
  error_message varchar2(500 char), 
  filename varchar2(256 char) 
  );

 create table table1 ( 
  node_id integer, 
  category varchar2(50 char), 
  qname_id varchar2(50 char), 
  string_value varchar2(50 char) 
  );

 create table table2 ( 
  node_id integer, 
  value varchar2(50 char), 
  qname_id varchar2(50 char), 
  long_value integer 
  );

 create table table3 ( 
  id integer, 
  name_id integer 
  );

 create table table_assoc ( 
  child_node_id integer, 
  parent_node_id integer);

Open in new window

slightwv (䄆 Netminder)

All but the first query seem to access the same tables over and over. I would look at trying to access the tables once with a single query. I don't know your tables, data or requirements so I doubt I'll be able to provide a copy/paste solution.

If you can create a really simple test case that shows what you are tying to accomplish we might be able to help with a runnable test case.

>>I dont have oracle login.I think only access to those guys who are working in oracle.

How are you expected to provide working code if you don't have access to a system to test on?

Geert G

>>I dont have oracle login.I think only access to those guys who are working in oracle.

Might be better to let those guys ask the questions ?

chaituu

ASKER

Please check the DDL's and the respective tables sample data.

there are some other extra columns are there in TEMP_NODE_PROPERTIES but it is not required here to get the final put.

if you run the GT_ADD_ISBNS table then the final output will be shown against each record.

created the account in livesql.oracle but couldnot login due to unknown errors from that server itself.so pasted all the tables and the sample data.

<code>
SET SERVEROUTPUT ON;
DECLARE
V_NODE_ID VARCHAR2(20);
V_FILENAME VARCHAR2(100);
V_CATEGORY_COUNT INTEGER :=0;
FINAL_FILNAME VARCHAR2(2000);
V_ERRORMESSAGE VARCHAR2(2000);
V_FINAL_ERRORMESSAGE VARCHAR2(2000);

CURSOR C1 IS
SELECT isbn FROM GT_ADD_ISBNS GT;

CURSOR C2(v_isbn in varchar2) IS
SELECT AN.ID NODE_ID
            FROM
            TEMP_NODE_PROPERTIES ANP,
            TEMP_NODE_PROPERTIES ANPP,
            TEMP_NODE AN
            WHERE
       ANP.NODE_ID=AN.ID AND
ANPP.NODE_ID=ANP.NODE_ID AND
AN.TYPE_QNAME ='asset' AND
(ANP.STRING_VALUE ='uPDF' AND
ANPP.STRING_VALUE ='Entire PDF') AND
ANP.TYPE_QNAME='categories' AND
           ANP.NODE_ID IN(SELECT CHILD_ID
FROM TEMP_CHILD_ASSOC START WITH PARENT_ID IN(
SELECT CHILD_ID FROM TEMP_CHILD_ASSOC WHERE NAME in(v_isbn))
CONNECT BY PRIOR CHILD_ID = PARENT_ID);

BEGIN
--Iterating all Products
FOR R1 IN C1
LOOP

FINAL_FILNAME :='';
BEGIN

--To check whether Product is exists or not
SELECT AN.ID INTO V_NODE_ID
FROM TEMP_NODE AN,
       TEMP_NODE_PROPERTIES ANP
WHERE
AN.ID=ANP.NODE_ID AND
ANP.TYPE_QNAME = 'product' AND
ANP.STRING_VALUE in(R1.ISBN);

V_CATEGORY_COUNT :=0;
V_FINAL_ERRORMESSAGE :='';

--To check Whether Product inside the assets are having the 'category' is applied or not
FOR R2 IN C2(R1.ISBN)
LOOP

V_CATEGORY_COUNT := V_CATEGORY_COUNT+1;

BEGIN
--In this Logic Product inside the assets have applied the 'category' But those assets are having documents LIKE '%pdf%' or not
SELECT ANP.STRING_VALUE into V_FILENAME
            FROM
            TEMP_NODE_PROPERTIES ANP
            WHERE
ANP.TYPE_QNAME='name' AND
((
ANP.STRING_VALUE LIKE '%pdf%' AND
ANP.STRING_VALUE NOT LIKE '%pod%')
) AND
ANP.NODE_ID=R2.NODE_ID ;

FINAL_FILNAME := FINAL_FILNAME || V_FILENAME ||',';

DBMS_OUTPUT.PUT_LINE('R1.ISBN:'|| R1.ISBN || ':R2.NODE_ID:' || R2.NODE_ID || ':FILENAME:'|| FINAL_FILNAME);

EXCEPTION WHEN
NO_DATA_FOUND THEN
V_FINAL_ERRORMESSAGE:=V_FINAL_ERRORMESSAGE|| 'Category is applied for this Product But for the asset:'|| R2.NODE_ID || ':Documents[LIKE %pdf% AND LIKE %pod% ] were not found ;';
UPDATE GT_ADD_ISBNS SET CATEGORY11_ERROR_MESSAGE= V_FINAL_ERRORMESSAGE WHERE ISBN= R1.ISBN;

END;--Iterating for each NODEID

END LOOP;--Iterating the assets[Nodes] for each product of catgeory

DBMS_OUTPUT.PUT_LINE('R1.ISBN:' || R1.ISBN ||'::V_CATEGORY_COUNT:' || V_CATEGORY_COUNT);

IF(V_CATEGORY_COUNT = 0) THEN
UPDATE GT_ADD_ISBNS SET CATEGORY11_ERROR_MESSAGE= 'Category is not applied to none of the Assets for this Product' WHERE ISBN= R1.ISBN;
END IF;

EXCEPTION WHEN
NO_DATA_FOUND THEN
UPDATE GT_ADD_ISBNS SET CATEGORY11_ERROR_MESSAGE= 'Product is not Found:' WHERE ISBN= R1.ISBN;
END;

DBMS_OUTPUT.PUT_LINE( R1.ISBN || '::Final documents:'||REGEXP_REPLACE((FINAL_FILNAME), ',$', ''));
UPDATE GT_ADD_ISBNS SET CATEGORY11_FILENAME=FINAL_FILNAME WHERE ISBN= R1.ISBN;

COMMIT;
END LOOP;--looping gt_isbns
END;
</code>

<code>

--------------------------------------------------------
-- File created - Wednesday-February-21-2018
--------------------------------------------------------
--------------------------------------------------------
-- DDL for Table TEMP_CHILD_ASSOC
--------------------------------------------------------

CREATE TABLE "TEMP_CHILD_ASSOC" ("PARENT_ID" VARCHAR2(20), "CHILD_ID" VARCHAR2(20), "NAME" VARCHAR2(2000))
REM INSERTING into TEMP_CHILD_ASSOC
SET DEFINE OFF;
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1141','1143','9780748700585');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1143','1202','testfolder1');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1143','1203','testfolder2');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1341','1343','9780748700587');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1343','1344','folder');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1202','1223','9781424005468_p01_covpdf.zip');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1202','1226','9781424005468_p01_hipdf.zip');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1202','1229','9781424005468_p01_pod.zip');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1202','1232','9781424005468_p01_pod_chaitu.zip');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1344','1345','x.zip');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1344','1346','y.zip');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1441','1443','9780748700588');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1241','1243','9780748700586');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1243','1244','testfolder');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1244','1245','john_testpdf.zip');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1244','1246','stab_pdf.sit');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1443','1444','9781424005468_p01_hipdf.zip');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1541','1543','9780748700589');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1543','1544','xyz.zip');

</code>

<code>
--------------------------------------------------------
-- File created - Wednesday-February-21-2018
--------------------------------------------------------
--------------------------------------------------------
-- DDL for Table TEMP_NODE
--------------------------------------------------------

CREATE TABLE "TEMP_NODE" ("ID" VARCHAR2(20), "TYPE_QNAME" VARCHAR2(20))
REM INSERTING into TEMP_NODE
SET DEFINE OFF;
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1143','product');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1202','folder');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1203','folder');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1226','asset');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1232','asset');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1229','asset');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1244','folder');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1223','asset');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1243','product');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1245','asset');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1246','asset');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1343','product');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1344','folder');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1345','asset');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1346','asset');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1443','product');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1444','asset');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1543','product');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1544','asset');
</code>

<code>
--------------------------------------------------------
-- File created - Wednesday-February-21-2018
--------------------------------------------------------
--------------------------------------------------------
-- DDL for Table TEMP_NODE_PROPERTIES
--------------------------------------------------------

CREATE TABLE "TEMP_NODE_PROPERTIES" ("NODE_ID" VARCHAR2(20), "TYPE_QNAME" VARCHAR2(20), "STRING_VALUE" VARCHAR2(300))
REM INSERTING into TEMP_NODE_PROPERTIES
SET DEFINE OFF;
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1143','product','9780748700585');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1223','categories','uPDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1223','categories','Entire PDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1223','name','9781424005468_p01_hipdf.zip');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1226','name','9781424005468_p01_covpdf.zip');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1226','categories','Entire PDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1226','categories','uPDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1243','product','9780748700586');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1244','folder',null);
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1245','categories','uPDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1245','categories','Entire PDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1246','categories','uPDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1246','categories','Entire PDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1245','name','john_testpdf.zip');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1246','name','stab_pdf.sit');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1343','product','9780748700587');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1344','folder',null);
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1345','categories','uPDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1345','categories','Entire PDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1345','name','x.zip');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1346','categories','uPDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1346','categories','Entire PDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1346','name','y.zip');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1443','product','9780748700588');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1444','name','9781424005468_p01_hipdf.zip');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1543','product','9780748700589');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1544','name','xyz.zip');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1544','categories','Image');

</code>

<code>
--------------------------------------------------------
-- File created - Wednesday-February-21-2018
--------------------------------------------------------
--------------------------------------------------------
-- DDL for Table GT_ADD_ISBNS
--------------------------------------------------------

CREATE TABLE "GT_ADD_ISBNS" ("ISBN" VARCHAR2(15), "CATEGORY11_FILENAME" VARCHAR2(2000), "CATEGORY11_ERROR_MESSAGE" VARCHAR2(2000))
REM INSERTING into GT_ADD_ISBNS
SET DEFINE OFF;
Insert into GT_ADD_ISBNS (ISBN,CATEGORY11_FILENAME,CATEGORY11_ERROR_MESSAGE) values ('9780748700585','9781424005468_p01_covpdf.zip,9781424005468_p01_hipdf.zip,',null);
Insert into GT_ADD_ISBNS (ISBN,CATEGORY11_FILENAME,CATEGORY11_ERROR_MESSAGE) values ('9780748700586','stab_pdf.sit,john_testpdf.zip,',null);
Insert into GT_ADD_ISBNS (ISBN,CATEGORY11_FILENAME,CATEGORY11_ERROR_MESSAGE) values ('9780748700587',null,'Category is applied for this Product But for the asset:1346:Documents[LIKE %pdf% AND LIKE %pod% ] were not found ;Category is applied for this Product But for the asset:1345:Documents[LIKE %pdf% AND LIKE %pod% ] were not found ;');
Insert into GT_ADD_ISBNS (ISBN,CATEGORY11_FILENAME,CATEGORY11_ERROR_MESSAGE) values ('9780748700588',null,'Category is not applied to none of the Assets for this Product');
Insert into GT_ADD_ISBNS (ISBN,CATEGORY11_FILENAME,CATEGORY11_ERROR_MESSAGE) values ('9780748700589',null,'Category is not applied to none of the Assets for this Product');
</code>

ASKER CERTIFIED SOLUTION

chaituu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Geert G

sorry, too busy, yer still processing item per item

chaituu

ASKER

nobody has not given best solution.

Geert G

the best solution is by someone with access to your system
without access to the system, nothing can be verified

we try to help you by giving advice, but in the end it's you who has to do it
tuning requires lots of trials/attempts and verifications

we hire a tuning expert who comes in 1 day per week
not only do the databases get tuned, but devs get educated too
it's a process without end