1

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

  • The calculated columns is time-constrained, so not only depending on an extra point for votes or columns. I think an index on that would be a bad idea, I could be mistaking though. Will post the total query when I'm back at my pc – NicoJuicy Apr 8 '18 at 11:41
  • It's the current date time, so not a constant. So yes, it changes a lot. It's used for ranking items and has a time variable – NicoJuicy Apr 8 '18 at 11:44
  • SQL 2016 and the plan is available on brentozar.com/pastetheplan/?id=SJ-rHlOiM , the sql is generated by EF so it's pretty big... – NicoJuicy Apr 8 '18 at 20:03
3

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 < 2
ORDER BY u.Reputation;

NUTS

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);

NUTS

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 < 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!

  • Very good answer, but handling it in my application would be slower ( since i think SQL is just faster). Limiting the result queries is a possibility, eg. show only results that have a vote. I'll give the indexes a closer look and limit the requested fields for now, i also uploaded the plan as suggested by a comment above – NicoJuicy Apr 8 '18 at 20:01
  • 1
    @NicoJuicy note the title of your question, "Slow calculated sort in MS SQL Server." – Brent Ozar Apr 8 '18 at 21:38
  • Okay. You are right about that – NicoJuicy Apr 8 '18 at 23:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.