Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

Mantra on data processing

* 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 -> http://asktom.oracle.com/pls/ask/search?p_string=Analytics+rock+and+roll

Reference : http://tkyte.blogspot.com/2006/10/slow-by-slow.html

5 Comments »

  1. [...] So view option used less latching and ran faster(older releases will show a larger disparity between the run times). Again time to remember the “Universal mantra on data intensive processing” : don’t use plsql when plain old sql will do it for you ;) [...]

    Pingback by Which one perform better for the same job : Oracle Function or Oracle View « H.Tonguç YILMAZ Oracle Blog — January 1, 2007 @ 4:06 pm | Reply

  2. [...] Part 1 Filed under: Oracle Performance, Oracle HowToS — H.Tonguç Yılmaz @ 6:39 am Universal mantra on data intensive processing advices that “You should do it in a single SQL statement if at all [...]

    Pingback by Oracle SQL Extentions Part 1 « H.Tonguç YILMAZ Oracle Blog — January 7, 2007 @ 6:40 am | Reply

  3. [...] have to choose a migration methodology which reduces the I/O, and remember the mantra on data intensive processing; Create table as select(CTAS), merge statement or conditional multi-table inserts and dbms_errlog [...]

    Pingback by Some migration experiences to share « H.Tonguç YILMAZ Oracle Blog — March 17, 2007 @ 3:39 pm | Reply

  4. regarding: You should do it in a single SQL statement if at all possible

    I have to disagree. I have written single SQL statements that were pages long and found later that I really regreted doing so. The problem with doing everything in a single statement is debugging. When the results of your query are not right and you need to figure out why, with a complex query you are out of luck. If you break the query down into several smaller queries the debugging becomes easy.

    I would say, “you should use PL/SQL and break the code into small pieces UNLESS performance is a problem. If performance is a problem, then start combining the smaller SQL statement where possible”

    regards,
    Mike

    Comment by hicamel — April 3, 2007 @ 11:17 pm | Reply

  5. hicamel thank you for your comment, I totally agree with you in terms of flexibility and maintainability of the developed application.

    But there are sometimes some constraints you have to obey the performance needs for example of a legal problem. I experienced several examples, until it was over I didnt wanted to go for the analytics solution where pipelined plsql should handle, but at the end it was a legal issue and it had to be finished within some time frame that we had no alternative but anaytics.

    Cost was of course inevitable after some change needed, it was death of pain to modify some several pages of analytics for the new requests.

    Best regards,
    Tonguc

    Comment by H.Tonguç Yılmaz — April 4, 2007 @ 7:15 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 79 other followers

%d bloggers like this: