Skip to main content
1 of 2
mike30
  • 2.8k
  • 2
  • 17
  • 19

A "rule-of-thumb" to get blazing speed in a database is to use set-based commands instead of separate procedural style commands. It's not only the network round trips that affect performance. Even if you have the loop embedded in the sql, you'll find it orders of magnitude slower than a set-based command.

--SLOW way, procedural
loop i=0 to 999999
    update foobar set status='test' where id=i;
end loop

vs

--FAST way, set-based
update foobar set status='test' where id between 0 and 999999

Just identify the bottle necks. You can keep the rest of the logic controlled with your objects. If you operate on a single record at a time, the procedural style is just as fast.

mike30
  • 2.8k
  • 2
  • 17
  • 19