Using stored procedures for inserts/updates

hi,

Assuming that i have different applications having anywhere between 5-100 tables. Will be better if i use stored procedures to insert into/update these tables or should i use insert/update statements directly from the front end. the fron end can be VB/ASP/JSP. can anyone please advice me on what will be the disadvantages of stored procedures ?

satish

why would you wanna do that?

hi,

suppose if i have a transaction table with 1 million rows. If i call an insert stament from VB, the statment will be parsed each time because the values will defer. but if i write a stored procedure to do the inserts, i pass all the values to the proc which already has a pre-compiled insert statement and uses bind variables to insert into teh database. wouldn't it be better in terms of performance. or will calling the stored proc have a big overhead ? what will be the performance gain ?

satish

I dont understand why the statement would have to parse 1 million times with VB, VB can use bind variables with no problems
Also inserting 1 milliong rows from front-end tool is not normal, that sounds more like a batch job which would fit in PL/SQL. Do you insert 1 millions so often that make you consider this possibility? The only time I have seen an application use pure stored procedures for transaction was due to security issue, all the code were wrapped

Tom Kyte has a very nice phrase, if you can do it with SQL do it, if not PL/SQL, if not Java, if still no then PRO*C

hi,

i will not be inserting a million rows daily. it has approximately a million rows. let's say i have a front end from which users enter 1000 transactions a day. if different users are using diferent connections, will tbe bind variables give me the same performance ? in my application, the same user does not repeat the query. there may be connections inserting into the same table. will bind variables it still work ? can u give me some disadvantages(like memory usage ?) of stored procs if any ?

satish

you code the queries in VB not the users so no matter from what/where/which connections the SQL are always the same therefore you can perfectly reuse the queries with bind variables?

PL/SQL is simply slower and mind you there arent many good PL/SQL programmers around (at least in the country I am)

There are pros and cons to PL/SQL approach. If you ask Steven Feuerstein (undoubtably one of the top PL/SQL gurus) he'll advocate encapsulating all data manipulation actions into PL/SQL modules.

Bind variables are not an isue here - you may or may not use bind variables both in PL/SQL procedure or in your client application.

One of the arguments against encapsulating every DML statement into a PL/SQL procedure might (probably) be performance, as pando has stated. Many unnecessary context swithes from PL/SQL to SQL and back.

One of the arguments for PL/SQL approach would probably be a higher level of abstraction and much higher level of flexibility.

Howevere I agree with pando that there aren't many large production systems where every single SQL is encapsulated in a PL/SQL objects.

Another problem with using stored procedures is the lack of portability. If you want to switch to something like SQL Server or DB2 you're making life very difficult for yourself.

Just a thought :)

Pando, you quoted The Diety himself, then proceeded to contradict him. Mind you, I'd never seen the quote before, but it is wonderful, although I'd bag the whole Pro*C thing and substitute VB :).

As Mr Hunter pointed out..

Quote:

---

One of the arguments for PL/SQL approach would probably be a higher level of abstraction and much higher level of flexibility.

---

These are incredibly important points!

However, as for the performance issue, I don't see how you would plan on getting better performance elsewhere. The only time you get context switches are when you are doing SQL..PL/SQL..SQL..PL/SQL. In VB, it would be SQL..VB..SQL..VB. But in *that* case, instead of context switches, you have trips across the network to get to the database, which is worse. (note that I am assuming the PL/SQL engine is on the server and ignoring such things as SQL*Forms, etc.) Also note that PL/SQL has the bulk-processing statements that can greatly minimize those context switches.

Why would you bring back a hundred thousand rows to process in PL/SQL when you can do it in a single SQL statement? Likewise, why would you send ten thousand rows back across the wire to VB when you can do it in PL/SQL?

Performance-wise, you can't beat PL/SQL. Period.

As for large systems having all their SQL completely wrapped in PL/SQL. I have now written 2 such large systems. Unfortunately I have also worked on some other large systems where this was not the case and they have been *much* worse. There are many, many advantages to wrapping your database in an abstraction layer of packages.

Do it if you can!

RANT

Finally, as for portability... give it up! Portability of code is a wild goose chase. I don't have time for the full argument here, but any sane person *has* to realize that there are more differences between the database vendors than similarities. Okay, now that Oracle has *finally* decided to adopt the ANSI*92* standards (almost 10 years after the fact), it is finally *possible* to write portable SQL. However, that is *only* if you:

