Slow by Slow...

Was reading the "theDailyWTF" site - and saw Alex going on about slow by slow processing (in a sqlserver database no less).  Really hits home - I see it all of the time.

Why do people feel compelled to write code like:

for x in (select * from t1)
loop
   insert into t2 (c1,...) values (x.c1,...);
end loop;

Instead of just "insert select".  I'll never ever understand it.  Just last week on asktom - a similar thread came up.  The "boss" wanted the developer to not use a single SQL statement to process data, but loops of loops (a do it yourself nested loop join!!!).

I was working with a customer who was seriously considering moving to another database because "Oracle was slow".  So, I asked for a chance to see if we couldn't speed things up.  I was given some of their code - it looked like this:

insert into t ( empno, effective_date, .... )
select empno, effective_date, ....
  from t1, t2, t3, t4, ...
 where ....;

loop
    delete from t
     where (empno, effective_date) in
    (select empno, min(effective_date)
       from t
      group by empno
     having count(1) > 1);
    exit when sql%rowcount = 0;
end loop; 

Now, do you see any problems with that?  This was a data warehouse - big table.  Basically the logic was to try to "keep the most recent employee record".  How did they do that?  They got ALL employee records (filling the table up) and then removed bit by bit the ones they did not want.  Assume every employee had at least two historical records - you would have a table that is half empty after the first pass (most had more than one!) and not only that, but you would constantly full scan the table T over and over until you found no records to remove (just one employee with 100 records - you loop 101 times).

Further, I explained that not only does it remove the "old" records - if two records existed for some employee on the newest date - both of them would disappear (no records for that employee).  They said "no way, that isn't what that code does" - had to prove it to them (just put in two records with the same empno/effective_date and run the loop - bamm - they both go away).

I rewrote the code as follows:

 insert into t ( empno, effective_date, .... )
  select empno, effective_date, ......
    from 
( select empno, effective_date, .... , 
         max(effective_date) OVER ( partition by empno ) max_edate
         --count(effective_date) OVER ( partition by empno, effective_date ) cnt
             from t1, t2, t3, t4, ....
            where .... )
   where effective_date = max_edate
     --and cnt = 1;

We added the comments to the COUNT check after determining that it was a bug in their code.  This insert as select ran faster than their insert did (far far far fewer records to insert), resulted in a table with no "whitespace" (which was nice for their data warehouse and subsequent full scan queries!). And the delete loop - it was gone. 

Turns out Oracle wasn't slow, the hired consultants wrote slow code.  Anyone can write slow code :)

My mantra, that I'll be sticking with thank you very much, is:

• You should do it in a single SQL statement if at all possible.


• If you cannot do it in a single SQL Statement, then do it in PL/SQL.


• If you cannot do it in PL/SQL, try a Java Stored Procedure.


• If you cannot do it in Java, do it in a C external procedure.


• If you cannot do it in a C external routine, you might want to seriously think about why it is you need to do it…

think in sets...

learn all there is to learn about SQL...

Analytics rock and roll...