Home » SQL & PL/SQL » SQL & PL/SQL » Pro *c Vs Stored procedures
Pro *c Vs Stored procedures [message #184850] Fri, 28 July 2006 05:50 Go to next message
vinu_vkumar
Messages: 45
Registered: March 2005
Member
Hi All,
I have a doubt regarding the performance of two scenarios listed below.

1. Write an insert statement from Pro *c code directly that will direcly insert into the database.
2. Invoke a stored procedure precompiled, passing parameters to it in the database server that has the insert statement built in it.

Performance wise is there advantage in either one of the methods or are they same in the way they function. If one is better than the other can you give me reasons supporting the same.

Regards,
Vinu.
Re: Pro *c Vs Stored procedures [message #184853 is a reply to message #184850] Fri, 28 July 2006 05:57 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
#1 is better performance wise, because you bypass SQL <-> PL/SQL context switches. Additionally you can use FORALL and host arrays to achieve a "bulk insert" via Pro*C. The downside is maintainability, but odds are if you are changing a parameter you probably have to change the Pro*C code anyways.

http://www.dbasupport.com/oracle/ora9i/bulk_binds1_1.shtml

[Updated on: Fri, 28 July 2006 05:57]

Report message to a moderator

Re: Pro *c Vs Stored procedures [message #184854 is a reply to message #184853] Fri, 28 July 2006 05:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd like to see some figures and a worked example to support this please.
icon7.gif  Re: Pro *c Vs Stored procedures [message #184855 is a reply to message #184850] Fri, 28 July 2006 06:06 Go to previous messageGo to next message
vinu_vkumar
Messages: 45
Registered: March 2005
Member
Hi wagnerch,
Assuming that I am not doing any bulk insert of that sort. My application is pumping data at the rate of 100 transactions per second. Each insert is committed individually.

How do you think 1 is better than 2?

Mr. JRowbottom, I hope that your quote was not intended to me Smile .

I would request all to show some reasons supporting this situation.

Warm Regards,
Vinu.
Re: Pro *c Vs Stored procedures [message #184862 is a reply to message #184854] Fri, 28 July 2006 06:21 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:60122715103602

I think I have already supported my comment.
Re: Pro *c Vs Stored procedures [message #184864 is a reply to message #184855] Fri, 28 July 2006 06:27 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
Again, I have already stated why I think my comment is true. If it isn't then feel free to point it out. It seems like a no brainer to me...

I can do the SQL myself directly via Pro*C. Or I could involve yet another component of the database (PL/SQL), to do the SQL for me.

Why would I add a another layer, unless there was some benefit here? To me it would be nothing more than an additional CPU cost.
Re: Pro *c Vs Stored procedures [message #184875 is a reply to message #184862] Fri, 28 July 2006 07:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To be fair, I see a lot of discussion about context switching between pl/sql and sql, and very little mention of C or Pro*C at all.

In Tom's own words (I know - argument from authority. So sue me)
Quote:

Context switching is a fact of life in all "multiple language environment". It is an implementation detail that wouldn't really be documented - it is just a fact of life.


Why do you fel that this applies when switching from Pl/Sql to Sql, but not from Pro*C to Sql?

It should be fairly straightforwards to sort out which of the 3 options (Pro*C, Pl/sql and bulk inserts) is quicker, and if I knew more about C than how to spell it, I'd do it myself.
Re: Pro *c Vs Stored procedures [message #184881 is a reply to message #184875] Fri, 28 July 2006 08:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
A thread from {http://asktom.oracle.com/pls/ask/f?p=4950:8:3219838299064291075::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1138433172691]AskTom[/url] deals with just this question, and the answer is "It depends"
Re: Pro *c Vs Stored procedures [message #184932 is a reply to message #184881] Fri, 28 July 2006 15:17 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
JRowbottom wrote on Fri, 28 July 2006 09:30

A thread from {http://asktom.oracle.com/pls/ask/f?p=4950:8:3219838299064291075::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1138433172691]AskTom[/url] deals with just this question, and the answer is "It depends"



Great post JW. In case some people's browsers have a problem with that link, try this.

http://asktom.oracle.com/pls/ask/f?p=4950:8:3219838299064291075::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1138433172691
Re: Pro *c Vs Stored procedures [message #184944 is a reply to message #184881] Fri, 28 July 2006 17:09 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
Oddly enough I have actually read that thread. Personally I still think PL/SQL, if you are always calling from Pro*C anyways, is a third layer that doesn't need to be there.

My assumptions were the PL/SQL code would always be called from Pro*C, but perhaps that is incorrect?

Personally, I advocate PL/SQL hands down if the data is already in the database. If the data is on a filesystem then I would suggest writing a loader (not using sqlldr -- it is great for one shot deals, but I have never found it very usable from a production standpoint). If it is high-volume then I would use Pro*C (FORALL & host arrays). If it is low-volume then I would use Perl.
Re: Pro *c Vs Stored procedures [message #185084 is a reply to message #184850] Sun, 30 July 2006 23:25 Go to previous messageGo to next message
vinu_vkumar
Messages: 45
Registered: March 2005
Member
Thanks Guys,
That thread was real interesting one. Thanks again for the time spent by all.

Regards,
Vinu.
Re: Pro *c Vs Stored procedures [message #185456 is a reply to message #184944] Tue, 01 August 2006 18:27 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
wagnerch wrote on Fri, 28 July 2006 17:09

Oddly enough I have actually read that thread. Personally I still think PL/SQL, if you are always calling from Pro*C anyways, is a third layer that doesn't need to be there.

My assumptions were the PL/SQL code would always be called from Pro*C, but perhaps that is incorrect?

Now you have completely lost me.

The original question was about the likely performance difference between the same INSERT statement in PL/SQL and Pro*C, although admittedly it was worded ambiguously so that it could mean that the two approaches did different things, but I'm assuming that they both do the same thing in order for there to be any sense in comparing them.

First you said Pro*C would be faster than PL/SQL because it avoids the context switch between SQL and PL/SQL, although as JR mentioned that rather ignores the context switch between SQL and C, then you added a link to an article about bulk processing in PL/SQL although AFAIK Pro*C also has arrays and bulk processing so I'm not sure what point that is making.

Then when JR asked for a demo you linked to an AskTom thread that explained how PL/SQL and SQL are two separate languages and there is an overhead to switching between them, and claimed you had made your point. I assume you are trying to tell us that SQL and C are the same thing and thus avoid call overheads.

Now you are saying (if I have this right) that in a comparison between Pro*C and PL/SQL, you would have to call PL/SQL from Pro*C anyway and so Pro*C is by definition twice as fast.

Just checking...
Re: Pro *c Vs Stored procedures [message #185461 is a reply to message #185456] Tue, 01 August 2006 20:56 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
I sent in the link about bulk binds in PL/SQL with my initial response. And the point of the link was to present a general introduction to the context switching concept, not specifically to point out bulk binds.

Regarding the Ask Tom link, the point here again was to point out the context switching & the fact that it runs as p-code in a VM. It would seem logical to assume that PL/SQL has the same cost of parsing and executing statements as Pro*C does, because they are passed to a common SQL engine. But they clearly are different beasts.


Theoretically, there is more overhead doing it in PL/SQL just basing it on the fact that it is compiled p-code which still interpreted (p-code sitting on top of Oracle's PL/SQL VM sitting on top of native code).


My general point is if you are using Pro*C already (getting the file from the filesystem and loading it into the database) then don't waste your time with a stored procedure, it is just unnecessary overhead.

I think the assumptions and conclusions are fairly logical, and a test case is totally unnecessary.

Would I advocate not using PL/SQL? Hell no!
Would I advocate not using Pro*C? Hell no!
It just depends on what your doing. I would say 98% of the code that I have worked with is PL/SQL, Oracle Forms, Perl, or Java. 2% is Pro*C, and it is purely due to interface constraints or where I really need speed.

I have a data interface built on Pro*C and host arrays, and it loads 40 million rows worth of data a day. No sweat, and it utilizes minimal CPU time (10~15%).

If you have further insight then please share it.
Re: Pro *c Vs Stored procedures [message #185471 is a reply to message #185461] Tue, 01 August 2006 22:52 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
If the question was about loading from file, then Pro*C would be faster than PL/SQL because it can do direct file I/O, not for any reason to do with context switching. Both have to do context switching anyway so the subject is rather irrelevant. However the question was not about loading from file (perhaps Vinu can confirm), in which case the whole subject of file I/O is rather irrelevant.

Yes it might be more efficient to call SQL directly from Pro*C, as opposed to calling SQL from PL/SQL from Pro*C, if that is the only choice. Perhaps Vinu can confirm how the application accesses the database to launch its 100-commits-per-second attack (btw Vinu, you need to find a way of not committing 100 times per second).

Re: Pro *c Vs Stored procedures [message #185872 is a reply to message #184850] Thu, 03 August 2006 23:47 Go to previous messageGo to next message
vinu_vkumar
Messages: 45
Registered: March 2005
Member
Hi William,
Excuse me for not responding the past 3 days, was on leave .

Mine is a telecom roaming solution and you would understand that each subscriber so to say would have his own data in the tables and the logic for each subscriber runs independent of the others. Hence it becomes important for me in the context of his logic to commit his transaction when ever that happens, in this case it happens around 100 transactions per second. Just to add that the same subscribers transaction does not repeat within that time though. Hence commiting is logically required for me.

Coming back to the original question. I just wanted to know if there was any difference between writing an insert in a pro* C file and a pro* C file calling a procedure to do the same.

Just to elaborate on this (Im not good in pro* C so excuse me for the algorithm for it). Assume there is a Location update message coming for a subscriber and assume we allow the subscriber to latch on a roaming network(mobile network), now when this happens you need to store a record saying that the subs is allowed on that network.

1. Direct insert here in Pro* c

insert into allow values();
commit;

2. Indirect insert using a pl/sql
call pr_allow(imsi,vlr);
commit;

create or replace procedure pr_allow(imsi varchar,vlr varchar)
as
begin
insert into allow values();
end;
Re: Pro *c Vs Stored procedures [message #185889 is a reply to message #185872] Fri, 04 August 2006 01:23 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
In that case you are comparing calling PL/SQL with not calling PL/SQL, and I would expect not calling PL/SQL to be faster. There are some advantages of self-documentation and logic encapsulation when using a procedure, but I guess for sheer speed you won't beat embedding the SQL directly in the app. Why not test it and see what difference it makes?

Regarding the commits, can't you batch them up somehow and process 100 of these logical transactions at a time, committing once at the end? I think 10g R2 has some features to optimise this though - I'll check when I have time.
Re: Pro *c Vs Stored procedures [message #185890 is a reply to message #184850] Fri, 04 August 2006 01:32 Go to previous message
vinu_vkumar
Messages: 45
Registered: March 2005
Member
Thanks William,
I rather test it as you said.

And about batch commit, I cannot batch them into one because you never know when the next request is going to come. And your new request must use the previously commited data into consideration for its process, I just gave a sample insert as the only thing happenning but in real time there are other parameters that get updated and this has to be used by future requests, not just for that subscriber in fact this commit affects any other transaction that happens after it. So commit looks inevitable in my case for each transaction.

Regards,
Vinu.
Previous Topic: Problem in number selection
Next Topic: SELECT qry doubt ...
Goto Forum:
  


Current Time: Sun Dec 04 04:53:42 CST 2016

Total time taken to generate the page: 0.05976 seconds