Printable View
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
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
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
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
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)
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
plz read my first post, I said pl sql supports now array processing == bulk operations
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
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
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