How to get dreadful database performance

 

I love this video on “getting dreadful database performance” by Stephane Faroult.
The video is hilarious and informative:

Screen Shot 2014-05-21 at 8.08.47 AM
http://www.roughsea.com/vids/SQL_Best_Practices.html

Screen Shot 2014-05-21 at 8.08.47 AM
He tackles top 5 keys to dreadful performance

Screen Shot 2014-05-21 at 8.08.47 AM

  1. EAV “schema” 2:10
  2. Unusable indexes 6:00
  3. Hard parsing 7:36
  4. Unitary processing 10:40
    • user functions (instead of join, ex: currency conversion) 12:19
  5. views of views 14:46

Conclusion: most developers are young and clueless about database (java programmers?) and code must be refactored by developers not by DBAs in production databases. Hopefully your developers will get guidance by an experienced (older) expert.
Screen Shot 2014-05-21 at 8.08.47 AM
Screen Shot 2014-05-21 at 8.08.47 AM

In my Experience

Screen Shot 2014-05-21 at 8.08.47 AM

The first topic EAVs was a huge problem at 3 of the last 4 companies I was at, including Oracle’s OEM 10g Grid control, despite solid analysis of pro’s and con’s by John Beresniewisz.Screen Shot 2014-05-21 at 8.08.47 AM

Check out more on the
Screen Shot 2014-05-21 at 8.08.47 AM
Screen Shot 2014-05-21 at 8.08.47 AM

Origins of Stephane’s Video  Style

Screen Shot 2014-05-21 at 8.08.47 AM
Emailing Stephan, I found out that his video was inspired by this fun video (though less related to UI or databases):
Screen Shot 2014-05-21 at 8.08.47 AM
Identity 2.0
Screen Shot 2014-05-21 at 8.08.47 AM
I like this kind of presentation. Fun and informative as well as efficiently re-iterating the important points.

Screen Shot 2014-05-21 at 8.08.47 AM

Screen Shot 2014-05-21 at 8.08.47 AM

My Approach to Tuning SQL

Screen Shot 2014-05-21 at 8.08.47 AM

One of the main things I do to tune SQL is to encourage the optimizer to take path.
I often determine the most important steps to do first and put them in a sub select. I’ve been meaning to write something up about this for years. Unfortunately all the great examples have been lost at customer sites happily left behind after my work was done. Only later did I kick myself for not having saved more. I have not taken the time or energy to come up with some good examples. Well, all is not lost because here is another great video from Stephene Faroult on this exact subject:
Screen Shot 2014-05-21 at 8.08.47 AM
The ideas he lays out

Screen Shot 2014-05-21 at 8.08.47 AM

  • Take the highest seletivity steps and do them first (this is where I often use “inline views”)
  • Sort the least amount of data , ie sort the key column and not all the data columns
  • Join in non-key columns as late as possible
Screen Shot 2014-05-21 at 8.08.47 AM
To summarize, I look for the central data (what Stephen calls the “key”) and put this in an inline view , then sort, then finally join in any missing columns
1098176_10202046154420479_2047307696_n


Uncategorized

  1. Trackbacks

  2. No trackbacks yet.
  1. Comments

  2. | #1

    I like Tim Gorman’s extreme EAV story:
    https://www.simple-talk.com/opinion/opinion-pieces/bad-carma/

    > My approach to Tuning SQL
    These are good points and very similar to my own core principles:
    – If existing SQL then reformat personal preference (a manual task which helps give familiarity and often highlights obvious errors)
    – Try to state the question that the SQL is meant to be asking (rarely easy)
    – Separate core logic and leave “boilerplate” additional tables/columns until later using inline views, as you mentioned, or my preference is WITH.
    – Look for joins which should be IN/EXISTS subqueries, e.g. no selected columns
    – Eliminate early
    – Sort small
    – Avoid functions (where the worst offender is SQL which calls functions in where clause where the function itself contains SQL)

  3. khailey
    | #2

    @Dom: thanks for the link to Tim’s story. That’s a good one :)
    Nice list of steps to take when approaching SQL tuning


8 − = five