Tom, I curious to know, may be this is stupid question. Why SQL that can be done with simple SQL (join, subquery) version is faster compared with the same SQL using procedure call (ie to replace subquery). Please explain me the technical stuff behind that.
in general, the more you let the database do, the less the database HAS to do.
Let us say you want to join table T1 with 100,000 records to table T2 with 500,000 records (each row in T1 joins to about 5 rows in T2)
suppose you do this in code:
for x in ( select * from T1 )
for y in ( select * from T2 where T2.key = x.key )
so, that is 100,000 one row fetches from T1. And each time we do that, we run a query against T2 - we run that query 100,000 times. Every time we run it - it takes time to just set up and execute the query. And even worse - the optimizer would likely use an index on T2.KEY (finding 5 rows out of 500,000). So, we would do 100,000 index range scans.....
As opposed to:
select * from t1, t2 where t1.key = t2.key
Now, indexes - they will be smartly ignored - two nice full scans - a hash join, very efficient.
If we did it procedurally - the most efficient plan would not be available to the optimizer.
Think of it this way. Your boss could micromanage you and tell you bit by bit what to do. And a lot of it would seem repetitive to you. By the time you were done - you would see that there was a really efficient way to accomplish what you just did in a fraction of the time - HAD YOU BEEN TOLD what the goal was, not how to do it.
The database works in the same way - tell it the goal - not how to do it and let it decide how best to attack the problem.