Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    84

    Unanswered: stored procedures Vs Application program

    Can someone tell me which one of these is going to have faster db access - 1. stored procedure ( written in C/ SQL) or an application program ( say written in C ) which is running as a native client.

    2. Does the fact that the SQL SPs run within the database engine have any impact on the performance vis a vis an application running on the same machine ?

  2. #2
    Join Date
    Sep 2003
    Posts
    84
    29 views and no replies.
    I must have asked a very stupid question as this is should be a basic consideration while designing any client-server application using stored procedures.

    Can someone please help.

  3. #3
    Join Date
    Aug 2003
    Posts
    106
    Dipanjan:

    Speed is a consideration for SP but not the ONLY consideration!

    Sometimes one would like to build a SP just to encapsulate a business logic so that there is consistency in applying the business rule.

    Getting to back to your question....

    It depends.

    Scenario A where SP returns a result set containing 1000 rows
    ===============================================
    I would *guess* that SP will be faster eventhough the application is a native client (not a web client and in same domain). Even with native client it is connected via a network to the server. So in this scenario the native client just makes one call to SP and there is just one IO to the database (read SPEED) and the *complete* result set is returned to the app. On the contrary if the app makes a call using multiple queries (to mimic the SP) then there are multiple IOs (read SLOW) to the database

    Scenario B where SP makes just one SQL call and returns 1 row
    ===============================================
    Makes no difference if you code this one sql in the app or SP

    Again this is my *guess* - Hope somebody can dispute or agree to my understanding!

    PS: My understanding of *native client* is that it is in the same domain and is NOT a web client

    You are the creator of your own destiny!

  4. #4
    Join Date
    Sep 2003
    Posts
    84
    Thanks for the reply.

    By native client I meant an application program running on the database server.

  5. #5
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    A sql stored procedure would be compiled to C anyhow and it would also be bound to the database providing all the performance benefits this offers. In addition, they are incredibly easy to develop, maintain and enhance. So, I'm going to stick my neck out and say that the SP should win out all the time!

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If the C application program is running on the same machine as the DB2 database server, I don't think you can go wrong with embedded SQL in an external C program. But I am not certain about which is faster (external C program or SP).

    Many people do not like to run C or Java SP's as non-fenced (because it cause DB2 to crash), so if run fenced, I doubt there is any performance advantage that SP's have.

    Stored procedures do have an advantage if the program is running remotely.

    According to IBM, C SP's are the fastest, SQL is next, and Java is the slowest. This could change in future releases of DB2.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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