Slow Calculated sort in MS SQL Server I have a big query (Entity Framework generated one) and I'm using a Calculated Field. Performance has already improved by adjusting the Clustered Index to include the [On] (datetime) column.

The sort on the calculated function is responsible for 100% of the performance slowness according to the Query Plan.

The function estimated the order of the item and ranks it according to the votes/comments it received and gives newer items a higher ranking. So the score changes every hour + every vote on item/comment (similar to Reddit).

In short, this is what it ranks by

CAST( [Project4].[C1] AS float) / (POWER( CAST( DATEDIFF (hour, [Project4].[On], '2018-05-01 00:00:00.0000000') AS float) + cast(2 as float(53)), cast(1.8 as float(53)))) AS [C1],


The problem is, when I sort by [On], I have immediate results. When I sort by the mentioned calculation, it takes 12-16 seconds.

The literal '2018-05-01 00:00:00.0000000' is dependent on current time.

How would I improve this?

Edit:
I didn't think it was useful to post the query, because it's created by EF and very big (also for Stackoverflow, see this pastebin )

More useful: pastetheplan
You shouldn't do this kind of sorting in SQL Server, you should do it in your application. Here's why:

When you need to order data that doesn't have an index that supports the required sort order, your query will do all the work at runtime with a Sort operator.

SELECT *
FROM dbo.Users AS u
WHERE u.Reputation &lt; 2
ORDER BY u.Reputation;




The Sort operator will ask for memory to sort all of the columns you request by the column(s) you're ordering by (this is greatly simplified, but going deeper isn't really necessary here).

If I add an index that supports the sort order (and in this case, the WHERE clause) I'm asking for, the query plan may change to use it. This isn't guaranteed, since at some point the optimizer may decide that doing a Key Lookup isn't in its best interests.

CREATE INDEX ix_yourmom ON dbo.Users (Reputation);




But in this case, I'm returning a small enough number of rows to make the Key Lookup attractive. It has six pack abs and a cool anime haircut, or something.

Of course, if I add more complexity to the ordering, the plan goes back to the original one that scans the clustered index and uses a Sort operator to give us the ordering.

SELECT *
FROM dbo.Users AS u
WHERE u.Reputation &lt; 2
ORDER BY CAST(u.Reputation AS FLOAT) / 
POWER(CAST(DATEDIFF(HOUR, u.CreationDate, '2018-05-01 00:00:00.0000000') AS FLOAT) 
+ CAST(2 AS FLOAT(53)), CAST(1.8 AS FLOAT(53)));


The way you'd normally get around this is by adding a computed column:

ALTER TABLE dbo.Users ADD RankingSort_Literal AS 
CAST(Reputation AS FLOAT) / 
POWER(CAST(DATEDIFF(HOUR, CreationDate, '2018-05-01 00:00:00.0000000') AS FLOAT) 
+ CAST(2 AS FLOAT(53)), CAST(1.8 AS FLOAT(53)))


But since you said:


  The literal '2018-05-01 00:00:00.0000000' is dependant on current
  time.


Which makes the DATETIME literal the equivalent of calling GETDATE(), which means that your computed column wouldn't be determinstic. Having a non-deterministic computed column means you can't a) index it b) persist it. 

Sometimes that's okay, but not when you're trying to sort data by it. There's no benefit here.

Of course, I'm saying all this with no insight into:


The rest of the query 
Current indexes 
The query plan
Table definition 
SQL Server hardware and settings


It could be that adding memory, or changing your query to use a temp table in a stored procedure would make sense, or simply changing your query to select fewer rows via a TOP would help. I'm not sure if you're able to make any of those changes though. 

Which brings me back to my original point: You should do this sorting in the application.

Hope this helps!