- Match the naming restrictions of all platforms
- Don't call any SQL functions (they all have different names)
- Don't use any hints
- Don't use any dblinks
- Don't use any triggers (defined differently on each)
- Don't use bitmap indexes, or IOTs or clusters or hash indexes
- and many, many more restrictions

You would have to create a database with such minimized functionality that, IMHO, it would not be *worth* converting to any other platform. Not that it would matter, with the resulting poor performance of said database, the project would likely fail anyway;). Come on people! These products *thrive* on their performance-enhancing modifications to the standard. Not using PL/SQL when you are most likely already using most of these other functionalities is just plain foolish at best and application self-destruction at worst.

I'd also have to ask: What exactly have you done to ensure that replacing the middle tier is as easy as possible? How easy is it going to be to re-write everything in LISP? How much easier was it to convert from a fat client to the internet? So why should the database...THE biggest performance hog of them all!!... Why should *IT* be the layer that has to be handcuffed to satisfy your mis-directed portability aspirations? As a matter of fact, it is in the *database* where every opportunity for performance enhancements should be, nay, MUST be explored. In most applications, you can run every single line of code (minus database calls) in the *entire* application and be done inside 15 minutes. While in these same applications there is usually more than one statement that can *by itself* take over 15 minutes. Face it, the most important performance enhancements belong in the database. If you have to write code that can *never* be ported in order to gain that performance, then that is the price that must be paid. Of course, that is a little extreme, as almost *anything* can be converted with enough time. But the idea that 'we don't want to make things faster or better for the database because it would be more difficult to convert' is total hogwash. There are a *lot* of things to worry about when building an application. Portability of the database layer doesn't even register on my radar.

/RANT

Now, on a slightly calmer note, it should be noted that if your database is wrapped in an abstraction layer, it *is* easier to change things inside the database. The flexibility that is real (unlike that of portability) is the flexibility to change the database itself. To merge tables; split tables; move tables; change indexes; change security, change SQL, etc. With a database that is properly wrapped in an abstraction layer, all these things are possible. If, on the other hand, there are 3 primary applications as well as a dozen downstream 'feeder' applications that *all* contain raw SQL that directly references your tables, columns and indexes, then changing/optimizing the database itself is damn near impossible.

So like I said, if you can build the abstraction layer - do it!

Sorry, I feel better now,

- Chris

"Hallelujah! Holy S***! Where's the Tylenol"
- Clark W. Griswold

I hear what you're saying about portability, but I don't think it can be dismissed so easily. It really depends on the nature of the application.

I agree that batch processing will almost definitely be written as stored procedures in whatever language your DB uses. Performance makes this a must.

As for front ends, the majority of the SQL in web applications is trivial. I've been invoilved designing and coding applications that run well against Access, SQL Server and Oracle, depending on the scalability required. This would have involved much more work if I had attempted to use abstraction layers.

Just a thought.

Cheers!

chris that quote if I remember right is in both Tom's book and website

and I have read about it quite a few times :D

Quote:

---

Originally posted by chrisrlong

As Mr Hunter pointed out..

Quote:

---

One of the arguments for PL/SQL approach would probably be a higher level of abstraction and much higher level of flexibility.

---

---

Any day you can take credit for jmodic's work is a good day! http://www.theunholytrinity.org/crac...grinbounce.gif

Quote:

---

Originally posted by marist89

Quote:

---

Originally posted by chrisrlong

As Mr Hunter pointed out..

Quote:

---

One of the arguments for PL/SQL approach would probably be a higher level of abstraction and much higher level of flexibility.

---

---

Any day you can take credit for jmodic's work is a good day!

---

Doh! :D

My apologies to both of you ;)


And as for the portability:

Quote:

---

Originally posted by TimHall
I hear what you're saying about portability, but I don't think it can be dismissed so easily. It really depends on the nature of the application.

---

I completely agree. There are specific types of applications where portability is the key. My concern is that that *most* people simply remember that 'somebody once said something about PL/SQL being bad for portability, so I guess we don't want PL/SQL'. In *most* applications, PL/SQL and abstraction layers are very, very important and portability is an afterthought at best. Of all the business-critical internal applications I've written or consulted on, I can't think of one where portability was a viable concern. So the point most people should take with them is that 'unless it is a stated requirement to make the system portable, do not forsake any other functionality in the name of portability'. That would be the starting point. Exceptions always exist, of course, but they are argued on a case-by-case basis.

