-
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
-
-
This is disscuss about nothing.
Question about using pro*c (pro*ada, pro*fortran ...) or pl/sql doesn't exists.
We usually have only one basic question:
What kind of system architecture we are going to build:
- with havy client's component
- whth thin client
- mixed model (with balance between client and server(s))
- with deditated application servers.
...
- platform depended
- platform independed
...
when we decided for ourself this problem (or problems) we may make choises of
languges, tools ... what ever.
About faster/slover.
only 1 sql query can make a degradation of perfomance whole application in pl/sql.
only 1 loop in pro*c or struture of objects or memory allocation/deallocation procedure in c, can kill whole programm.
-
Oh, hoppla.
This sounds like Shakespeare 'much do about nothing';
You are right, if you start a new project you look in a global scope.
But in a running project if a module/program works to slow
you have to ask yourself if you have choosen the right tool/Language for the Solution.
Sometimes you have to deal with the detail-area very much.
Orca
-
It's correct. But...
In this case u should ask another question. Like:
"how i can use pl/sql (or bla, bla...) if i have ... problems with perfomance.
or i have to use another ways for solve this problem ?"
Rule --> if u going to get right anwer u have to ask right question.
------------------------------------------
NOTE: ur question about ALTER TABLE MOVE - right question.
[Edited by Shestakov on 05-02-2002 at 11:01 AM]
-
Yes indeed;
You must ask the right questions to get the right anwers;
Orca
-
Hi ,
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
Mah@rac
-
There are three issues here. First any valid solution in 2002 might no longer be a valid solution 14 years later given that we are on 12c now and 8i hasn't been supported in 8-9 years. Two the OP probably hasn't logged onto this site in years, and might no longer have access to the code that they had been using. Finally, you asked a new question based on a dead thread.
If you are filling up your temp table space, you are probably doing a large disk sort. Is it possible that you have a cartesean product? If not could adding an index with the columns that you are filtering on and the sort order that you want work? No one can help you with your issue, unless you provide some details.