Which one to use : Pro*C or PL/SQL
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Which one to use : Pro*C or PL/SQL

Hybrid View

  1. #1
    Join Date
    Mar 2001
    Posts
    149
    Hi,
    I was asked to develop a program that would process and extract data to a text file from Oracle. I'm more familiar with
    stored procedures and I would like to have all processing done within stored procedure and the results would be written to a file using a Pro*C program. A programmer at my workplace said that it's better to using Pro*c to do everything since its much faster compare to stored procedure. Is this true? Please comment on this. THanks

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    it is true, pro*c is much faster and powerful
    however pl/sql in 8i has improved a lot so it now can handle array processing like pro*c as well, but I guess C is always faster

  3. #3
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hello;
    PRO*C is a client-side tool accessing the database
    with embedded SQL/embedded PL/SQL

    Developing database-oriented programs
    is directly in PL/SQL much easier.
    The deploymant to different OS is much easier,
    The PL/SQL-Language is very easy to handle and
    has no POINTERS and has a very easy and secure DATA-Definition.

    Fast, what is fast?
    When you compare a PRO*C-Program using SQL'S and then a PL/SQL-PRocedure the procedure could even be faster due to less client-server-communication.

    Developing a Program with PL/SQL should be 2-3 times faster as with PRO*C mixed.

    Orca

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    The pity is PL/SQL is interpreted not compiled which make it slower no matter what

    We had this Datawarehouse extracter made by PRO*C then there was this guy who made a PL/SQL procedure to do the same. PRO*C took 45 minutes, PL/SQL took 2.5 hours

  5. #5
    Join Date
    Apr 2002
    Location
    Philippines
    Posts
    77
    I haven't use Oracle9i but have read that it does pl/sql compilation into native C code. Does this greatly improved pl/sql performance against using Pro*C? (Haven't used Pro*C either)

  6. #6
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Ok, loading Data in Datawarehouse not depends
    on the Language it depends on the Possibilty
    to insert data not row, but blockwise
    It's just an effctive way in bindung data
    and reducing overhead.
    In PL/SQL exists Bulk-operations too,
    have you used them?

    Orca

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    plz read my first post, I said pl sql supports now array processing == bulk operations

  8. #8
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    OK!
    But i think ist not correct to say C is always faster.
    when i would have a program without db-access you would be totally correct.

    Moving Businesslogic from the Client to the server is
    ALWAYS faster.
    In case of DWH i am your opinion, it's easier to do that in PRO*C. But the best results i still had wit embedded PL/SQL when the insert-logic is complex.
    Have a nice day
    Orca



  9. #9
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    One Example
    we had a batch-program written in PRO*C
    accessing and calculating Ticket-data in a DWH.
    The batch lastst 40H!
    Then we moved the whole thing to PL/SQL
    -> 3H!!
    Orca

  10. #10
    Join Date
    Apr 2002
    Posts
    14
    newbie

    There is no right or wrong answer here as the two tools are very different.

    It is really going to depend on the amount of data required by the processing and the complexity of that processing. C is good for CPU intensive processing but PL/SQL is better at handling data.

    The main thing to remember with Pro*C is that each call to Oracle is "expensive" - reduce the number of calls - by merging selects or using array fetching often makes a huge difference.

    If you need lots of data to process one line in the output file then PL/SQL is probably a better choice. If on the other hand the processing is CPU intensive (say floating point maths etc) and the data requirement is small then Pro*C might be better.

    Remember that the two technologies can be merged as well. Pro*C can often benefit from using PL/SQL to get diverse data in one call to Oracle. You can sometimes find that some derived column that is based on lots of data can benefit from being selected using a user defined PL/SQL function rather than selecting the data into Pro*C and then deriving the value.

    Also don't forget that if the extract file is simple to construct with SQL then a SQL statement run through SQL*Plus with spool might just do the trick (it does occassionally and is so simple to code). Extra gimmicks can then be added using simple awk / sed etc on Unix. In the right circumstances it's a blast to code and quick to run (SQL tuning dependent).

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width