Using SQL & Perl together - Which should be used for common functions?

I didn't find any dupes of this question, but if there is one or more, I'm sorry - please comment with link.

The question is basic, as probably is the answer. If I'm using Perl to execute & act upon a database, which avenue (Perl vs. SQL) should I place the burden on when shared functions are involved?

Functions like - LEN, IF/ELSE, CONCAT and many more as well as arithmetic functions, for example are common to both systems.

This SQL statement is loaded with case blocks and other operations that can be replicated with Perl. So, if the same logic could be implemented in Perl, is it worth re-writing? What conditions influence the decision to take the burden of one system and place it on the other?

SELECT DISTINCT     s.id stu_id,
                    stu_id.fullname stu_name,
                    p.major1 major,
                    p.minor1 minor,
                    s.reg_hrs,
                    NVL(st.cum_earn_hrs,0) ttl_hrs,
                    p.adv_id curr_adv_id,
                    adv_id.fullname curr_adv_name,
                    CASE    WHEN    (p.adv_id <> 35808 AND p.major1 = 'NS')                 THEN    (1165)
                            WHEN    (p.adv_id = 35808 AND p.major1 = 'NS')                  THEN    (35808)
                            WHEN    (p.adv_id = 9179 AND p.major1 = 'DART')                 THEN    (9179)
                            WHEN    (p.minor1 IN ('RT','RESP') AND st.cum_earn_hrs >= 24)   THEN    (70897)
                            WHEN    (p.major1 IN ('CDSC','CDSD'))                           THEN    (52125)
                            WHEN    (p.major1 IN ('CA','CB'))                               THEN    (24702)
                            WHEN    (p.minor1 = 'NURS')                                     THEN    (51569)
                            WHEN    (p.major1 = 'LEG')                                      THEN    (13324)
                            WHEN    (p.major1 = 'CC')                                       THEN    (73837)
                            WHEN    (p.major1 = 'CCRE')                                     THEN    (1133)
                            WHEN    ((p.adv_id IN (SELECT DISTINCT id FROM fac_rec WHERE stat = 'I'))
                                    OR (st.cum_earn_hrs < 24 AND (p.adv_id||p.major1) IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat = 'A' AND max_stu > 0 AND min_hrs >= 24))
                                    OR (s.id NOT IN (SELECT DISTINCT stu.id FROM stu_acad_rec stu, sess_info si WHERE stu.yr = si.prev_yr AND stu.sess = si.prev_sess AND stu.reg_hrs > 0 AND stu.reg_stat IN ('C','R') AND stu.prog = 'UNDG'))
                                    OR ((p.adv_id||p.major1) IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat <> 'A' OR max_stu <= 0))
                                    OR ((p.adv_id||p.major1) NOT IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat = 'A' AND max_stu > 0)))
                            THEN    (9238)
                            ELSE    (p.adv_id)
                    END     new_adv_id,
                    CASE    WHEN    (p.adv_id <> 35808 AND p.major1 = 'NS')                 THEN    ('Deborah')
                            WHEN    (p.adv_id = 35808 AND p.major1 = 'NS')                  THEN    ('Veronica')
                            WHEN    (p.adv_id = 9179 AND p.major1 = 'DART')                 THEN    ('Stella')
                            WHEN    (p.minor1 IN ('RT','RESP') AND st.cum_earn_hrs >= 24)   THEN    ('Lisa')
                            WHEN    (p.major1 IN ('CDSC','CDSD'))                           THEN    ('Joanne')
                            WHEN    (p.major1 IN ('CA','CB'))                               THEN    ('Barbara')
                            WHEN    (p.minor1 = 'NURS')                                     THEN    ('Karen')
                       开发者_Go百科     WHEN    (p.major1 = 'LEG')                                      THEN    ('Nancy')
                            WHEN    (p.major1 = 'CC')                                       THEN    ('Alberta')
                            WHEN    (p.major1 = 'CCRE')                                     THEN    ('Naomi')
                            WHEN    ((p.adv_id IN (SELECT DISTINCT id FROM fac_rec WHERE stat = 'I'))
                                    OR (st.cum_earn_hrs < 24 AND (p.adv_id||p.major1) IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat = 'A' AND max_stu > 0 AND min_hrs >= 24))
                                    OR (s.id NOT IN (SELECT DISTINCT stu.id FROM stu_acad_rec stu, sess_info si WHERE stu.yr = si.prev_yr AND stu.sess = si.prev_sess AND stu.reg_hrs > 0 AND stu.reg_stat IN ('C','R') AND stu.prog = 'UNDG'))
                                    OR ((p.adv_id||p.major1) IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat <> 'A' OR max_stu <= 0))
                                    OR ((p.adv_id||p.major1) NOT IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat = 'A' AND max_stu > 0)))
                            THEN    ('Staff')
                            ELSE    (adv_id.fullname)
                    END     new_adv_name,
                    CASE    WHEN    (p.adv_id <> 35808 AND p.major1 = 'NS')                 THEN    ('NS majors not assigned to Veronica go to Debbie')
                            WHEN    (p.adv_id = 35808 AND p.major1 = 'NS')                  THEN    ('NS majors stay with Veronica')
                            WHEN    (p.adv_id = 9179 AND p.major1 = 'DART')                 THEN    ('DART majors stay with Stella')
                            WHEN    (p.minor1 IN ('RT','RESP') AND st.cum_earn_hrs >= 24)   THEN    ('RT-RESP minors go to Lisa')
                            WHEN    (p.major1 IN ('CDSC','CDSD'))                           THEN    ('CDSC-CDSD majors go to Joanne')
                            WHEN    (p.major1 IN ('CA','CB'))                               THEN    ('CA-CB majors go to Barbara')
                            WHEN    (p.minor1 = 'NURS')                                     THEN    ('NURS minors go to Karen')
                            WHEN    (p.major1 = 'LEG')                                      THEN    ('LEG majors go to Nancy')
                            WHEN    (p.major1 = 'CC')                                       THEN    ('CC majors go to Alberta')
                            WHEN    (p.major1 = 'CCRE')                                     THEN    ('CCRE majors go to Naomi')
                            WHEN    (p.adv_id IN (SELECT DISTINCT id FROM fac_rec WHERE stat = 'I'))
                            THEN    ('Current advisor is inactive')
                            WHEN    (st.cum_earn_hrs < 24 AND (p.adv_id||p.major1) IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat = 'A' AND max_stu > 0 AND min_hrs >= 24))
                            THEN    ('Total credits for this student did not meet the advisor reqs for this major')
                            WHEN    (s.id NOT IN (SELECT DISTINCT stu.id FROM stu_acad_rec stu, sess_info si WHERE stu.yr = si.prev_yr AND stu.sess = si.prev_sess AND stu.reg_hrs > 0 AND stu.reg_stat IN ('C','R') AND stu.prog = 'UNDG'))
                            THEN    ('This student did not attend '||si.prev_sess||si.prev_yr)
                            WHEN    ((p.adv_id||p.major1) IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE (stat <> 'A' OR max_stu <= 0)))
                            THEN    ('Current advisor is not advising students with this major')
                            WHEN    ((p.adv_id||p.major1) NOT IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat = 'A' AND max_stu > 0))
                            THEN    ('Current advisor is not advising students with this major')
                            ELSE    ('Student will stay with current advisor')
                    END     change_comm
