none
LINQ TO EF, very slow results RRS feed

  • Question

  • Hi,
    I am starting using linq, very interesting and powerful, but when the query is complicated, it is very hard(for me now) to make sure I find the best way to make it efficient.
    the original sql query is this:

    SELECT CONVERT(CHAR(10),history.histdate,110) AS PDate,
           history.histprod ASProduct ,
           history.histsequence AS Sequence,
           history.histwarehouse AS Warehouse ,
           products.prddesc1 AS Description,
           SUM(history.histprc) AS PrcQty,
           history.histprcum AS PrcUM ,
           SUM(history.histtare) AS Tare,
           SUM(history.histMoisturetare) AS MTare,
           AVG(history.histprc) AS AVG1 ,
           SUM(history.histalt) AS AltQty,
           history.histaltum AS AltUM
    FROM history,products

    WHERE history.histprod = products.prdcode
          AND history.histsequence = products.prdsequence
          AND history.histwarehouse = products.prdwarehouse
          AND history.histmistakeflag = 0

    GROUP BY history.histdate,
             history.histprod,
             history.histsequence ,
             history.histwarehouse,
             history.histaltum,
             history.histprcum ,
             history.histlinepcid,
             products.prddesc1

    HAVING (history.histdate >= '8/8/2011 12:00:00:00 AM')
         AND (history.histdate <= '8/15/2011 12:00:00:00 AM')
         AND (history.histlinepcid = 1)

    ORDER BY 
             history.histdate DESC,
             history.histprod,
             history.histsequence,
             history.histwarehouse

    this query in SSMS takes no time running, when I converted to LINQ, obviously I did it wrong, it takes about 10 seconds
    here is the linq I created that is wrong, if someone can help, pointing out where did it got wrong I really appreciated.:


    var DataSummary = from h in sc.histories
                      where (h.histdate >= minDate && h.histdate <= maxDate)
                         && (h.histshift == shift)&& (h.histmistakeflag == false)
                      join p in sc.products
                      on new {h.histprod,h.histsequence,h.histwarehouse}
                      equals new{ histprod = p.prdcode, histsequence = p.prdsequence, histwarehouse = p.prdwarehouse } into tempjoin

    from f in tempjoin.DefaultIfEmpty()

    group h by new{ h.histdate, h.histprod, h.histsequence,h.histwarehouse, h.histaltum, h.histprcum,h.histlinepcid, f.prddesc1} into g

    orderby g.FirstOrDefault().histdate descending,g.FirstOrDefault().histprod,g.FirstOrDefault().histsequence,g.FirstOrDefault().histwarehouse
    select new{
             PDate = g.Key.histdate,
             Product = g.Key.histprod,
             Sequence = g.Key.histsequence,
             Warehouse = g.Key.histwarehouse,
             Description = g.Key.prddesc1,
             PrcQty = g.Sum(p => p.histprc),
             PrcUM = g.FirstOrDefault().histprcum,
             AltQty = g.Sum(p => p.histalt),
             AltUM = g.FirstOrDefault().histaltum,
             Tare = g.Sum(p => p.histtare),
             MTare = g.Sum(p => p.histMoistureTare),
             AVG1 = g.Average(p => p.histprc)
            };
         return (IQueryable)DataSummary;

     


    Oscar

    Monday, August 15, 2011 7:09 PM

