I was to optimise a SQL query (7 min to execute,yes) with around 20 joins (I did not write this). Checked for missing indexes,etc.. but nothing worked. Stared outside the window, and back to desk reordered the joins ,executed in 10 secs.

Crappy SQL queries do provide great scope for providing eye dropping performance improvements.

I once looked at a stored proc that took several minutes to run. Changing a parameter from NVARCHAR to varchar (to match the underlying field) made it run in about 5 seconds.