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 ?

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.

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

Thanks for the reply.

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

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!

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.