Using stored procedures for inserts/updates
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 31

Thread: Using stored procedures for inserts/updates

  1. #1
    Join Date
    Jul 2000
    Location
    India
    Posts
    60
    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

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    why would you wanna do that?

  3. #3
    Join Date
    Jul 2000
    Location
    India
    Posts
    60
    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

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  5. #5
    Join Date
    Jul 2000
    Location
    India
    Posts
    60
    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

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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)

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  9. #9
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  10. #10
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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!
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width