Answers

  • Thanks again for these valuable links, I did a little reading, what I did to resolve the problem is eliminate the join at the top, and because I know the this is a summary query it will not return many records, the only reason for the join was to get the description of the product, so I read the description at the end by using the key values and the query returned in one second instead of 10 seconds before, huge change here is the new linq query :

     var Det =  from h in sc.histories
                           where (h.histdate >= minDate && h.histdate <= maxDate)
                           && (h.histshift == shift)
                           && (h.histmistakeflag == false)

                           group h by new { h.histdate, h.histprod, h.histsequence,
                                           h.histwarehouse, h.histaltum, h.histprcum,
                                           h.histlinepcid } into g

                           orderby g.FirstOrDefault().histdate descending,
                                  g.FirstOrDefault().histprod,
                                  g.FirstOrDefault().histsequence,
                                  g.FirstOrDefault().histwarehouse

                           select new
                           {
                            
                              PDate =   g.Key.histdate,
                              Product = g.Key.histprod,
                              Sequence = g.Key.histsequence,
                              Warehouse = g.Key.histwarehouse,
                              Description = (from p in sc.products
                                             where (p.prdcode == g.Key.histprod && p.prdsequence == g.Key.histsequence &&  p.prdwarehouse == g.Key.histwarehouse)
                                             select new {descr = p.prddesc1}),
                              PrcQty = g.Sum(p => p.histprc),
                              PrcUM = g.FirstOrDefault().histprcum,
                              AltQty = g.Sum(p => p.histalt),
                              AltUM = g.FirstOrDefault().histaltum,
                              Tare = g.Sum(p => p.histtare),
                              MTare = g.Sum(p => p.histMoistureTare),
                              AVG1 = g.Average(p => p.histprc),
                           };
                 return (IQueryable)Det;


    Oscar
    • Marked as answer by Oscar Vargas Monday, August 15, 2011 8:59 PM
    Monday, August 15, 2011 8:58 PM

All replies

  •  
     
    Also use a profiler like SQL Profiler to view the generated T-SQL being
    generated and how the Select is executing when it's ran.
     
    Monday, August 15, 2011 7:46 PM
  • Thanks for the links, I did use sql profiler and also did this in VS Console.WriteLine(((ObjectQuery)DataSummary).ToTraceString())

    and the query looks so far like a normal query(I know it will look different but not as this one looks) and I did try to parse it in SSMS, took me a lot of time to figure it out to actually be able to run it at SSMS it took 10 sec to run, but it is totally huge query that I cannot believe it is right, actually the results are correct, the actual query its totally wrong and dificult to follow, it made it very complicated.

    Thanks


    Oscar
    Monday, August 15, 2011 8:01 PM
  • Thanks again for these valuable links, I did a little reading, what I did to resolve the problem is eliminate the join at the top, and because I know the this is a summary query it will not return many records, the only reason for the join was to get the description of the product, so I read the description at the end by using the key values and the query returned in one second instead of 10 seconds before, huge change here is the new linq query :

     var Det =  from h in sc.histories
                           where (h.histdate >= minDate && h.histdate <= maxDate)
                           && (h.histshift == shift)
                           && (h.histmistakeflag == false)

                           group h by new { h.histdate, h.histprod, h.histsequence,
                                           h.histwarehouse, h.histaltum, h.histprcum,
                                           h.histlinepcid } into g

                           orderby g.FirstOrDefault().histdate descending,
                                  g.FirstOrDefault().histprod,
                                  g.FirstOrDefault().histsequence,
                                  g.FirstOrDefault().histwarehouse

                           select new
                           {
                            
                              PDate =   g.Key.histdate,
                              Product = g.Key.histprod,
                              Sequence = g.Key.histsequence,
                              Warehouse = g.Key.histwarehouse,
                              Description = (from p in sc.products
                                             where (p.prdcode == g.Key.histprod && p.prdsequence == g.Key.histsequence &&  p.prdwarehouse == g.Key.histwarehouse)
                                             select new {descr = p.prddesc1}),
                              PrcQty = g.Sum(p => p.histprc),
                              PrcUM = g.FirstOrDefault().histprcum,
                              AltQty = g.Sum(p => p.histalt),
                              AltUM = g.FirstOrDefault().histaltum,
                              Tare = g.Sum(p => p.histtare),
                              MTare = g.Sum(p => p.histMoistureTare),
                              AVG1 = g.Average(p => p.histprc),
                           };
                 return (IQueryable)Det;


    Oscar
    • Marked as answer by Oscar Vargas Monday, August 15, 2011 8:59 PM
    Monday, August 15, 2011 8:58 PM