Linq to dataset, Query optimization

Question

I have following linq queries:

var itembind = (from q in dsSerach.Tables[0].AsEnumerable()
            select new
                           {
                               PatternID = q.Field<int>("PatternID"),
                               PatternName = q.Field<string>("PatternName") + " " + q.Field<string>("ColorID") + q.Field<string>("BookID"),
                               ColorID = q.Field<string>("ColorID"),
                               BookID = q.Field<string>("BookID"),
                               CoverImage = (from img1 in objJFEntities.ProductImages.ToList()
                                             where img1.PatternName.ToLower() == q.Field<string>("PatternName").ToLower()
                                             select new CoverImage
                                             {
                                                 URL = "Images/MediumPatternImages/" +
                                                     q.Field<string>("PatternName") + "_" + q.Field<string>("ColorID") + q.Field<string>("BookID") + q.Field<string>("ImageExtension"),
                                                 ID = q.Field<int>("ProductImageID")
                                             }).FirstOrDefault(),
                               TotalCount = q.Field<int>("TotalCount")
                           }).Distinct();



var patterns = (from r in itembind
            group r by new { r.PatternID, r.ColorID } into g
            select new SearchPattern
            {
                PatternID = g.Key.PatternID,
                PatternName = string.Join(",", g.OrderBy(s => s.ColorID).OrderBy(s => s.BookID)
                                          .Select(s => String.Format("<a href='{0:s}' title='{1:s}'>{2:s}</a><br />",
                                                     new object[] { String.Format("Product.aspx?ID={0}&img={1}", g.Key.PatternID, s.CoverImage.ID), s.PatternName, s.PatternName })).FirstOrDefault()),
                CoverImage = g.Count() > 1 ? (from img1 in objJFEntities.ProductImages.ToList()
                                              where img1.ProductImageID == g.Select(i => i.CoverImage.ID).FirstOrDefault() && img1.ColorID.ToString() == g.Key.ColorID

                                              select new CoverImage
                                              {
                                                  URL = "Images/MediumPatternImages/" +
                                                      img1.PatternName + "_" + img1.ColorID + img1.BookID + img1.ImageExtension,
                                                  ID = img1.ProductImageID
                                              }).FirstOrDefault() : g.Select(i => i.CoverImage).FirstOrDefault()


            }).ToList();

these queries are taking more then 1 minute to execute for the 1000 records only. The dsSearch is a dataset filled with records returned from my procedure in SQL. Am using entity framework. The site is deployed with IIS7.0. The SQL server 2008 is in use.

I got "Error Message:Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." , "Cannot open database "DB" requested by the login. The login failed." & "The underlying provider failed on Open." kind of error very frequently site.

Please tell me how to optimize such a query.

EDIT:

Here is the procedure

http://pastie.org/7160934

Answer 1

Rolfvm is correct in pointing out that objJFEntities.ProductImages causes the problem, but the analysis is a bit different. You fetch the entire ProductImages table into memory for each iteration of the query when you enumerate over it. So one optimization would be to fetch the images first in a collection and use that collection in the query statement

var localImages = objJFEntities.ProductImages.ToList();
...
CoverImage = (from img1 in localImages....

But then, your query seems to do far too much. You build the first part itembind without executing it. Then you build the second part (var patterns = (from r in itembind) and execute it by ToList(). But in the second part you never use the CoverImage from the first part. So creating these is a waste of resources. (Or you skimmed the code, hiding another use of the first part).