Quote:

---

Originally posted by TimHall
As for front ends, the majority of the SQL in web applications is trivial.

---

It all depends on the App. The current application I'm at is off the charts in terms of volume of traffic, complexity of SQL, amount of data, size of reports... everything! And I have to optimize the damn thing with *no* abstraction layer!! Instead, we have tons of SQL strewn throughout tons of files in multiple applications in multiple languages. Converting to another database is actually *harder* without an abstraction layer because I would have to search out SQL which is often dynamically built in different languages, change *all* of it and then update *all* of these apps at the same time. If I had an abstraction layer, however, I could possibly keep the interface to the layer the same while pointing all the internals to the new engine. Not easy, but possible. If not, I would simply have to find and change a bunch of simple procedure calls, which is *much* easier than finding and re-writing dynamically-generated SQL in some obscure language. In the current situation, conversion is pretty much impossible. Unfortunately, so is changing even a single column name. Again, without the benefit of an abstraction layer, *everything* is more difficult, if not impssible.

So again, I don't disagree that portability *can* be accomplished and that sometimes it *is* necessary. *Most* of the time, however, it is neither realistic nor necessary. So again, unless it is a requirement of the system, there are usually much more important things to worry about.

- Chris

[Edited by chrisrlong on 12-19-2001 at 04:46 PM]

There were some quotes from Tom Kyte's book and some discussion about SQL application portability in this thread, which reminded me about another Tom's mantra that can be found (if I remember correctly) in the first chapter of his book, and goes approximately like this: "There is (almost) no such thing as an *optimized* and portable SQL code". True, there are standards about SQL that most vendors follow (more or less), yet the internal processing of those SQL statements inside RDBMS is very different from vendor to vendor.

For example, read consistency or locking mechanism in Oracle is totaly different than it is in the SQL Server. So unless the whole application is realy the most trivial one, the developer must be very aware of the target database the application will use if the optimal performance is one of the goals.

It's a gross generalisation but, in it's simplest form you end up with one of two choices:

1) Take the approach that the application will look similar for each engine, but will be serviced by different underlying SQL & stored procedures.

2) Use the lowest common denominator so the application needs virtually no modification for different engines.

Neither choice can be considered more or less valid than the other depending on the REQUIREMENT.

chrisrlong seems to be saying, don't dismiss PL/SQL APIs just because someone mentioned portability issues one. I agree but, playing devils advocate I would say, don't assume you should use PL/SQL APIs until you find a reason not to.

You should arrive at the same conclusion whichever startpoint you use.

You're cursed if you do and you're cursed if you don't :)

I'm sorry, but I just can't let this one drop :D. Too many people are easily swayed by such things. They 'worry' about using PL/SQL or some other functionality because of things like this.

In it's simplest form, the way I see it is:

1) Portability is a requirement. In this case, you must make the choices stated by TimHall above.

2) The more STANDARD case is that portability is irrelevant. My point is that for most of the business-internal applications being written, which is the Lion's-share of the business, portability is pointless. There is no half-way here. Either you take advantage of the 1000 performance-enhancing differences of the engine or you don't. The *vast majority* of applications *need* these differences.

So the point is that unless portability is a stated requirement, forget about it. Trust me - you will *need* to use all the 'extras' that Oracle has to offer and you will thank me later if you build a nice abstraction layer now. :)

- Chris

Chris. Don't have a cow man!

These decisions are never made on a single issue. As I said, I don't disagree with you, but I think it is too easy to say always use it.

I'm currently in a position where I'm advising a group of VB programmers about improving performance on a system. One of the first suggestions I made was to use PL/SQL APIs for most of the functionality. Even the batch processing is currently done in VB. Unfortunately the developers involved have very little PL/SQL experience. This means they either take time to learn it, get someone in to do it for them or I do it. As none of these options are possible at this time they are carrying on with what they have. Yes, some concessions have been made but essentially the decision has been made to stick with an all VB approach. You can lead a horse to water etc.

You gotta larf!

Quote:

---

Originally posted by TimHall
Chris. Don't have a cow man!

---

:D

Quote:

---

Originally posted by chrisrlong

Quote:

