Forum Stats

  • 3,782,584 Users
  • 2,254,669 Discussions
  • 7,880,131 Comments

Discussions

Categories

How to write code efficiently

User_R4VJD
User_R4VJD Member Posts: 31 Green Ribbon
in SQL & PL/SQL

       cur1 and cur2 - cursor value

p_vent - In parameter of procedure

SELECT max(generation_date)

        FROM T1

        WHERE c_cd=cur1.c_cd 

           AND d_cd=cur1.d_cd 

           AND l_cd=cur1.l_cd 

           AND T1_sub_l =cur2.sub_lat

           AND T1_cln =NVL (p_vent, '%')

           AND via = (select via 

from T2 

where l_cd=cur1.l_cd 

AND sub_l=cur2.sub_lat

AND cln =NVL (p_vent, '%')

AND p_cd=NVL (p_pri_code, '%'));


How can we write this code efficiently in oracle 11g

  

Tagged:
· Share on TwitterShare on Facebook

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,401 Red Diamond

    Hi, @User_R4VJD

    How can we write this code efficiently in oracle 11g

    Don't use cursors unless you really need to. Depending on what you're trying to do, you can do it in a single query. If you really do need to use a cursor, use only one; combine the two cursors into one. Exactly how depends on your data and your requirements. If you'd care to post some sample data (CREATE TABLE and INSERT statements) for all tables involved, the results you want from that sample data, and explanation (in general terms) of how you get those results from that data, and the code behind the other cursor, then I could try to show you exactly how to do it.

    · Share on TwitterShare on Facebook
  • User_R4VJD
    User_R4VJD Member Posts: 31 Green Ribbon

    Please avoid that cursor details.

    Just help in writing code efficiently

    · Share on TwitterShare on Facebook
  • GregV
    GregV Member Posts: 3,075 Gold Crown

    Hi,

    As Frank said, don't use cursors if you don't need to, especially here as you seem to be interested only in getting a max date.

    Combine both queries and retrieve the max dates directly.

    By the way, are you sure about this kind of condition:

     AND T1_cln =NVL (p_vent, '%')

    Did you mean a LIKE operator since you're using '%', or is '%' really stored as is?

    · Share on TwitterShare on Facebook
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,850 Gold Crown

    There is no purely logical reason why that code should be inefficient, given the correct constraints and indexing there would be an extremely efficient execution path to produce the required result.

    It's possible, however, that in your specific case the data model means the relevant constraints are not valid.

    Note that the "via = (select from T2)" means the subquery MUST logically be able to return exactly one row. (If it returns more than one then Oracle will raise an error, if it returns no rows you have effectively got a predicate "via = null" which does not evaluate to true (this is not an error, but it might not be what you were expecting).

    As the previous two responses have implied - is this query inefficient, or is it simply that the surrounding pl/sql cursor approach means it executes a very large number of times, accumulating a much greater volume of work that seems reasonable.

    Regards

    Jonathan Lewis

    · Share on TwitterShare on Facebook
  • EdStevens
    EdStevens Member Posts: 28,611 Gold Crown
    edited Jun 16, 2021 1:59PM

    Please avoid that cursor details.

    Just help in writing code efficiently

    But it is most likely that the cursor IS the source of any inefficiency.

    I'm always amazed when people ask for help solving a problem, but try to take the most likely solutions off the table.

    BluShadowKayK
    · Share on TwitterShare on Facebook
  • BluShadow
    BluShadow Member, Moderator Posts: 41,623 Red Diamond

    We don't have your data, nor have you provided details of the cur1 and cur2 cursors, nor shown the full PL code nor described the logic you are trying to achieve, so that kinda makes it hard for us to help fully.

    From what I can gather, you've got cur1 as a main cursor, then cur2 is likely a cursor that opens up inside that cur1 cursor based on some related data from cur1, and then you're querying the MAX value from T1 using some related data in T2 based on both of the cursors.

    As others have already said, having cursor looping is more likely to slow things down as you'll be issuing your inner query for each loop of the inner cursor. This is kind of "row by row" or otherwise known as "slow by slow" processing.

    If you can, you should consider combining your cursors into a single SQL query, something along the lines of...

    SELECT T1.c_cd, T1.d_cd, T1.l_cd, T1.l_sub_1, max(T1.generation_date)
FROM   (<query of cur1>) cur1
       JOIN (<query of cur2) cur2 on (<whatever join conditions related cursor 2 to cursor 1)
       JOIN T1 on (    T1.c_cd = cur1.c_cd
                   AND T1.d_cd = cur1.d_cd
                   AND T1.l_cd = cur1.l_cd
                   AND T1.T1_sub_l = cur2.sub_lat
                  )
       JOIN T2 on (    T2.via   = T1.via               -- equivalent via comparison
                   and T2.l_cd  = T1.l_cd              -- T1.l_cd = cur1.l_cd
                   and T2.sub_l = T1.T1_sub_l          -- T1.T1_sub_l = cur2.sub_lat
                   and T2.cln   = T1.T1_cln            -- T1.T1_cln = NVL(p_vent, '%')
                  )
WHERE  T1.T1_cln = NVL(p_vent, '%')
AND    T2.p_cd   = NVL(p_pri_code, '%')
GROUP BY T1.c_cd, T1.d_cd, T1.l_cd, T1.l_sub_1


    Your "via = (select via from T2 where ...) was essentially using many of the same conditions from your cursors as you were using with T1, so it makes more logical sense to just treat it as a join to T2 instead using the values you've already compared with T1.

    Then bring in your cursors as the main drivers of the query, starting with cur1, joining that to cur2 on whatever conditions are needed to relate them, and then join T1 to the results of those.

    You'll then get results of your MAX for all the T1 records in one go, so you'll need to include the columns from T1 in the select and group by them, so you've got the MAX for each one.

    Whether you stick all that in one cursor and process that one by one is up to you... depends what you're doing with those results.

    · Share on TwitterShare on Facebook