Any reason why a Java JDBC crud operation like...
static String stmt = " insert into emp (col1, col2) values (?,?) ";
would be slower, faster, or un-noticeable performance difference versus calling a stored procedure via jdbc, and then having that db stored procedure do the actual insert? I'm wondering what's the point of pushing down the inserts
like that into db level stored procedures, as opposed to just have the JDBC do it directly. What are the performance
and even design considerations? We're currently using SQL Server 2000.
Performance differences between CRUD Prepared Statements and a similar stored procedure are probably negligible. The Prepared Statement SQL is much easier to maintain, though, because you can externalize all of it into a configuration file.
Generally, I recommend only using stored procedures for strange or complex database operations, where the extra work of maintaining the stored proc is worth the improvement in performance.
Google "SQL Injection".... 'nuf said.
No matter how well you THINK you've written your code SQL injections vulnerabilities are likely to occur. Parameterized Stored Procs prevent this.
If you encapsulate all you're data access logic in Stored Proces and only grant the user rights to run the Stored Proc (and not the tables), you can control how the data is accessed. Stored Procs prevent people from running querries like "SELECT * from CustomerCreditCards" and "DELETE FROM ORDERS".