Better SQL writing techniques
I know this is out of the Java scope but I am sure there are many SQL experts in this forum, I was just wondering whether anyone can point me to the right sources for learning how to write better SQL and hence faster execution.
I tried google and mostly I get the standard fixing the index, running the query analyzer ...etc.
But what I really want is some examples on things that we should avoid doing/using in a SQL statement, things that we should do more (for example, using outer join, but I never really come across an article that explains why that is).
I would appreciate if anyone knows a book or just a tutorial which does just that.
Thx.
# 1
Buying books and taking classes at community colleges are what help you to get better at sql writing.
I myself took a course in sql. The thing is that you can get better by taking classes. By the way, avoid join statements if you want to execute things faster. I prefer to use stored procedures because they are a compiled thing which in turn means faster execution. I don't have examples for stored procedures right now.
Hope it helps.
# 2
Learn how to optimise a query. That'll give you a better idea of what's actually going on under the covers. I'd recommend the O'Reilly book in performance tuning.
Then get your head around the theory properly. I'd recommend the Joe Celko books for that.
Above all, write a lot of SQL.
# 5
I wouldn't mind answering specific questions although this is not really the place to do it as SQL is not really JDBC and is certainly not Java but writing down what I have learnt in four years would be like writing a book on the subject.
I will say that understanding how to run a query plan and understand what it is telling you will help you identify good/bad SQL and/or any bottlenecks.
