1 2 Previous Next 28 Replies Latest reply: Mar 21, 2013 3:43 AM by BluShadow Go to original post RSS
      • 15. Re: Need Query
        982895
        Hi Blushadow,

        Sorry for the late repsonse..was on leave..
        Yes This requirement is for UAT and PROD differentiation .. As per my Boss, they will dump the data using export the user objects and import... The other team sometimes through RUN command, they will dump the data in the form of files. I have shown the below code to my PL. To improve the performance, Can this be modified like.. keeping the counter before the cursor so that the select statemnet can retrieve 1000 rows and then update the same and so on..

        create or replace procedure PATIENT_MASK AS
        BEGIN
          
        DECLARE
        
         CURSOR C
         IS
         SELECT * from PATIENT_DUMMY ;
        
         COUNTER number(4) default 0;
        
         BEGIN
        
          FOR rec in C
        
           Loop
        
        
            UPDATE PATIENT_DUMMY SET FIRSTNAME = FIRSTNAME||'|'||'X'
            WHERE REGISTRATIONID = rec.REGISTRATIONID ;
        
            counter := counter +SQL%rowcount ;
           
        
            If counter >= 1000 then
             counter := 0;
             commit;
            end if;
        
        
           END Loop;
        END ;
        
         end PATIENT_MASK;
        Help will be very much appreciated
        • 16. Re: Need Query
          BluShadow
          No, you are still committing inside a cursor loop, and you are still trying to do things in batches (the slow way). This is NOT how to do it if you want it to be fast.

          You just need to do:
          update patient_dummy set firstname = firstname||'|X';
          commit;
          It will perform faster, be more transactionally safe, and will do the job. why overcomplicate something so simple? There is no justification for breaking the update into 'batches' of 1000 records or whatever.
          • 17. Re: Need Query
            982895
            Hi Blushadow,

            As I already told , we tried with that simple statement but the DB got hand due to large no.of records.
            And this is the requirement from the user. So, Please make the changes in the procedure itself
            • 18. Re: Need Query
              Paul  Horth
              979892 wrote:
              Hi Blushadow,

              As I already told , we tried with that simple statement but the DB got hand due to large no.of records.
              And this is the requirement from the user. So, Please make the changes in the procedure itself
              What problem did you have in just doing a normal update? Be specific (error codes etc.)
              • 19. Re: Need Query
                APC
                979892 wrote:
                As I already told , we tried with that simple statement but the DB got hand due to large no.of records.
                Okay let's accept this is a real requirement.

                It this example I'm assuming you have less that 1 million rows with a numeric identifier which has been incremented monotonically i.e it ranges from 1 - <1,000, 000. You will need to adjust the {font:courier new}loop_target{font} value to fit your actual requirement.
                create or replace procedure PATIENT_MASK AS
                BEGIN
                  
                 loop_target pls_integer := 1000;
                  
                 BEGIN
                 
                  FOR loop_no in 0 .. (loop_target-1) 
                   LOOP 
                 
                    UPDATE PATIENT_DUMMY 
                    SET FIRSTNAME = FIRSTNAME||'|'||'X'
                    WHERE REGISTRATIONID between 1 + ( loop_no*1000) and  1000 + ( loop_no*1000)
                    and FIRSTNAME not like '%|X';
                 
                   commit;
                  
                   END Loop;
                 
                 end PATIENT_MASK;
                So basically it selects and updates records in chunkcs of 1000. There's no danger of ORA-1002 or ORA-1555, which are common side-effects of commiting inside a loop . The additional filter in the WHERE clause makes it idempotent, and that's always a good thing with long-running jobs.

                Cheers, APC
                • 20. Re: Need Query
                  982895
                  Hi Paul,

                  Doing just by a simple update is very much easy for us..but when my other teammates are doing the same, the DB is gettng hang (its not allowing anythng to do further) itseems..So, only this trial. Hope You understand my problem atleast now..
                  • 21. Re: Need Query
                    982895
                    Hi APC,

                    Thanku so much for the code. Just wanted to have clarification on the same. Will this be good in terms of performance when compared to the proc which I gave..

                    I will try to execute the same and let you know if problem persiists..

                    thanku
                    • 22. Re: Need Query
                      982895
                      And wwill it randomly take the rows and update ? as u have mentioned where condiiton FIRSTNAME not like '%|X'
                      • 23. Re: Need Query
                        982895
                        APC, one more doubt, u have given registration ID between 1 to 999..
                        This will work if ID starts from 1... rite?
                        If the ID randomly have the numbers, how can it works...
                        My table have values like :
                        1020141
                        10201350
                        10201351
                        10201501
                        1020145
                        • 24. Re: Need Query
                          BluShadow
                          979892 wrote:
                          Hi APC,

                          Thanku so much for the code. Just wanted to have clarification on the same. Will this be good in terms of performance when compared to the proc which I gave..
                          How can you compare code that is written to work against code that is fatally broken? Performance can always be considered 'better' in code that works against code that doesn't work.
                          • 25. Re: Need Query
                            APC
                            979892 wrote:
                            Will this be good in terms of performance when compared to the proc which I gave.
                            Your procedure was RBAR. My appraoch used set-based processing. In SQL sets always beat rows
                            And wwill it randomly take the rows and update ? as u have mentioned where condiiton FIRSTNAME not like '%|X'
                            No. That line is there if you need to re-run the procedure again, because it didn't complete the first time. The problem with intermittent commits is that it breaks the transactional integrity. You don't - or at least you shouldn't - want to update the same row more than once.
                            If the ID randomly have the numbers, how can it works...
                            If the ID is a primary key - which it should be - and numeric - which it should be - then there is no random-ness in the query. But if you have gaps in the series you will find that ...
                            where ID between 2001 and 2999
                            ... actually only updates 875 records. So there's variability in the size of the batch, that's all.


                            Cheers, APC
                            • 26. Re: Need Query
                              982895
                              Hi APC,

                              Thanks for the response again. I tried testing the code but its taking more than 1 hr to execute 8000 rows. I guess the line
                              WHERE REGISTRATIONID between 1 + ( loop_no*1000) and  1000 + ( loop_no*1000)
                                  and FIRSTNAME not like '%|X';
                              taking much time as 'like' operator take more time to execute. Please correctme if I am wrong ..
                              So, atleast for the first time removed the line 'and FIRSTNAME not like '%|X' ' and executed but stilll taking more time.
                              Please suggest me to run the Proc little faster


                              Thanks,

                              Edited by: 979892 on Mar 20, 2013 11:16 PM
                              • 27. Re: Need Query
                                Paul  Horth
                                979892 wrote:
                                Hi Paul,

                                Doing just by a simple update is very much easy for us..but when my other teammates are doing the same, the DB is gettng hang (its not allowing anythng to do further) itseems..So, only this trial. Hope You understand my problem atleast now..
                                The whole DB hangs? You can't do anything else?

                                If you really mean that, I would log a call to Oracle support.

                                Either that or you DB is woefully underpowered/undersized for the tasks you want it to do.

                                Here we regularly update millions of rows with no problem whatsoever.
                                • 28. Re: Need Query
                                  BluShadow
                                  979892 wrote:
                                  Please suggest me to run the Proc little faster
                                  The fastest way is the single update statement. The moment you start trying to do things in batches you must expect it to be slower (usually very noticably slower).

                                  If a single update statement seems to be a problem, you would be better to figure out why it's causing a problem and fix that issue (i.e. increase available temp space or whatever is needed), rather than trying to work around it with slower code.
                                  1 2 Previous Next