Using stored procedures for inserts/updates

[satishpc]

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

[pando]

why would you wanna do that?

[satishpc]

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

[pando]

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

[satishpc]

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

[pando]

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)

[jmodic]

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.

[TimHall]

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

[chrisrlong]

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..

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

[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 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!