DML generated at front-end vs. Insert - Update - Delete performed by stored procedures

Question

Im wondering about the real advantage of performing dml commands (inserts, updates, deletes) in the database via stored procedures for simple CRUD applications. Whats the beneffit with that appoach over just using some generic procedure in the front-end that generates the dml commands?

Thanks in advance.

Answer 1

Visual Foxpro? For single record updates, I doubt that there is any performance benefit to SP's. The effort to maintain them certainly trumps any marginal performance gain you might get.

The stored procedures gurus might have thoughts on other benefits besides performance.

Answer 2

One of the main benefits is control of access. The application only has EXECUTE permission and no direct data access permission. This way the administrator can inspect the procedures and ensure they use proper access paths (ie. indexes). If the application has direct access to the tables, developers will write crappy SQL and bring down the server.

Answer 3

Performance wise you are unlikely to see any benefit. I think it is more about security of the database.

The advantage of stored procedures in any case is the ability for the DBA to control the security access to the data differently. It often is a preference call by the DBA. Putting the CRUD access to the server in the server means they control 100% access to the server. Your code has to meet their stored proc "API".

If you include the logic in the Visual FoxPro code via a remote view, cursor adapter, or SQL Passthrough SQLExec() it means you have 100% of the code control and the DBA has to grant you access to the database components, or through the application role your code would use for the connection. Your code might be a bit more flexible with respect to building the CRUD SQL statement on the fly. The stored proc is going to have to handle flexible parameters to build the statements generically.

Rick Schummer

Answer 4

For stored procs:

• Remove SQL injection risks
• Encapsulation (security, treat them like methods)
• Allow client code to change (same API to database)
• Deal with increased complexity (eg insert parent an child in a SQL-side transaction)
• Easier to manage transactions