SQL generated by 4GLs vs. Oracle stored procedures
We are attempting to adopt programming standards which would mandate the use of stored procedures in our Oracle 8i and 9i databases for data access. Application developers prefer to take advantage of the SQL generation capabilities of IBM's WenSphere and Compuware's Uniface. DBAs feel the security, performance, maintainability, visibility and sharability of procedures stored in the databases outweigh the ease of development with generated SQL. Any related experiences and advice would be appreciated.
Software/Hardware used:
Software/Hardware used:
ASKED:
January 26, 2005 10:24 AM
UPDATED:
January 30, 2005 2:46 AM
Answer Wiki:
This is a classic developmental question, and any contribution is always just a small view of the ongoing debate, external factors often play a larger part. As a general rule it is true that the closer to the database the more easy it is to enforce business rules, it is easier to have a constraint go unviolated if it is defined at the table level rather than at the application level. Stored procs also afford you centralized security, there are lost of resource you can look up which describe this approach. I think it really boils down to answering a few questions:
1 - Do you have the DBA resources do carry out this work now,
and will that resource be there once the project goes live ?
My guess is you'll have a Prod DBA but not a dev one, whilst you'll always need the J2EE developer. You also be tied to Oracle, not a bad choice but tied nonetheless
2 - Does your tools provide adequate and well optimized code?
If WebSphere generates good SQL and doesn't parse 100 times per page then you can use that but I've found that lots of tuning time is taken removing , 100 SQL for 100 lines of display, type issues.
3- Are your developers as good at writting SQL as your DBAs?
Some are some aren't, experienced RDBSM developers are often better than DBAs, again this goes back to the resource ratio you have and how many developers versus DBAs you have. It's pointless putting loads of logic in the DB if you have to teach PLSQL to 20 J2EE staff.
The nicest approach I've come across sees all the SQL stored in XML files, that way both developers and DBAs can read them easily and spot issues quickly. I would then leave the stored proc aspect to areas where large amounts of data are worked on and network traffic would be harmfull. The RDBMS resources can then be used for constraints, indexes, tuning and monitoring SQL, efficient dataloads and that is usually enough to keep them busy. The important thing is that your database is designed well enough so that regardless of the datasource, data remains clean and as close to the business rules as possible.
That's my 2 cents.
Nick
To see all answers submitted to the Answer Wiki: View Answer History.
concur 100% with reply from geester.
To add, PL/SQL is much more pwoerful than anything Websphere or CA tools can churn out.
Yes, adopt a standard, but don’t get fanatical about it, there are times to make valid exceptions to the rule.
Questions to ask:
Does Websphere have the ability to use the database specific functionality (for instance Oracle Analytic functions, or Oracle’s implementation of TRANSLATE or subquery factoring using WITH clause, using Ref Cursors, custom types, DBMS_% packages, etc.) ? Short answer: in IBM’s dreams !
What about when you need some specific functionality to do something special in Oracle (an insurance company client of mine, wanted a function that accepted an adviser’s id and the function was to return the last appointment for the current month, with date and type, and the first appointment for the next month, this would have involved very complex SQL, but with PL/SQL, the function I wrote executed in 10 milliseconds, and it took me 10 minutes to write and test the function, whereas a SQL centric approach may have been rather expensive, involving many joins and possibole performance concerns and having to worry about the skills of production support staff to maintain the complex SQL once I was off site).
Can WebSpehere generate the SQL required for hierarchical queries (CONNECT BY PRIOR, START WITH, ORDER SIBLINGS BY, NOCYCLE (10g) ?) Same answer again.
Can WebSphere / CA generate Oracle hints when hints are to be used ? Perhaps, but probably not.
When you want a generic SQL generator (like Siebel, PSOFT and SAP do), you are dumbing down the database to the lowest common denominator, which means you are really using the database as a glorified dumb storage bin. The database is there to do powerful and meaningful processing, USE IT !
Getting off my soapbox now. Hope that helped:
Regards:
Ferenc
Hi to all,
I answer just for giving 100 points to Ferenc’s answer.
I totally agree with it, in every line and every word !
In my opinion, the so-called “database independent” development leads unevitably to very bad results, at least
in what concerns performance issues.
The “most-common denominator” used for making an application database-independent takes it down to the lowest level, lower than any specific database’s possibilities.
Databases concure to offer better and better results and
facilities, if they all were the same then a single one of
them would suffice for everybody.
It’s a pity not to use Oracle’s wonderful proprietary
additions, in comparison to other databases.
The standard code-generating tools usually do not create
performant code.
At least from my experience, it’s strange to imagine an
organization where the DBA-s SQL knowledge to be wider than
the developers’ !
Usually I think rather the opposite is true in most organizations. And this is somewhat natural, considering
that the DBA performs all of the maintenance work, while
the developers are developing both projects and their own
development skills as well.
Every organization should be supposed to have at least a
small group of developers at the highest possible level of
knowledge, even if the DBA team is also at a high level.
I think no code-generating tool could ever compete a
consciuous development done directly by the development
team, based on both the database capabilities and the
business requirements.
My best regards,
Iudith Mentzel