Is it possible to simplify a SEDE query that has the same formula written multiple times?
For instance, this query is writing rtrim(LOWER(Title))
five times:
select
rtrim(LOWER(p.Title)),
count(rtrim(LOWER(p.Title)))
from Posts p
group by rtrim(LOWER(p.Title))
having (count(rtrim(LOWER(p.Title))) > 1)
order by count(rtrim(LOWER(p.Title))) desc
In answers, please specify if your factorisation is purely cosmetic or if it also has a performance impact.
First - the permanent solution here is to clean up your data. Using functions like LTRIM, RTRIM, UPPER, LOWER makes your not SARGEable. In other words your queries can slow to a crawl because it's impossible for SQL Server to retrieve the data you need from an index without scanning all rows.
Enter the APPLY + VALUES inline aliasing trick
This is something I came up with some time ago at first to simplify my code but I later discovered some occasional performance benefits which I'll demonstrate. First some sample data:
Let's say we have a query that takes a few variables or parameters, performs a calculation on them and uses that value throughout a query. Note the case statement below.
We can simplify this query like this:
Each query returns identical results. Now the execution plans:
Not only have we simplified our query, we've actually sped it up. In my original query the optimizer had to calculate the same value twice and perform two sorts. Using my inline aliasing trick I was able to remove a sort and a calculation