0

So, I'm reading Ian Abramson's Beginners guide to Oracle Database 10g (coming from using mysql), and it mentions PL/SQL and gives all these great reasons to use it, however I'm doing this in preparation for a rewrite of several applications my company uses.

My head programmer argues that if PL/SQL is no more efficient at database access, all parsing and queries should be done on client side programs to spread the load out from the Oracle server. I argue that we should compartmentalize our code and attempt to avoid code duplication, and PL/SQL may help us do that.

The problem is, we don't actually know (And this book doesn't tell us), how efficient database access is in PL/SQL over an application running on a different machine that makes 3-5 (largish) queries per operation. I would think it's more likely to run faster because (to my knowlege) pl/sql functions store and save their execution plans, and the traditional network overhead would be cut by a factor of 3-5 (depending on the number of queries)

Sidney
  • 624
  • 7
  • 20

1 Answers1

3

This is a non-trivial question, and and will likely be influenced by your application design. In general, if you can do it in SQL, do it in SQL. If you can't do it in SQL, do it in PL/SQL. If you can't do it in PL/SQL, do it in Java (in the database). If that doesn't work, do it in external client side code.

Now, that isn't universal. If you're not doing something database related, then it makes sense to do it on the client side. If it's highly CPU intensive, and it makes sense to do it in the client, then great. The reason is, you're paying big money to license Oracle, and you're (probably) paying per CPU. So, if you're doing a lot of non-database related work on the CPUs licensed for Oracle, that's extremely expensive.

To summarize, if it's actual database related code (insert/update/delete/select), even if it has some non-trivial logic around it, it makes sense to put it in the database. If it's not actually interacting with the database, it probably doesn't belong in the database.

Finally, your comments about PL/SQL code efficiency are true. There are efficiencies with PL/SQL around parsing and storing execution plans. Also, PL/SQL will automatically pre-fetch data.

Lots more information about advantages of PL/SQL can be found here: http://docs.oracle.com/database/121/LNPLS/overview.htm#LNPLS00101

halfer
  • 19,824
  • 17
  • 99
  • 186
Mark J. Bobak
  • 13,720
  • 6
  • 39
  • 67
  • I know I'm probably asking for more of an opinion here, but, if I were working with a query such as "Get A, if A.property, get B, if B.Property, get C.." (It's actually a recursive operations on the same table, the most levels of recursion I've seen are 5 but that doesn't mean it couldn't be infinite), would it be better to run in pl/sql as much as possible due to decreased round-trip time/caching execution plans, or would the excessive checks be better placed in a client side app? – Sidney Sep 04 '14 at 14:31
  • Not sure what you're getting at, as "get a, if a.property,get b, if b.property..." isn't actually a SQL query. Also, not sure what you mean by "excessive checks"...? – Mark J. Bobak Sep 04 '14 at 15:00
  • Ah, sorry, I was trying to boil down a redundant process. We have a table that has some recursive indexes. For example, the logic structure in the client side app is something like "While (A.next is not 0) retrieve B from database, set A equal to B". There are repeated checks to see if A.next is 0, and repeated queries to get B from the database. I'm wondering if logic like this wouldn't be better implemented in PL/SQL. – Sidney Sep 04 '14 at 15:03
  • If A is a cursor you are looping over and then querying out one row from B for every row in A in a loop, the best would be to turn that logic into a single SQL statement using joins, if that is at all possible. And it very often is. If the logic is very convoluted it might be necessary with the loop, and then it can most often perform better in PL/SQL than client side, both because of much less network round trips and because PL/SQL is designed to perform SQL pretty efficiently doing for example automatic bulk fetching in a for loop, cursor caching and other optimizations. – Kim Berg Hansen Sep 04 '14 at 17:43
  • 1
    Oh, and you mention recursive operation on the same table - that can also often be handled in SQL using CONNECT BY or in version 11.2 with recursive subquery factoring. If it's data handling and manipulation, put as much logic as possible into SQL, in my opinion ;-) – Kim Berg Hansen Sep 04 '14 at 17:47