This one always amazes me. How far people are willing to go to save them selves some tpng. I’m talking about the tendency of many developers to create some sort of a container for everything they do. Specifically I’m talking about the overuse of views. Don’t get me wrong, views have their place in the world but come on! I frequently show up on customer sites where there are performance issues. In most cases the performance boils down to bad sql and in many cases it is a simple statement like:
select col1, col2, col3 from view1 v where v.col5 = :bind1
Simple enough, huh? Except when you look closer view1 is based on 3 other views and each one of those is based on another 3 views and each one of those is based on 3 other views and each one – you get the picture. The worst case I’ve found so far is 9 levels, including user defined functions. What brings me to bitch about this? I just came from a customer who has a statement that has around 50% of all his CPU power, months of total elapsed time and returns only a few rows every time it is run. This of course is based on views. I rewrote that query and unnested it bringing each execution down from 9 – 10 seconds to 0.1 seconds, per execution. If you could 100 fold your systems performance/throughput whatever by simple things like this (which are not simple in most cases, unnesting can be dreadful)…There was no index created, no actual “tuning” except unnesting. The sad part is that Oracle perhaps should be able to do this, but it can’t except in simpler cases.
Unfortunately, you cannot in most cases. These are often third party applications written by professional developers who have no regard for the performance of your database. Get your act together. Write professional code. Do what you are supposed to be doing. Worst thing is that the standard response for performance complaints is that the DB is not performing. Interesting.
Second thing is overuse of PL/SQL functions. A simple example, same as above actually. Each and everyone of these views was using a functon called NVS which was basically a user defined function :
function nvs(p_str in varchar2) return varchar2 is begin return nvl(p_str, ' '); end nvs;
So, it basically replaces nulls with a space. Same as NVL(p_str,’ ‘). Saves you 4 keystrokes. I measured this, this actually performs 100% worse than using NVL. Double. Do not call these functions unless there are really good reasons to. Saving 4 keystrokes is not a good reason. I just measured the basic PL/SQL, no context switching, nothing. Do not call PL/SQL from within SQL if you don’t have to. Sometimes you do have to. Just think before acting and test your solutions using real data.