I love it when a plan comes together…

Yesterday I was trying to optimize a data transfer that took the best part of two hours to complete…

After a quick inspection I noticed a couple of things:

  • After the data was transfered from a remote database, it was updated 5 times to alter some of the values. A couple of quick DECODEs in the transfer query and these five updates were removed.
  • The transformation of some of the data was done by a really inefficient function. It was doing date arithmetic by selecting from dual, rather than just using a procedural assignment like, “variable := SYSDATE + 1”.

After rectifying both these issues the transfer time was reduced from nearly 2 hours to just 3 minutes.

I love it when a plan comes together… 🙂

Cheers

Tim…

PS. If only all optimizations were so easy…

Author: Tim...

DBA, Developer, Author, Trainer.