tiistai 13. joulukuuta 2011

ER model

Yesterday I was solving (again) a problem on an sql clause that did not perform well enough.
When I solve this kind of problems I usually draw a picture of the query: tables, joins, where conditions etc. Just yesterday I got a great idea: use the ER model as the base for the drawing! So print out the ER model and draw the aliases, joins, wheres etc on that picture. You will save time and the drawing looks very clear!

I did solve the problem and the ER helpped me a lot: missing join, too many outer joins (on tables where the relationship is mandatory) etc.

Feel free to use this tip unless you are already using this method.

2 kommenttia:

  1. It is not ER diagram you are drawing. It is a visual sql tunning (VST) diagram. Nice list for furter reading http://dboptimizer.com/2011/10/20/tuning-blog-entries/ at "VST – vistual sql tunning". DB Optimizer does those.

  2. My point was that Data Modeler is not just for "drawing" ER models. There are many other ways of using it as well. This as an example of that. I found it very useful and no need for other tools. It was also a good "tool" to prove that outer joins are not needed (ER model shows the relationship is mandatory).