Greetings,
Here is my question:
There is load balanced Oracle 9i DB, 100.000 hits per day expected.
2 possible ways to work with DB: all SQL in stored procedures or SQL statements in code (C#.NET)
Does anyone know how both ways will influent on performance and maintenance?
Is there information about similar test results online?
Thank you.
I prefer stored procedures over sql code embedded in application code for maintainability and simplicity.
If you can wrap your sql in a stored procedure just do it. It is easier for someone to read the code too.
Performance wise also stored procedures are pre-compiled in the server, so it works better.
Also you separate user interface/middle tier code from database code.
I would agree with skhanal, that using stored procedures are better approach that custom SQL in the your code (.NET)
Also just to add one more performace improvement using stored procedure: In the case of the large database accessing, there is no need to lose time transfering the SQL string when they can be already stored (and compiled) into the DB itself.
For stored procedures only parameters are shipped over the middle tier to db tier.
Also, I would suggest to use packages instead of single stored procedures (functions). Packages gives better object-like approach and grouping the procedures and functions into logical structures.
Greetings :)
Thank you,
My concern is that by design all SQL will be in stored procedures. So it will be about 2000 stored procedures in DB. Does anyone have experience with approach like this? What problems did you have?
There are some serious disadvantages to using database stored code. Namely, it means your application is not portable across RDBMS'. It also means you must maintain two sets of application code -- in this case, your .Net code and your database code.
The other disadvantage that is often overlooked is that platforms such as j2EE and .Net have a lot of functionality for automatically generating SQL on the fly. If you have to work with database stored code, then you may lose this ability.
The advantages are better database code management and performance. (Particularly if you are working with bulk data.)
You can take a middle approach by using temporary procedures when you need to work with bulk data, and otherwise use dynamic SQL.
In Oracle, use packages rather than stored procedures or functions. They allow you to modularise your database code, as well as create types and return result sets to the application.
They also reduce the "invalidation chain", where if you change one table or procedure, it triggers a huge amount of recompilation of all objects that reference the changed object, which in turn recompiles all objects that reference those objects, which in turn...
Because packages supply an interface and a body, only changes to the interface will require recompilation of referencing objects. And if these are in turn packages, then only the package bodies will be recompiled, and there will be no further recompiles.
There's no ideal solution to your problem I'm afraid. Either way involves compromises.
Don't forget that you can parameterize stored procs to the point where you don't really need a proc for every call into the DB you are making. You may write a proc that does updates on a particular table, and the parameters indicate the new column values and/or the conditions for the update. So, you end up with fewer procs that way. Also, you can still use dynamic SQL, which is not quite as performant as static SQL, but using BIND VARIABLES is the key there. You can use bind variable even with dynamic SQL. This option allows for a lot of parameterization of stored procs.
Tom Best
Beware of Packages - if you design and create them poorly then any gain in performance you were going to get from compiled SQL you are not going to get. For example, if you put all of your 2000 procs in to one package then every time one of your procs is called, you have to load the entire package in to memory.
Another advantage of Stored Procedures / Functions / Packages is security. By putting all of your SQL in these, you can effectively provide a web user with access to execute only these and not give the web user CRUD access to your base tables. A good selling point for your customers and quite prudent from your point of view.
What about transactional SQL? Transactions are generally easier to implement in Stored Procedures, as opposed to an application - although it's not always possible to do this.
I believe portability across database platforms is a red herring. Once you have invested plenty of money in one platform, you are extremely unlikely to change platforms.
However, having to maintain two sets of application code is a fair point. In saying this though, it can be good to separate it out from the point of view of allowing "real" SQL developers to write the SQL and make sure that it is tuned. I've worked in plenty of situations where SQL is poorly tuned post-implementation. Releasing a tuned version of a stored procedure is far easier than releasing another version of your application.
My recommendation would be to create two users: one being the schema owner and another being the user that will execute the stored procedures. Then write well designed, tight packages and call them from your application.
To avoid unnecessary network traffic use a database package. It also helps you in easier code maintenance. Divide the packages into logical units – do not store all database functions and procedures in the same package. The database packages are precompiled thus you do not waste time to compile it at run time. Also, to reach better database performance, you may keep frequently used packages in memory buffer pool.
Best wishes
Dusan Marjanov
Stored procedure is better.:)
Stored procedure is the best. Infact you can go for packages.
Believe in the Best .. That's Oracle...
Not Microsoft
Antony Gubert
Absolutely use Oracle packages.
Tuning sql, maintenance of db interface is insolated from the application code.
Changes will occur control them with releases.
Recompilation will occur when changes are made in developement enviroment.
It is development. Have an application dba responsible for this piece of the application.
Build wrapper packages that front end uses for common modules. these call lower level proc associated with tables.
Return sql results in ref cursors.
don't directly expose tables to end users.
Views for security. if users need update to tables use Updates thru instead of triggers on views which call stored procs in packages.
Data security can't be overlooked and is something not to be handed to the application.