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

Solution 1:

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)

Solution 2:

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.

Solution 3:

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.