We might have been experienced many circumstances or waver when to use an oracle procedure or .net function to implement a logic.
Let us see what oracle does upon request:
Oracle has 3 different kinds of processes running at its end to run or execute certain requests.
1) Back ground process.
2) Server process.
3) Network process.
The processes listed have there own importance and designed to improve performance. Oracle data base has fine defined memory structures which support best manner to manage, organise, manipulate, etc data based on request from user.
Example :-
When user provides a sql select query to run at oracle server, it does not do blindly retrieve data from disk based on applied logical query. Instead it has an algorithm to go through the steps in an organised manner to send data.
It has library cache and dictionary cache buffers which stores recent oracle queries and data from it respectively. So oracle optimises the process of handling user requests or queries and improves performance.
When the logic or requirement if the developer is to manipulate (insert/delete/update) data or get a value based upon the data available in oracle tables (also need to join tables) , etc.. it is better to use oracle procedures/functions which improve performance , as sql optimiser helps it.
The advantages of using Oracle objects for implementing for such kind of logics which are dependant upon the available data in data base and other kind are
1) sql optimiser plays a vital role to improve performance.
2) Compiling the oracle objects also helps developer to provide proper sql syntaxes.
3) If any table objects are dropped from database sets the dependant objects state to invalid or which need to be complied again. This helps user to find such dependant procedures/functions and implement them.
4) If the application set up along with data base is installed on client machine, later if developer needs to fix an issue related to logic implemented, In case of front-end programming language (java, .net) developer needs to build the set up again and install it at client environment. Where as using data base objects like procedures.funtions developer can just provide sql scripts that should be runned at client data base serer to reflect the changes.
We shall see many requirements like in a grid we should provide a sorting button in header for each data column, on click of which we should sort the data available at that column and update the grid.
For such requirement we usually do is we use data table in .net , apply it to a data view object ,sort the data view and bind the updated data view to the grid.
For which CLR should handle the memory to sort the data.
Where as if we use oracle, by calling the same query to retrieve data and applying “order by” clause at end of the query select query, bind to grid is more efficient
This is because as said , if we use a select query oracle first looks into dictionary cache if data available for that query, library cache whether same query is used earlier and if found immediately assigns the execution plan to the optimiser, which retrieves data. If user sorts the same column again next time (running same query in oracle data base) definitely will have faster response from web server.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment