1 2 Previous Next 25 Replies Latest reply on Aug 13, 2009 2:01 PM by Sven W. Go to original post
      • 15. Re: LOOP Problem
        21205
        PL/SQL is fast.... but not as fast as SQL...

        Horten69 is right. You asked advise, you get advise, you choose to ignore it,.. fine. Just remember that this is a volunteer forum.
        • 16. Re: LOOP Problem
          563935
          Read my last answer... I will use the thing that works.
          My experience with this one is that I won't get it faster than the pl/sql. If you know how to, I'm pleased.

          I'm sorry if my english and writing was to gross.

          BT
          • 17. Re: LOOP Problem
            94799
            As pointed out simple set-based (i.e. SQL) solutions are typically much more performant than procedural (i.e. PL/SQL) ones.

            If your simple SQL does not follow this rule then you should be anticipating that something is causing the optimizer to take an inefficient approach, either the way you have written your SQL or the underlying table structure or statistics. It follows that an inefficient SQL approach can make PL/SQL solutions look rather cheaper - the truth though is that a much more efficient SQL solution probably exists but you have not found it yet.

            In your case it appears to me that the join to the main table from correlated subquery ("cal.matnr = nvl (mara.imatn, mara.matnr)") is not going to be able to be answered by an index lookup, hence running the subquery for each row of the main table is likely to be extremely expensive.

            Rather than UPDATE with subquery here I would be looking at UPDATE of in-line view or perhaps MERGE.

            Can you tell us what version you are on? Also can you give us some simple example of the tables / data here and clarify which columns referenced are indexed?
            • 18. Re: LOOP Problem
              66470
              As long as we are comparint apples to pears (working sql to non-working plsql) i do not care about the "my pl/sql is faster than my sql". Otherwise my suggestion would be to do

              update table_a set beh = 0;

              This as well as the pl/sql does not do what it should, but it will be pretty fast.

              So as padders said : give more information on what you have to do, and you might get some help.
              • 19. Re: LOOP Problem
                563935
                table matnr in main_cur cursor includes the matnr that I want to look up (indexed)


                table mard in beh_cur includes stock where matnr is the primarykey (indexed)
                table t001l in beh_cur includes naming of the warehouses, and I have excludes the where-statemant where I just want to look up some warehouses in this cursor. lgort is the primary_key (indexed)

                Mara in matr_cur includes all materials (matnr) and classes for the materials (imatn). Both matnr and imatn are indexed.
                Many matnr can have a relation to one imatn, but just one imatn can have a realation to a matnr.

                Since matnr in main_cur can be a imatn or/and a matnr, I have to look up if I got a match ahead imatn before I match it ahead å single matnr. If it match the imatn, then I want to sum stock for alle the matnr that have a relation to this imatn.

                I use oracle 9.


                BT
                • 20. Re: LOOP Problem
                  Sven W.
                  btlenes wrote:
                  I have tried your suggestions with no luck.
                  I'm going to update a table with the value of beholdning_.

                  I've also tried the simplest SQL to do so, but I don't have the time to wait for many hours.

                  ex that I have tried:

                  update table_a cal
                  set beh = (select sum(to_number(replace(mard.labst, '.', ''))) beh
                  from mard
                  , mara
                  , t001l
                  where 1 = 1
                  and mard.lgort = t001l.lgort
                  and cal.matnr = nvl(mara.imatn,mara.matnr)
                  and mara.matnr = mard.matnr)
                  But an update like this can be easily imroved performancewise. There are a some things to mention.

                  1. It is an update. The Pl/sql logic that you compared it to has only select.
                  2. You update the full table table_a. So each line of this potentially big table will be updated.
                  3. Because of this join "and cal.matnr = nvl(mara.imatn,mara.matnr)" the statement might not be able to use a good index. maybe to split it in two separate updates would be the clever thing to try.
                  4. please enclose your code in tags like this {<b></b>code}. it improves readability and code formatting a lot.
                  5. to_number(replace(mard.labst, '.', '')) looks dangerous to me. Is the mard.labst column by any chance already of datatype number or what the hell do you want to do there? Imaging two values like 2.01 and 2.001. The sum will be 2202 (= 201 + 2001). The typical mathematician would expect 4.011. but your math might be different. This problem is of cause meaningless for the comparison sql to pl/sql. But the type conversions take time.
                  6. Not sure if you always need to join table t001l
                  update table_a cal
                  set beh = (select sum(to_number(replace(mard.labst, '.', ''))) 
                                        from mara, t001l, mard
                                        where mard.lgort = t001l.lgort
                                       and cal.matnr = mara.imatn 
                                       and mara.matnr = mard.matnr)
                  where cal.matnr in (select m2.imatn from mara m2, t001l t2 where m2.lgort = t2.lgort) /* this is the important part to speed up the query! */ 
                  ;
                  
                  update table_a cal
                  set beh = (select sum(to_number(replace(mard.labst, '.', ''))) 
                                        from t001l, mard
                                        where mard.lgort = t001l.lgort
                                        and cal.matnr = = mard.matnr)
                  where beh is null /* some restriction to avoid updating the same values from the first update again */
                  and not exists (select null from mara m2, t001l t2 where m2.lgort = t2.lgort and cal.matnr = m2.imatn) /* this can the important part to speed up the 2. query! */ 
                  ;
                  • 21. Re: LOOP Problem
                    66470
                    Having 2 updates might be a bit of a problem, specially if other sessions are changing the base data we use for the update...

                    so i would do a
                    update table_a cal
                    set beh = COALESCE((select sum(to_number(replace(mard.labst, '.', ''))) 
                                          from mara, t001l, mard
                                          where mard.lgort = t001l.lgort
                                         and cal.matnr = mara.imatn 
                                         and mara.matnr = mard.matnr)
                                        ,(select sum(to_number(replace(mard.labst, '.', ''))) 
                                          from t001l, mard
                                          where mard.lgort = t001l.lgort
                                          and cal.matnr = = mard.matnr))
                    ;
                    i totally agree on the "what the hell are you doing here : sum(to_number(replace(mard.labst, '.', ''))) " question.

                    hth
                    • 22. Re: LOOP Problem
                      Sven W.
                      horten69 wrote:
                      Having 2 updates might be a bit of a problem, specially if other sessions are changing the base data we use for the update...
                      I agree on that, however this would be a problem only if the matnr/imatn changes. And the pl/sql logic might have this problem too.
                      • 23. Re: LOOP Problem
                        66470
                        I would not really take that pl/sql as a reference...
                        • 24. Re: LOOP Problem
                          563935
                          Thanks for your feedback. These sql's work perfectly.

                          The first one took about 8 minutes to execute, and the second 14 minutes.

                          The replace for the labst is to convert the decimals (not the thousand), so it can be used for the european characterset. This has been tested for the fails that you mention and others. But thanks for your alertness.


                          BT
                          • 25. Re: LOOP Problem
                            Sven W.
                            It is good to know that we could help. But please test the result of my updates very carefully, even if performancewise it seems to be ok now.

                            Especially for the different combinations of matnr. I tried to be as close to your original update as possible. But only you have the data and can make sure the result is ok.
                            1 2 Previous Next