Simple data fetch from SQL Server takes too long - .NET

Question

I have this simple view class in .NET:

public partial class vw_ViewFromMSSQL : BaseSQL
    {
        public long ID { get; set; }
        public long databaseid { get; set; }
        public long prop2 { get; set; }
        public long prop3 { get; set; }

where BaseSQL is:

public abstract class BaseSQL
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long ID { get; set; }

        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public Guid UUID { get; set; }
    }

Now I have this simple LINQ which should return me the data for given databaseid:

var data = await _context.vw_ViewFromMSSQL
                .Where(x => x.databaseid == databaseid )
                .Select(x=> new DTOClass()
                {
                    ID = fks.ID,
                    databaseid = x.databaseid,
                    prop2 = x.prop2,
                    prop3 = x.prop3
                }).ToListAsync();

Now this data fetches data in ~10 seconds even though it should return only 150 rows. However if I run the query in SQL Server, I get results in 1 second. I have tons of other calls like this related to other views but they return the result much more faster than this. This view has indexes, it has every column described in the class. Another thing to note is that all of the calls go through Task so they're async calls.

This is kind of more of a discussion than a question but what could be the reason for this slow fetch and is there a way to speed it up?

Answer 1

The abstract class BaseSql already has ID & UUID as property. Why also add ID to vw_ViewFromMSSQL which already comes by inheriting from the base class?

You can also turn on logging, so you can see the underlying query.

Did you try without mapping to DTO? So just:

var data = await _context.vw_ViewFromMSSQL
                         .Where(x => x.databaseid == databaseid )
                         .ToListAsync();