FROM                stu_acad_rec s,
                    prog_enr_rec p,
                    OUTER stu_stat_rec st,
                    id_rec stu_id,
                    id_rec adv_id,
                    sess_info si
WHERE               s.id = p.id
                    AND s.id = st.id
                    AND s.id = stu_id.id
                    AND p.adv_id = adv_id.id
                    AND s.yr = si.curr_yr
                    AND s.sess = si.curr_sess
                    AND s.reg_hrs > 0
                    AND s.reg_stat IN ('C','R')
                    AND s.prog = 'UNDG'
                    AND p.prog = 'UNDG'
                    AND st.prog = 'UNDG'
                    AND s.id NOT IN (3,287,9238,59999) {System test use IDs}
INTO TEMP           stu_list
WITH NO LOG;

---

i would look at this from a performance perspective, and a re-use perspective.

If you try it on both sides, you may find one to be much faster than the other - that would be a good indicator for which to prefer.

If you will re-use some query in more than one place, then you would want to incorporate much of the business logic in the query as possible, so you do not need to replicate that in the GUI.

(and i have to say, although not strictly part of your question, that much of that case logic looks like you could do a good model in the schema and replace the case with a normal join to some associative tables)

---

Simple functions like LEN or SUM or COUNT are usually best done on SQL.

Those monster list of cases, on the other hand, I would probably keep in the application (and use a dictionary rather than loads of switches)

EDIT - or join to another table to eliminate many of those cases. good answer Randy. when you have that many cases it gets to the point that its data rather than logic, and the query shouldnt be holding data itself.

---

There are 2 considerations here:

1. Performance and scalability:

   If you only have a few (1-2) concurrent clients, SQL might be faster doing the same logic.

   However, if you start scaling up, it is a LOT more complicated to scale up a web server than to add new clients (or app servers if your business logic lives in an app server). So for any significant scale of requests, performance-wise you should ALWAYS try to offload as much processing as possible off of SQL server.

2. Code reuse:

   • If you only have ONE Perl code piece that will do this logic, then you can equally well put it in SQL or Perl.

   • If you have >1 piece of Perl code that does this logic, you can either put it in SQL (but see performance tradeoff discussed above), or better yet, put it into a Perl module and use that module everywhere. Basically, data access object (DAO).

   • If you have code in MANY languages including Perl that needs this, then you need to decide for yourself whether the performance issues of keeping the code in SQL server outweigh the pain ofkeeping the correct DAO logic in sync between libraries in >1 language.

     Obviously, if you go for SQL side logic, you MUST encapsulate it into a view or stored procedure to avoid code duplication in those multiple languages.