I was working on some code when I came across a behemoth of an inline SQL query nestled in the code behind of a page.
Actually it was within several lines of concatenated text values, and had no formatting whatsoever!. My first intention was try to make it a stored procedure, but doing so I couldn’t help but try to optimize it a little. Here’s my attempt….
Firstly you’ll notice the second query is far easier to read (hopefully). I dont stand for the old skool method of joining tables, join with a join and it’s obvious what is going on, plus it prepares isolation of results for the where clause afterwards.
Secondly there are fewer joins in the Column definition part of the select. I have included one of the original selects, because when optimizing the stored procedure it was siginificantly fast enough to use select top 1 … than it was to Left Outer Join. I did however Coalesce so that the case statement didn’t repeat iteself.