Following on from my posting about the presentation (about Enterprise databases) that I did at a Microsoft event on SQL Server 2008, I’ve just had an article on designing efficient SQL published on a website that’s strongly biased towards SQL Server developers – using an example of SQL written in the SQL Server dialect.
It’s worth a read. Don’t be put off by the change in dialect, the topic and technique are more significant than the language. (In fact it’s a write up of a presentation that I gave at the UKOUG Conference Series 2009 in Birmingham.)
Jonathan,
thank you very much for that article. Although it uses strange terminology, it’s definitely understandable and worth reading.
I usually draw a picture of the SQL when it has ~4 tables involved, just to better understand what was the original idea behind it (without a picture I’m not good at that) and quite often it helps not only to pick up “right” way for SQL execution, but also to structurally modify a query – I mean to do the CBO’s job (or developer’s – that depends on point of view). I use pretty much similar way, except that I can’t make it look “perfect” :-D I also don’t have a “standard” for representing subqueries, I think I’ll borrow your style, it looks good. Thanks again.
Comment by Timur Akhmadeev — March 4, 2010 @ 9:24 pm UTC Mar 4,2010 |
Hi Jonathan,
Nice post about the visual approach.
Quite often when asked to tune a query I do this.
And more than once I discovered that queries with bad response times where missing one or more join conditions.
But it stays tedious work.
regards Hans-Peter
Comment by Hans-Peter Sloot — March 16, 2010 @ 2:22 pm UTC Mar 16,2010 |
[...] 3-How to design efficient SQL ? (must) Jonathan Lewis-SQL Server 2 [...]
Pingback by Blogroll Report 26/02/2010 – 05/03/2010 « Coskan’s Approach to Oracle — March 25, 2010 @ 1:48 pm UTC Mar 25,2010 |