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

Have you confirmed that your connection string is correct? Are you using Integrated Security? — Phil Murray, Mar 29 '13 at 12:05
yes, I have verified this. am not using Integrated security in connection string — Neha, Mar 29 '13 at 12:06
The error certainly suggests its the connection to the database timing out and not the query (I think the default time out is 60 seconds). I would check the connection and your IIS settings. — Phil Murray, Mar 29 '13 at 12:10
but this was not always happens. sometime for 3000 records even it works out. — Neha, Mar 29 '13 at 12:15
Can you simplify this? It's hard for me to see where you're accessing the database. LINQ to DataSet only queries against in-memory data. — John Saunders, Mar 29 '13 at 21:32

score 3 · Answer 1 · answered Mar 29 '13 at 12:46

3

In the first query you are doing a objJFEntities.ProductImages.ToList() , with the ToList() call you are fetching every entry from the database, and later filter the results in memory.

answered Mar 29 '13 at 12:46

Rolfvm

336
2
9

I tried after removing ToList() . Query won't give any output after that. – Neha Mar 29 '13 at 13:24
But the ToLower() action is propably not supported by the mapping to sql (not sure which db you are using). So it depends on the database collaction you are using. Best thing to see why your query is not returning any results is to use the sql profiler which is included with the sql management studio – Rolfvm Mar 29 '13 at 13:40

score 2 · Accepted Answer · answered Mar 29 '13 at 20:37

2

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).

answered Mar 29 '13 at 20:37

Gert Arnold

105,341
31
202
291

Thanks, pushing collection to separate works & it now takes 1/4 the of the previous time.I understand the difference, many thanks. Please once verify my procedure too & give me the suggestion if that could be optimized too. – Neha Mar 30 '13 at 08:55
Well, as I said, I wonder why you create the `CoverImage` objects. And you could try to fetch only the `ProductImages` you're going to need. – Gert Arnold Mar 30 '13 at 10:20
The coverImage is used in pattern query & as well the result in my UI – Neha Mar 30 '13 at 10:30

Linq to dataset, Query optimization

2 Answers2