Entity framework takes about 10 minutes to get data from Azure sql

Question

I have a Azure sql database in which i record chat logs that i later analyze. The query was fine at the beginning but currently it has around 11 million entries and it took me 586 second to get the data.

My model

public class Messages
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    [Index]
    public DateTime Timestamp { get; set; }
    public string Message { get; set; }
    public string Username { get; set; }

    public int ChannelId { get; set; }

    [ForeignKey("ChannelId")]
    public virtual Channel Channel { get; set; }
}

Database call

                IEnumerable<Messages> messages = context.Messages.AsNoTracking()
                                     .Where(x => x.Channel.ChannelName == Channelname &&
                                     x.Timestamp > StartTime &&
                                     x.Timestamp < EndTime).ToArray();

Data once inserted is only read. I tried indexing timestamp column but didn't seem to help. Data is also sorted by datetime so i don't understand why it takes that long.

UPDATE:

I did some bechmarks

My way Channel.ChannelName == Channelname: 4:35

My way Channel.ChannelId == Id: 16sec

Suggested answer: 4:30

Suggested with id: 16 sec

sample was 40k results.

How do i fix this? Can i optimize more?

Answer 1

Does your Channel table have proper keys and indexes? Also try filtering properties that aren't external tables first (ex: Channel).

var messages = context.Messages.AsNoTracking()
         .Where(m => (m.Timestamp > StartTime) && (m.Timestamp < EndTime))
         .Where(x => x.Channel.ChannelName == Channelname)
         .ToArray();