6

I am concerning about performance, specially on large table. In my example StatusId is a IDENTIY PRIMARY KEY.

I would like to know if there is a better way (faster) to get the last entry in a table.

  public IQueryable<GaStatus> GetLastStatus()
    {
        return context.GaStatuses.OrderByDescending(x => x.StatusId).Take(1);
    }
GibboK
  • 71,848
  • 143
  • 435
  • 658
  • 2
    if you just request one item, you shouldn't return IQueryable. – AcidJunkie Mar 11 '14 at 11:02
  • 2
    @AcidJunkie Why? In this example, the query is not yet executed, which may be something they want. For instance to make it part of some another queries. – MarkO Mar 11 '14 at 11:06
  • 1
    I think you should use LastOrDefault as you want just the last record and StatusId is the primary key. – Mohamed Farrag Mar 11 '14 at 12:29
  • LastOrDefault should return all the entries and then take the last one – not sure about perfomance on big tables - thanks for sharing anyway – GibboK Mar 11 '14 at 20:26

2 Answers2

5

The Entity Framework generates the following SQL for me:

SELECT TOP (1) 
[Extent1].[StatusId] AS [StatusId]
FROM [dbo].[GaStatuses] AS [Extent1]
ORDER BY [Extent1].[StatusId] DESC

This looks like a reasonable way to get the desired result, although 'Extent1' is not easy to read. See for example How to read the last row with SQL Server.

Therefore, I don't believe there is a better or faster way to do this in Entity Framework.

But there is almost always a way to improve the performance of a given SQL query. For example, looking at the execution plan in my particular database, the ORDER BY does a clustered index scan. This might or might not be good from a performance point of view. So, depending on your database, you might be able to identify performance problems and improve performance by writing the SQL directly rather than having Entity Framework generate it for you.

Community
  • 1
  • 1
Ande
  • 491
  • 1
  • 7
  • 22
-2

return context.GaStatuses.Max(x => x.StatusId);

AcidJunkie
  • 1,878
  • 18
  • 21
  • @ken2k Having a look at the actual SQL Execution Plan says otherwise. The main load comes from the index scan. – AcidJunkie Mar 11 '14 at 11:20