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
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.
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?
plz read my first post, I said pl sql supports now array processing == bulk operations
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
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
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
Can you please share code with me. We have similar problem to move large set oracle to DWH as csv files. We used PL/SQL procedure , but we faced the issue like temp table space exceed.
If possible please share the code for data movement.That will be more helpfull to me
Click Here to Expand Forum to Full Width