davoscollective (5/19/2014)
Good article, and very important.I noticed some queries only today being executed by very experienced analysts doing awful non-sargable sins like joining a date column to a datetime column, by converting the datetime to a varchar. In the same query they were casting the date column to a varchar in the where clause and comparing it to a string literal.
I thought I was being nice in my pointers on how to improve the query & link to article on sargable queries, but the actuary in question didn't appreciate my advice, stating they were just rerunning another actuary's query. The fact that the query went from 6 minutes to 2 seconds with those very basic changes was beside the point.
On the other hand this person can probably do things with R and SAS that would explode my head, but then, I'm not trying to do those things on their test machine!
It definitely is tough to sell some people on optimization, but if you go armed with lots of facts (CPU, memory, reads, writes, duration, etc...), it becomes a bit easier to make your case, even if others may not like feeling corrected.