Using SQL and Perl together - what should be used for common functions?

Question

I did not find a single deception of this question, but if there is one or more, sorry, comment on the link.

The question is basic, since it is probably the answer. If I use Perl to execute and work with a database, what prospect (Perl vs. SQL) should I take on the burden when common functions are involved?

Functions such as - LEN , IF/ELSE , CONCAT and many others, 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 using Perl. So, if the same logic can be implemented in Perl, is it worth rewriting? What conditions influence the decision to take on the burden of one system and place it on another?

 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') 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; 

Answer 1

I would look at it in terms of performance and the prospect of reuse.

If you try it on both sides, you can find it much faster than the other - it will be a good indicator that you prefer.

If you reuse a query in several places, you will want to include most of the business logic in the query as much as possible, so you do not need to replicate it in the graphical interface.

(and I must say, although I am not part of your question, that most of this case looks like this: you can make a good model in the circuit and replace the case with a normal join with some associative tables)

Answer 2

Simple functions like LEN or SUM or COUNT usually work best in SQL.

This list of monsters, on the other hand, I would probably leave in the application (and use a dictionary, not a lot of radio buttons)

EDIT - Or join another table to eliminate many of these cases. good answer Randy. when you have many cases, it comes to the point that its data, not the logic, and the request should not contain data.

Answer 3

There are two considerations here:

• Performance and Scalability:

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

  However, if you start to scale, it’s much harder to scale the web server than adding new clients (or application servers if your business logic lives on the application server). Thus, for any significant number of queries, in terms of performance, you should ALWAYS try to disable as much SQL server processing as possible.

• Code Reuse:

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

  • If you have> 1 part of Perl code that executes this logic, you can either put it in SQL (but see the commenting on the performance described above), or better yet, put it in a Perl module and use that module everywhere. Basically, a data access object (DAO).

  • If you have code in MANY languages, including Perl, that needs this, then you need to decide whether the performance problems with storing code on the SQL server outweigh the pain associated with the correct DAO logic in synchronizing between libraries in> 1 tongue.

    Obviously, if you switch to SQL logic, you SHOULD encapsulate it in a view or stored procedure to avoid duplication of code in these several languages.