---

Originally posted by TimHall
Chris. Don't have a cow man!

---

:D

---

Does that mean I'm forgiven for my wicked ways? :)

Just for clarification....

I was 'speaking' more to the 'audience' in my replies than directly to you. I'm certain that you had reasons for the approaches you took. I am just looking out for more of the novices who are easily 'put off' from using wonderful things like abstraction layers because of mostly non-applicable concerns for things like portability. I just wanted to make sure that they had all the proper context with which to frame our specific debate.

'Don't have a cow, man'....

...cracked me up. :D

T'was a pleasure sparring with you, my good man. I look forward to 'having at it' again sometime ;P

- Chris

Don't worry, I wasn't taking it personally (best call off the hitman :) ).

A couple of people mentioned performance. Take a look at the example below. I ran the two loops a few times and these results seem pretty consistent on my machine:

This example is worh looking at:

CREATE TABLE table1
(id NUMBER(10),
col1 VARCHAR2(50),
col2 VARCHAR2(50));

CREATE OR REPLACE PROCEDURE Table1_Insert
(p_id IN table1.id%TYPE,
p_col1 IN table1.col1%TYPE,
p_col2 IN table1.col2%TYPE) IS
BEGIN
INSERT INTO table1
(id, col1, col2)
VALUES
(p_id, p_col1, p_col2);
END;
/

SET TIMING ON

SQL> DECLARE
2 v_col1 table1.col1%TYPE := '1234567890';
3 v_col2 table1.col2%TYPE := '12345678901234567890';
4 BEGIN
5 FOR i IN 1 .. 10000 LOOP
6 Table1_Insert(i, v_col1, v_col2);
7 END LOOP;
8 END;
9 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.02
SQL>
SQL> ROLLBACK;

Rollback complete.

Elapsed: 00:00:00.05
SQL>
SQL> DECLARE
2 v_col1 table1.col1%TYPE := '1234567890';
3 v_col2 table1.col2%TYPE := '12345678901234567890';
4 BEGIN
5 FOR i IN 1 .. 10000 LOOP
6 INSERT INTO table1
7 (id, col1, col2)
8 VALUES
9 (i, v_col1, v_col2);
10 END LOOP;
11 END;
12 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.08
SQL>
SQL> ROLLBACK;

I know it's a crap test, but it's interesting.

Interesting test, but it simply highlights how easy it is to write ineffecient code in *any* language.

*If* one actually wanted to do what you are proposing, the most efficient solution would be:

Code:

---

PROCEDURE xTEST
IS
        TYPE ID_T        IS TABLE OF CRL_Table1.ID%TYPE       
                INDEX BY BINARY_INTEGER;
        TYPE Col1_T        IS TABLE OF CRL_Table1.Col1%TYPE
                INDEX BY BINARY_INTEGER;
        TYPE Col2_T        IS TABLE OF CRL_Table1.Col2%TYPE
                INDEX BY BINARY_INTEGER;

        l_ID_T        ID_T        ;
        l_Col1_T        Col1_T;
        l_Col2_T        Col2_T;

        l_Col1 CRL_Table1.col1%TYPE := '1234567890';
        l_Col2 CRL_Table1.col2%TYPE := '12345678901234567890';

BEGIN

        FOR i IN 1 .. 10000 LOOP
                l_ID_T        (i)        :=        i                ;
                l_Col1_T        (i)        :=        l_Col1;
                l_Col2_T        (i)        :=        l_Col2;
        END LOOP;

       
        FORALL i IN 1..l_ID_T.COUNT
                INSERT INTO CRL_Table1
                        (
                        ID                ,
                        Col1        ,
                        Col2
                        )
                VALUES
                        (
                        l_ID_T        (i),
                        l_Col1_T        (i),
                        l_Col2_T        (i)
                        );

END;

---

(As always, a 3-character tab makes everything line up beautifully:))

This executes in less than a second, at least on my server.

As I said, PL/SQL has the tools to greatly reduce, or in this case, eliminate the context switches.

- Chris

I think you've missed my point, or I've not made my point clear :).

I wasn't describing an ideal bulk load. It was just a way to show that 10,000 calls to a procedure doing the insert takes more processing than 10,000 calls using SQL alone. In this context the bulk load is not relevant.

