IQueryable Query Performance Tuning

Question

I would like some suggestions for how to make this simple LINQ code to be as fast and efficient as possible

tbl_WatchList contains 51996 rows

The below test takes 2 secs to run according to VS2012 test explorer

    [TestMethod]
    public void TestRemoveWatch()
    {
        using (var DB = new A4C_2012_devEntities())
        {
            var results = DB.tbl_WatchList.OrderByDescending(x => x.ID).Take(1);
            int WatchID = results.AsEnumerable().First().ID;
            Assert.IsTrue(WatchList.RemoveWatch(WatchID));
        }
    }

Try removing AsEnumerable. And if you really want performnce, skip EF and linq all together.. — MikeSW, Oct 28 '13 at 11:38

score 2 · Answer 1 · answered Oct 28 '13 at 11:38

2

You don't need to sort whole collection.

int WatchID = DB.tbl_WatchList.Max(wl => wl.ID);

Should be sufficient.

answered Oct 28 '13 at 11:38

bsar

21
3

score 1 · Answer 2 · answered Oct 28 '13 at 11:43

To optimize, do the following:

Use a profiling tool (such as SQL profiler) to see what SQL queries are sent to the database and to see what the real performance is of those queries.
Select the slow performing queries and analyse there query plan manually or use an Index Tuning Advisor to see what indexes you are missing.
Add the missing indexes.

IQueryable Query Performance Tuning

2 Answers2