5 Replies Latest reply on Mar 30, 2020 9:30 PM by Frank Kulash

    Running a procedure 100 thousand times

    Sunny86

      Hello experts,

       

      We have a requirement to print customer details which fetches data using multiple select statements and functions..we have around 100 thousand customers and each call to procedure updates data in one second.. so for 100 thousand executions it is taking more than 15 hours. Need to improve it under 3 to 5 hours.. is there a way we can execute the procedure from multiple sessions like one set of customer in one session?.. now one procedure cursor fetches customer and call another procedure to fetch details of customer..the second procedure inserts data to a temporary table and once it fetches all required data it will update to the main table.

        • 1. Re: Running a procedure 100 thousand times
          Tubby

          Sunny86 wrote:

           

          Hello experts,

           

          We have a requirement to print customer details which fetches data using multiple select statements and functions..we have around 100 thousand customers and each call to procedure updates data in one second.. so for 100 thousand executions it is taking more than 15 hours. Need to improve it under 3 to 5 hours.. is there a way we can execute the procedure from multiple sessions like one set of customer in one session?.. now one procedure cursor fetches customer and call another procedure to fetch details of customer..the second procedure inserts data to a temporary table and once it fetches all required data it will update to the main table.

          Without seeing the code the best I can offer you is to break up the set of customers into chunks and process in parallel.

          https://oracle-base.com/articles/11g/dbms_parallel_execute_11gR2

           

          Bear in mind that this could introduce concurrency issues and WILL take longer (total time, not wall clock time).

           

          If you want to share the actual code it's very likely someone can help you tune that, but for ease of use I'd start with the parallelization and see how you go.

           

          Cheers,

          1 person found this helpful
          • 2. Re: Running a procedure 100 thousand times
            EdStevens

            Without being able to see the code but based on your vague description, it sounds like rather than looking for some 'parallel' solution, the entire process could stand to be re-architected.  (is 're-architected' a word?  How about 're-designed').

             

            now one procedure cursor fetches customer and call another procedure to fetch details of customer..the second procedure inserts data to a temporary table and once it fetches all required data it will update to the main table.

            Sounds like a painful row-by-row, slow-by-slow process, handling the same data multiple times as you fetch it, call a procedure per row, write it to a table, then fetch it from that table to write to another table.

            • 3. Re: Running a procedure 100 thousand times
              L. Fernigrini

              There are many things that can be done

               

              1) Improve the process, avoid processing row by row and inserting into temp tables or variables, try to do averything in SET operations that update multiple rows on a single execution.

              2) Check the execution plan of the sentences you are running, maybe a missing index or other improvement can change the performance witout having to rewrite the process

              3) Run the process in parallel

               

              But it is impossible to help you without more context... You should explain in detail what the process does (and why)

              • 4. Re: Running a procedure 100 thousand times
                GregV

                Hi,

                 

                What's the DB version and edition? Like Ed I'm suspecting some slow-by-slow processing here: looping through the 100 thousands rows and for each of them call a procedure.

                Instead process the data using direct SQL. If you are using Enterprise Edition you could benefit from native parallelism, if Standard then using jobs to parellelize batches of rows could be an option.

                • 5. Re: Running a procedure 100 thousand times
                  Frank Kulash

                  Hi,

                   

                  Sure; depending on how your procedures work, you can have multiple sessions do exactly what you're doing now.  What have you tried, and what was the problem with it?

                   

                  There's no guarantee that using multiple sessions will be any faster; it might actually be slower.  How you run a procedure is usually less of a concern than what the procedure does.  I would first try to improve (or completely replace) the procedure: find out where it is spending time, and try to improve that.  Start with the business model.  Why does every customer need to be updated?  Why do all the customers have to be updated at the same time?  Could some of the work be done every time a transaction is preformed?

                  Sunny86 wrote:

                  ...

                  now one procedure cursor fetches customer and call another procedure to fetch details of customer..the second procedure inserts data to a temporary table and once it fetches all required data it will update to the main table.

                  That sounds like you're doing something to each customer individually.  Relational databases, like Oracle, work best when you handle large sets of data, not individual rows, at the same time.   Why is the existing process as slow as it is?  Why are you fetching data in one procedure, and updating it in another?  (That might be a good way to conceptualize what you're doing, but not a very efficient way to actually do it.)  Why are you using a temporary table, and not updating the real tables right away?

                  1 person found this helpful