A better example would have been 10,000 people requesting a single insert from a web page simultaneously. The procedure approach does take fractionally more time and resources. Not noticable on a single hit, but still real and affecting potential scalability???

That's not a reason for not using APIs. I agree there are a number of benefits but, there are certain overheads.

Quote:

---

[i]Originally posted by chrisrlong
(As always, a 3-character tab makes everything line up beautifully:))

- Chris [/B]

---

Don't talk to me about formatting. IE6 centers everything on this forum. Some dodgy HTML in the forum or code in IE6 :)

I found the quote I was looking for by Tom Kyte:


I actually list this in my book in order:

i) sql
ii) plsql
iii) java stored procedure
iv) c

sql = fastest, easiest, least amount of code to maintain
plsql = best integration with sql, fast, easy to code, best in the database
java = do all of the things plsql cannot do (eg: send an email, write a file)
c = that which java cannot do or java does too slowly.

To be fair, he does love the PL/SQL API programming model in a big way. See the "What you DO GAIN with this approach" section at:

http://asktom.oracle.com/pls/ask/f?p...0_P8_DISPLAYID,F4950_P8_CRITERIA:499097979114,%7BPL%2FSQL%7D%20and%20%7BSQL%7D

Cheers :)

Quote:

---

Originally posted by TimHall
A better example would have been 10,000 people requesting a single insert from a web page simultaneously. The procedure approach does take fractionally more time and resources. Not noticable on a single hit, but still real and affecting potential scalability???

---

So I was being somewhat deliberately thick in not addressing your core issue. I apologize. I wanted to show how PL/SQL can be used to solve the 'larger' performance issue of a database.

In this case, as you've pointed out, there *can* be performance impacts, albeit imperceptable at the individual call. So yes, it is conceivable that if everyone writes optimal SQL code, PL/SQL code and middle-tier code, you *can* get slightly better performance by calling the SQL directly.

However, let's step back and look at the larger perspective.

In most development teams, you unfortunately have a bunch of middle-tier code jockeys writing all 3 tiers. To be completely honest with you, most middle-tier programmers have no business writing SQL - they don't do it well enough. So, sooner or later there are performance problems, usually large ones. So, they call in somebody with a clue about SQL to 'fix' things. As such a person, would you like to look through tons of code in VB, C, java or some other middle-tier language? Or maybe all of them at the same time? Or would you rather go straight to the database with complete access to all the SQL and have completely free reign in actually fixing things?

If the SQL exists only in the database, it is much easier for 'real' SQL programmers to fix things, both the big and the small. The gains that can be made in SQL are 'order of magnitude' improvements. For example, the bulk-load I re-wrote above was over an order of magnitude improvement, most likely it was 2 orders of magnitude (<600 msec). *These* types of improvements *far* outweigh the doubling of time that your example showed.

So, in the larger context of database performance, I will go for the PL/SQL abstraction layer almost every single time. Then I will have the freedom to truly fix a broken database or even just to find and fix any and every bad SQL there is. Without an abstraction layer, my job becomes an order of magnitude more difficult, at least.

- Chris

I can't argue with that!

Quote:

---

Originally posted by TimHall
Don't talk to me about formatting. IE6 centers everything on this forum. Some dodgy HTML in the forum or code in IE6 :)

---

When I switched to IE6 I lost a couple of hours trying to find out why all of the sudden this forum has become almost unreadable because of the centered lines, and where the hell in the IE6 settings or in the registry I can changed that damned thing. Needless to say I haven't found that magical switch, argh...

Quote:

---

Originally posted by jmodic

Quote:

---

Originally posted by TimHall
Don't talk to me about formatting. IE6 centers everything on this forum. Some dodgy HTML in the forum or code in IE6 :)

---

When I switched to IE6 I lost a couple of hours trying to find out why all of the sudden this forum has become almost unreadable because of the centered lines, and where the hell in the IE6 settings or in the registry I can changed that damned thing. Needless to say I haven't found that magical switch, argh...

---

After futzing with it for a couple hours myself, I found the perfect fix for IE6 and this forum. ( http://home.netscape.com/computing/d...html?cp=hophb2 ) http://www.theunholytrinity.org/crac...b/anym/win.gif

Yep, that patch fixes the mentioned bug...

Made me laugh a lot!!!

I didn't read the link. I just clicked it. I nearly p*ssed myself when I was on the netscape site.

Well done!