SQL Linq .Take() latest 20 rows from HUGE database, performance-wise

Vote count:

0

I'm using EntityFramework 6 and I make Linq queries from Asp.NET server to a azure sql database.

I need to retrieve the latest 20 rows that satisfy a certain condition

Here's a rough example of my query

using (PostHubDbContext postHubDbContext = new PostHubDbContext())
{
    DbGeography location = DbGeography.FromText(string.Format("POINT({1} {0})", latitude, longitude));

    IQueryable<Post> postQueryable =
        from postDbEntry in postHubDbContext.PostDbEntries
        orderby postDbEntry.Id descending
        where postDbEntry.OriginDbGeography.Distance(location) < (DistanceConstant)
        select new Post(postDbEntry);

    postQueryable = postQueryable.Take(20);
    IOrderedQueryable<Post> postOrderedQueryable = postQueryable.OrderBy(Post => Post.DatePosted);

    return postOrderedQueryable.ToList();
}

The question is, what if I literally have a billion rows in my database. Will that query brutally select millions of rows which meet the condition then get 20 of them ? Or will it be smart and realise that I only want 20 rows hence it will only select 20 rows ?

Basically how do I make this query work efficiently with a database that has a billion rows ?

asked 1 min ago

Dv_MH

161