Hey, they stole my line!

I've been known to say from time to time "my car won't start, why not?" (followed many times by a "I've given you as much information as you gave me regarding your problem so now we are even")

Well, these guys stole my story!!! http://notalwaysright.com/diagnostics-through-osmosis/1379

Very funny blog most of the time - I can feel their pain.

Doing it wrong...

I hate queries of the following form:

select count(*) from <anything else here>

The reason?  The code typically looks something like this around the count(*):

select count(*) into l_cnt from .....;
if ( l_cnt > 0 )
then
    process_the_data;
end if;

I've always wondered why that code isn't just:

process_the_data;

Why bother counting - and then processing if that count was greater than zero.  Why not just process_the_data - that routine already knows how to stop when it runs out of data - just let it run out of data naturally on row zero if there is no data.

Many people don't stop to consider that

• The count can change between the select count(*) and the process_the_data call - there might be nothing by the time you get into the process_some_data



• The count can change while you are running the process_some_data call itself - you cannot use the count as "this is how many times to iterate" (I've seen it done - it fails spectacularly when there are less rows than you counted, it fails silently when there are suddenly more and you never get to them, it also sometimes works by accident).

I've seen code like:

select count(*) into :cnt from t;
allocate array of :cnt elements
open C for select * from t;
for i in 1 .. :cnt
loop
   fetch c into array(i);
end loop;
close c;

You can just imagine the damage that could do in a language like C for example - interesting results when there are less than :cnt rows to get, segmentation fault - core dumped (we HOPE - we hope it crashes) if there are more than :cnt rows to get.

 

Anyway, this isn't a post about "don't count and then process" (well, ok, it is in part) - this is a post about an interesting snippet of code a friend sent me.  They are on site doing some "tuning".  I've modified the variables and such to disguise it - but the "logic" is intact:

FUNCTION count_em_up
( p_input1 in number, 
  p_input2 in varchar2 
)
return number
IS
   CURSOR C 
   IS
   SELECT actual_columns
     FROM some_table
    WHERE a_column = p_input1 
      AND another_column = p_input2;

   l_the_cnt    number default 0;
BEGIN
   FOR rec IN C 
   LOOP
      l_the_cnt := l_the_cnt+1;
   END LOOP;
   RETURN l_the_cnt;
EXCEPTION
   WHEN OTHERS THEN
        RETURN NULL;
END;

That hurts me in so many ways. 

• The dreaded "when others <no error raised here>"



• A loop to COUNT THE ROWS RETRIEVED BY A QUERY!!!



• Because I did not believe it: A loop to COUNT!!! (had to be said twice)



• A function to count rows - probably used in higher level code like this "if count_em_up(x,y) > 0 then process_some_data; end if;"

Well, at least there is the very real probability of tuning this particular application - there is probably lots and lots of low hanging fruit out there like this!

It has been a while...

It has been a while since I've posted - so lots of things to talk about coming up. 

For a long time, I've had two monitors at home.  I got them back in 2001 right after finishing the first book Expert One on One Oracle - it was my gift to myself for finishing...  They cost a lot back then, it was a huge investment - but worth it.  They are both still going today (you can see one of them down below on the left - the other is with a friend).  Not only do they still work - but the entire dual monitor concept just changes the way you work (more screen, more stuff).

Two is no longer enough however - now I must have three.  But to get three video outputs was hard in my desktop - and I wanted to be able to plug into my TV to show pictures whenever I wanted as well (easily, not by ripping out the VGA cables and such).

I had been looking at wireless USB connections - but they all seemed slow and low resolution.  Till I hit this one:

 

IOGear GUW2015VKIT Wireless USB to VGA Kit

It supports up to 1600x1200 and everything lower.  Pop it off the back of the monitor and put it on the TV and voila', I'm using the TV as a monitor.  And - it is not slow, and it was really easy to install - no worries about "did I have a free PCI slot, or was it APG, did I need a half height card or a full size - etc".  Seconds after plugging it in - I had three monitors going:

 

Linux in the middle :) That is a full screen VNC window to my server - for answering questions and testing on.  I keep my email/status windows on the right.  To the left is the 'windows' screen.  

I'm really liking it.  Until I can figure out how to get more screens of course...

It is amazing how the amount of screen real estate changes the way you work.  I cannot do real work on my single screen laptop anymore - web browser, email - sure.  But trying to write an article/paper and use the database and have the browser and email going - impossible.

Apparently, I need a bigger desk at home...  For the fourth monitor.