List and Linq To Sql Performance Issue

Question

I Have One Table (My Sql) with 2 millions records and one List with 100 Records. I have List Except lamda expression for finding all those Urls that is in List but Not in Table.

Now Issue is that it's taking lot of time around 5 mins. I am working in powerful VPS and code and database in same server.

Please suggest me All possible way to increase the performance of linq to sql and linq to entity.

My Code Is`return

Urls.Except(DbContext.postedurllists.Select(crawl => crawl.PostedUrl).ToList()).ToList();`

Where Urls Is List Which Contain 100 Urls And postedurllists is a table that contains 2 Millions record. Thanks

Answer 1

You're currently pulling all of the URLs from the database. That's not a good idea. Instead, I would suggest pulling the intersection from the database by effectively passing your Urls list into the database, and doing an except based on the results:

var commonUrls = DbContext.postedurllists
                          .Select(c => c.PostedUrl)
                          .Where(url => Urls.Contains(url))
                          .ToList();

var separateUrls = Urls.Except(commonUrls);