Prepared Stmt or Stored Procedure ?

[Srinivasa Raghavan]

Can any one tell Whichis best either Prepared Stmt or Stored Procedure ?
What are the advantages of one over the other ?

[Nikhil Vasaikar]

Hi,

It all depends on how much data you want to play with. For huge datasets, stored procedure is a good iddea, having all the tables properly indexed incase if you are doing any selects. For small set of Data you could probably use a PreparedStatement. If you are executing an SQL for different sets of values , you can use the addBatch()-executeBatch() approach in the PreparedStatement. To summarize, the amount of data will drive your choice.

Regards,

Nikhil

[David O'Meara]

I'm going to disagree, but you'll find as many opinions as people 'round these parts

Personally I never use Stored Procedures unless I need some extremely database specific behaviour, maybe like reporting. The problem with them is that they force you to start pushing your domain logic into the database, and if you're writing a multi-tiered application you could end up with (at worse case) logic spread across your client, middle tier, persistence layer and then smatterings in legacy systems too.

Dave

[nilesh Katakkar]

I guess, now I am going to disagree to prove that Dave indeed is right !There will be as many opinions around as people.

I use stored procedures in most of the scenarios.Especially when working in the corporate and enterprise scenarios, your application is never gonna be the only application to access the data. Do you want to duplicate your data access logic in all those apps ? What if another application is .Net based ? You cannot use your same Java objects there. Now if you don't enforce your rules in the DB, the next application that talks to your data can very well compromise your data integrity.

Enterprise application are all about managing data. What changes is the delivery mechanism. It was client server first, now its web based. Tomorrow there will be some other paradigm. Java is not the last language. Paradigms will change.. data in the database won't. You can use Stored Procs from all your clients - Java based, .Net Based, PRO C or PHP based.

I have seen many Java programs using SQL / Jdbc directly in the java code. If I have to tune that query , then it would mean changing Java class. I can do so in Procedures directly where SQL is more readable, easily tunable and maintainable (as far as maintenance ask DBA's they will agree).

Other thing that people go JDBC way is to have DB independance. Well.. how much really can you acheive that ? Complying to SQL 92 std will only give you basic CRUD things. If you really want to exploit the power of SQL, you will be in need some point to use functionality like DECODE, TO_DATE , NVL, Analytical functions (in Oracle). If you have this SQL in Java JDBC client you are anyway depending on DB. Now add the fact that every db implement concurrency, locking differently. Only Oracle provides the multiversioning and non blocking reads right now. (SQL '05 will provide it with entirely new isolation level called snapshot) It's very difficult to acheive DB independence for truly scalable enterprise apps. And in my experience I have never seen any enterprise application yet changing the DB. MS apps were convereted to J2ee, some J2ee apps were converted to .Net .. some .net apps were converted to j2ee... however DB remained same.

PL/SQL, T-SQL all these languages are built for only one reason - tight integration with SQL and to exploit power of SQL. Use right tool to do right things .. we say that all the time dont we ?

Middle tier - all about application logic,presentation, content delivery mechanisms, integration with 3rd party system using webservices / Message Oriented Softwares.

DB - all about data access / business logic.

Do same things on the middle tier that you used to do in client tier env.
Java is good for computational intesive stuff. Stored Procs are good for SQL intensive stuff, reducing round trips to DB and having centrlized data access logic.

As far as difference between preparedstatement and SP goes, if you gonna do a SELECT / INSERT / UPDATE and your SP's gonna have just one SQL statement then, there's no difference between two. PreparedStatement have the advantage that, it always use bind variables protecting developer from misusing them, however all SQL is dynamic and Hard to read ! SP can use static SQL, explicit cursors / implicit cursors and proper use of bind variables in dynamic SQL.. giving better performance and allowing you to use features that clients paid for.

[Nikhil Vasaikar]

Well I agree that there will absolutely different opinions. Again if I require to update multiple tables based on multiple selects, all this in one go, then I would rather prefer writing this logic to a Stored Procedure. This would also help in maintaining/modifying the logic as the business evolves. I won't have to edit a bunch of Java files.

Nikhil

[David Yutzy]

We write most of our DB routines using Stored Procedures. We found, specifically with SQL Server, that a sort of "temp procedure" is created, compiled, executed, and dropped when using PreparedStatements. On a multi-processor box, quering against 6.5 million records, we see specific spikes to 80%-100% in the CPU's when a PreparedStatement is run, and especially so when 50-100 hits (from a very active webs site).

When using a Stored Procedure, the CPU doesn't even show the activity.

I DO agree with the statements, though, about distributing the Logic into a Stored Procedure. This is generally bad practice. Our SP are very simplistic, meaning, all of the BL is performed in a Java DAO layer, then when we have to touch the DB such as selects, inserts, updates, etc. We have already manipulated the data and simply have to commit or retrieve it to/from the DB.

When I first arrived at my new job, they had hard-coded everything in the SP within SQL Server, including the ODBC connection string. It is not uncommon to have 3,000 line SP's.

Using the DB server as a BL/DB/Processing layer is a horror in just about every respect; but when combined with sound Java practices can be quite beneficial, especially with high traffic web sites where speed is everything. We process over 100 million DB hits a month using Sun ONE AS and SQL Server on a 2 CPU box without so much as a burp in the CPU above 5%.

My 2-cents...