CODEWITHSUNDEEP
c#linq
SkyeBoniwell
SkyeBoniwell

Reputation: 7092

linq query that is causing performance issues

I have this method that returns true or false based on whether a website has any pages that have any external links.

The method does work. However, it is incredibly slow. Like it takes about 5 seconds to process a 100 websites with a combined total of 5000 pages.

protected bool WebsitesWithExternalLinks(int id)
    {
        var website = FetchModel(id);
        if (website == null) return false;
    //return true;  
        var externalLinks = website.WebPages
                          .Any(wp => wp.Links != null &&
                                    wp.Links.ExternalLinks.Any());
        return externalLinks;
    }

I've pinpointed the slowness is occuring somewhere in the linq query, but I'm not quite sure why it's happening. I have other linq queries that operate on WebSite and WebSage objects that are quite fast.

I'm not sure why this one is so slow.

Is there any way to speed up linq queries or replace them with something else?

Upvotes: 0

Views: 112

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109080

By the statement...

var website = FetchModel(id);

...you fetch one website from the database. Then you access website.WebPages. This loads all WebPages of that website in a separate query (lazy loading). Then for each of these pages, ExternalLinks are queries in yet more separate queries until the first page is found having at least one external link. So if there are many pages with zero links, this make take a considerable amount of time.

The remedy is to do it in one query. Directly using a context it would look like this:

var externalLinks = 
    context.Websites
           .Any(w => w.Id == id
                  && w.WebPages
                      .Any(wp => wp.Links.ExternalLinks.Any()));

This will execute one query that only return a boolean.

I can't tell from here how this fits into your architecture. Depends on how deeply you buried the context and how much freedom you allowed yourself to shape the queries you execute with it.

Upvotes: 3

Related Questions