This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Feb 11, 2013 9:41 PM by BillyVerreynne RSS

sql vs pl/sql

Rahul_India Journeyer
Currently Being Moderated
In this forum i have found everyone mentioning that uses SQL as much as possible but in PL_SQL Best Practices with Steven Feuerstein ,Steve ask to stop writing so much SQL.
Am i am misinterpreting things?If not then why this contradiction?
  • 1. Re: sql vs pl/sql
    Justin Cave Oracle ACE
    Currently Being Moderated
    There is a legitimate difference of opinion.

    If you're looking to maximize efficiency, you generally move work to SQL. If you're looking to maximize reusability, you generally do more in PL/SQL. Generally, most people have to balance those two objectives so they use some combination. Generally, data warehouse folks are more concerned with performance and don't need a lot of code reuse (since there is only one path for data to go through) so they tend to do much more in SQL.

    Justin
  • 2. Re: sql vs pl/sql
    SomeoneElse Guru
    Currently Being Moderated
    It's a different context.

    Very often in this forum someone will post a problem with a stored procedure that uses a cursor for loop to update a table.

    The suggestions that usually follow are to remove the cursor loop and use only sql statements to do the job.

    What Steven is talking about is to encapsulate dml into stored procedures and call them as needed. This reduces the need to write many individual sql statements.
  • 3. Re: sql vs pl/sql
    IvanBlanarik Journeyer
    Currently Being Moderated
    I've seen the Steven's presentation and I agree with him that it's good to have some standartized get/set procedures and use them instead of repeating the same select everywhere. This is good for the reusability and for easy code maintenance. I personally prefer to have some code generator which create package with standard procedures. Then I have to implement only procedures for special some purposes or for better performance. And I always must to create views to join several tables in order to provide data for UI... in this case sql cannot be replaced by pl/sql.
  • 4. Re: sql vs pl/sql
    Rahul_India Journeyer
    Currently Being Moderated
    Well Ok got it.
    Which path to chose depends on the need.

    So if you are an application developer then PL/SQL is the way to go.
  • 5. Re: sql vs pl/sql
    Justin Cave Oracle ACE
    Currently Being Moderated
    Rahul India wrote:
    So if you are an application developer then PL/SQL is the way to go.
    "Application developer" is pretty broad-- every developer is an application developer.

    If you are building a simple OLTP application, since most things that you're doing involve inserting or updating a single row in a table, the overhead of doing things in PL/SQL is relatively minimal and the benefits of reusability are relatively high so maximizing PL/SQL is a good plan. But even then, you're likely to end up in situations where you at least occasionally want to manipulate many rows-- that's where it often gets tricky because using the PL/SQL API that you already built for single-row cases won't scale nearly as well as straight SQL will. But putting the logic in SQL potentially makes it much harder to reuse. That's where understanding the trade-offs becomes important.

    Justin

    Edited by: Justin Cave on Feb 8, 2013 3:16 PM
  • 6. Re: sql vs pl/sql
    Etbin Guru
    Currently Being Moderated
    So if you are an application developer then PL/SQL is the way to go.
    To be taken [url http://en.wiktionary.org/wiki/cum_grano_salis]c u m   g r a n o   s a l i s (with a grain of salt) as you might end with something like I came across this week - a PL/SQL procedure doing some kind of account balance verification dealing with three tables of approximately 92, 42 and 39 million rows respectively.
    On Exadata the PL/SQL procedure takes 38 - 40 minutes each day to verify something under 5% of accounts (based on 23 working days in a month)
    The straight SQL takes under 90 seconds to verify all the accounts. Priceless ... (worth Master Card commercials)
    A side note on scalability: the PL/SQL procedure takes between 6 to 7.5 hours to complete it's daily portion of work when dealing with only three times as much rows as already mentioned roughly in the same proportions.

    Regards

    Etbin
  • 7. Re: sql vs pl/sql
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Rahul India wrote:
    In this forum i have found everyone mentioning that uses SQL as much as possible but in PL_SQL Best Practices with Steven Feuerstein ,Steve ask to stop writing so much SQL.
    Am i am misinterpreting things?If not then why this contradiction?
    I disagree with that (writing less SQL and more PL/SQL).

    SQL does one thing. PL/SQL does another. That simple. Not mystical explanation.

    The languages are not the same. For heaven's sake.. not the same in structure, not in syntax, not in design, not in usage, not in application.

    The correct approach is to use the right language for the job. Do not use a screwdriver to slam in nails. Do not use hammer to tighten screws. It should be as simple as that. Unfortunately, more than a few seems to have loose screws when it comes to correctly using SQL and PL/SQL - and carry too much baggage to see and understand the simple truth of using the right tool for the job.
  • 8. Re: sql vs pl/sql
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Rahul India wrote:
    Well Ok got it.
    Which path to chose depends on the need.

    So if you are an application developer then PL/SQL is the way to go.
    Wrong. Utterly. Totally.

    The ONLY language that is available to process database data is SQL. Does not matter whether you use Cobol, C/C++. Java, Pascal, or whatever. You want to use database data? That is done via SQL.

    You don't want to use SQL? Then use a No-SQL database.

    So ANY application developer that develops SQL database applications, needs to use SQL to get to the data in the database.

    And this is where the problems start as (and I am going to generalise) most .Net and Java application developers do not understand database technology, do not understand database architecture, do not understand data modelling, do not understand relational design concepts and do not understand the SQL language.

    And then make stupid statements like "+do it in the application and do not get stuck with too much SQL and database issues+". Then proceed to write and design code that abuses the database, suffers from performance problems, and is unable to scale with even small increases in data volumes.

    On real world example. .Net application. Logon form. .Net code reads the ENTIRE app users table from the database into a collection class. Then call a method to loop through the collection, looking for the logon user's object, in order to verify the logon password with the user object's password property.

    This is not only idiotic and sad... but also typical of what most application developer's approach are to SQL databases.

    And this is the primary reason for using PL/SQL. Do not allow application developers to use a language, technology and architecture they do not understand. Do not allow a single SQL in app code. Instead, abstract that into a PL/SQL interface. And via abstraction, remove the need for app developers to understand the database.

    As PL/SQL now serve as the interface for application code, who does the SQL now that application developers do not? The PL/SQL developer that is tasked with the design and development of the abstraction interface in PL/SQL - where this code now needs to CORRECTLY and PROPERLY use the SQL language.

    A PL/SQL developer that is not as good in the SQL language as he/she is good in PL/SQL, has no job writing PL/SQL in the first place.
  • 9. Re: sql vs pl/sql
    Stew Ashton Expert
    Currently Being Moderated
    Billy, if only you could be heard everywhere, especially where I work.

    Among all the vitally important things you say, I do have one partial disagreement:
    via abstraction, remove the need for app developers to understand the database.
    It's not that easy.

    App developers need to understand "parse once, execute many", without which there is no scalability. This involves using bind variables, connection pools and prepared statement caches.

    App developers need to understand transactions, since the application must govern transactional logic.

    App developers need to understand statement level atomicity (as do the PL/SQL developers).

    Within the J2EE world, much of this understanding is required not only of the developers, but of those who configure the J2EE Servers.

    Bottom line, with an abstraction layer the developer has less need to understand the data internals, but still has to understand fundamentals of the database.
  • 10. Re: sql vs pl/sql
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Stew, using bind variables in this respect is more of an issue of how to use the OCI (Oracle Call Interface). Does not matter whether the client makes a SQL or PL/SQL call - bind variables need to be used.

    The app developer needs to know how to use the call interface in the application programming language (and consume and close ref cursor responses), however the application developer does not need to understand the database or SQL (or even database fundamentals).

    One of the best examples I have seen of this was back with Oracle 7.3 and a commercial ISV medical processing system for dealing with dental(?) claims for medical aid administration. There was an extensive and comprehensive PL/SQL package layer that abstracted the database into discrete business processing steps. The client app (think they used PowerBuilder back then) only made use of PL/SQL calls and from an app developer perspective, nothing need to be understood of the database, beyond that.

    The complexity from an app developer's perspective was in fact understanding the business processing for claims, and understanding the (very large and at time complex) abstract interface for dealing with simplistic claims to quite intricate claims.

    That was almost 20 years ago... and one would have expected that basic lessons would have been understood and learned in app development.
  • 11. Re: sql vs pl/sql
    Dave Rabone Journeyer
    Currently Being Moderated
    Billy  Verreynne  wrote:

    That was almost 20 years ago... and one would have expected that basic lessons would have been understood and learned in app development.
    and they are ... towards the end of most projects
  • 12. Re: sql vs pl/sql
    Karthick_Arp Guru
    Currently Being Moderated
    Rahul India wrote:
    In this forum i have found everyone mentioning that uses SQL as much as possible but in PL_SQL Best Practices with Steven Feuerstein ,Steve ask to stop writing so much SQL.
    Am i am misinterpreting things?If not then why this contradiction?
    Its funny that i asked the same question 5 years back :) Check out the link in Ask tom

    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:672724700346558185

    Actually Tom and Steven had one on one conversation in this ;)
  • 13. Re: sql vs pl/sql
    Etbin Guru
    Currently Being Moderated
    really ?
    According to Murphy's Law the problems should begin to arise only when the application is in production already. ;)

    Regards

    Etbin
  • 14. Re: sql vs pl/sql
    Etbin Guru
    Currently Being Moderated
    Thank you for the link.
    Each time I'm discussing the matter I remember having read it suggesting to search for Feuerstein on Ask Tom.
    It's between favorites now to have the link at hand as things are very much alike to Tom's disagreements and not getting any better.

    Regards

    Etbin
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points