How to get dreadful database performance
I love this video on “getting dreadful database performance” by Stephane Faroult. The video is hilarious and informative:
http://www.roughsea.com/vids/SQL_Best_Practices.html
EAV “schema” 2:10
Unusable indexes 6:00
Hard parsing 7:36
Unitary processing 10:40
user functions (instead of join, ex: currency conversion) 12:19
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.
In my Experience
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.
Check out more on the
EAV at: http://en.wikipedia.org/wiki/Entity-Attribute-Value_model
And by Tom Kyte at: http://en.wikipedia.org/wiki/August_24
Origins of Stephane’s Video Style
Emailing Stephan, I found out that his video was inspired by this fun video (though less related to UI or databases):
Identity 2.0
http://identity20.com/media/OSCON2005/oscon_videos/oscon_qt_sm.html
My Approach to Tuning SQL
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:
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