none
OutOfMemory Exception

    שאלה

  • I am trying to query a set of tables but I keep getting an OutOfMemory exception. I was wondering if I could get some help restucturing the quesry. Here is the query that is running out of memory:

                            var orders = from o in orderHistory
                                         join v in greatPlainsContext.IV00102s on o.Sku.ToString() equals v.ITEMNMBR
                                         join d in greatPlainsContext.IV00101s on v.ITEMNMBR equals d.ITEMNMBR
                                         where v.PRIMVNDR != null &&
                                         v.PRIMVNDR.Length > 0 &&
                                         d.LOCNCODE != "aaa" &&
                                         v.ITEMNMBR != "123XYZ" &&
                                         d.ITMCLSCD != "FOO.COM"
                                         group o by v.PRIMVNDR into vendorList
                                         select new
                                         {
                                             Vendor = vendorList.Key,
                                             Summary = from h in vendorList
                                                       group h by new DateTime(h.Date.Year, h.Date.Month, 1) into skuHistory
                                                       orderby skuHistory.Key
                                                       select new { Period = skuHistory.Key, Quantity = skuHistory.Sum(s => s.Quantity) }
                                         };
    

     

    'orderHistory' lis a generic List (List<>) the rest involves tables in an SQL database.

    There are about 12,000 skus or part numbers in the List<> (orderHistory). There are probably about 20 different "vendors" designated by 'PRIMVNDR'. In this situation you can think of a vendor as a kind of group for the part numbers or SKUs. You could have serveral part numbers or skus for one vendor. The part number or SKU is denoted as 'ITEMNMBR' in the dabase tables.  I have a numch that the joins are inefficiently using memory but I am not sure how to resturcture it. Ideas?

    Kevin
     

    יום חמישי 11 יוני 2009 23:28

תשובות

  • This makes no sense. The above query should generate the following error message:

       NotSupportedException: Method 'Int32 Parse(System.String)' has no supported translation to SQL.

    because you cannot call int.Parse within a LINQ to SQL query. You can avoid this by declaring your skus as a string array instead:

       string[] skus = orderHistory.Select(o => o.Sku.ToString()).ToArray();

    Regarding your previous message, you need to finish your query locally. Something like this:

    var localQuery =
    from v in vendors
    select new
    {
        Vendor = v.Vendor,
        Summary =
           from h in orderHistory
           where h.Sku == v.Sku
           group h by new DateTime(h.Date.Year, h.Date.Month, 1) into skuHistory
           orderby skuHistory.Key
           select new { Period = skuHistory.Key, Quantity = skuHistory.Sum(s => s.Quantity) }
    }

    Joe

    Write LINQ queries interactively - www.linqpad.net
    • הוצע כתשובה על-ידי Joe AlbahariMVP, Editor יום שישי 10 יולי 2009 10:01
    • סומן כתשובה על-ידי KevinBurton יום שישי 10 יולי 2009 12:55
    שבת 13 יוני 2009 01:05
    משיב

כל התגובות

  • If this is a SQL database the where v.PRIMVNDR != null maybe should look for DBNull. Even still that should not affect thsi query if there are not many null valued PRIMVNDR's.

    Sorry no ideas.
    יום שישי 12 יוני 2009 03:52
  • The problem is that you're starting the query with a local collection. This will make the query very inefficient, in that it will have to round-trip SQL server for every element in the local input sequence, and store all the results locally before processing them.

    Instead, start with a LINQ to SQL table, and continue querying from there.

    The following should get you started:

       string[] skus = orderHistory.Select (o => o.Sku.ToString()).ToArray();

       var dbQuery = (
          from v in greatPlainsContext.IV00102s
          where skus.Contains (v.ITEMNMBR)
             && v.PRIMVNDR != null && v.PRIMVNDR.Length > 0 && v.ITEMNMBR != "123XYZ"
             && greatPlainsContext.IV00101s.Any (d => v.ITEMNMBR == d.ITEMNMBR && d.LOCNCODE != "aaa" && d.ITMCLSCD != "FOO.COM")
          select new { v.ITEMNMBR, v.PRIMVNDR }).ToArray();

    That will execute as a single efficient SQL statement, and give you an array of item numbers & primary vendor numbers. From there, you can finish the query locally.

    Joe


      


    Write LINQ queries interactively - www.linqpad.net
    • נערך על-ידי Joe AlbahariMVP, Editor יום שישי 12 יוני 2009 13:35 corrected typo
    • סומן כתשובה על-ידי KevinBurton יום שישי 12 יוני 2009 13:37
    • סימון כתשובה בוטל על-ידי KevinBurton יום שישי 12 יוני 2009 13:58
    יום שישי 12 יוני 2009 08:09
    משיב
  • Thank you. This makes sense. However I am not able to "connect the dots" from your snippets. For example I don't see that the array of strings "skus" is used any where. Also in the dbQuery I am not sure what sequence 'o.Contains' refers to.

    Thanks again for your help. Sorry I am a little slow.

    Kevin
    יום שישי 12 יוני 2009 13:29
  • Sorry - that should have been skus.Contains instead of o.Contains . I've corrected the query.

    Joe

    Write LINQ queries interactively - www.linqpad.net
    יום שישי 12 יוני 2009 13:36
    משיב
  • Thank you. I think I am almost there.

    It seems two pieces of infomation are lost in the projection to get the array of skus. One is the date. For example if skus 1,2, and 3 are from a particular vendor and the order history shows that 10 of 1 were ordered on 1/1/2009, 20 of 2 were ordered 1/2/2009, and 30 of 3 were ordered 1/3/2009 then the total for this vendor for the month of January would be 60. This sum I tried to incorporate by the final projection above by grouping the orders into monthly periods. The second piece of lost information is related and that is the quantity. So I need to be able to group by period (here I have chosen a period of one month) and vendor summing the quantity by period and vendor. While I think I understand what I want I am not sure how to incorporate this into an efficient LINQ query.

     

    Kevin

    יום שישי 12 יוני 2009 14:15
  • The code now looks like:

                        Dictionary<string, List<string>> result = new Dictionary<string, List<string>>(StringComparer.CurrentCultureIgnoreCase);
                        int[] skus = orderHistory.Select(o => o.Sku).ToArray();
                        using (GreatPlainsDataContext greatPlainsContext = new GreatPlainsDataContext())
                        {
                            var vendors = from v in greatPlainsContext.IV00102s
                                          where skus.Contains(int.Parse(v.ITEMNMBR.Trim())) &&
                                               v.PRIMVNDR != null &&
                                               v.PRIMVNDR.Length > 0 &&
                                               v.ITEMNMBR != "123XYZ" && 
                                               greatPlainsContext.IV00101s.Any(d => v.ITEMNMBR == d.ITEMNMBR &&
                                                                                    d.LOCNCODE != "aaa" &&
                                                                                    d.ITMCLSCD != "FOO.COM")
                                          select new { Vendor = v.PRIMVNDR, Sku = v.ITEMNMBR };
    
                            foreach (var vendor in vendors)
                            {
                                Debug.WriteLine(string.Format("{0} {1}", vendor.Vendor, vendor.Sku ));
                            }
                    }
    

    I am still getting an OutOfMemory exception.

    Kevin
    יום שישי 12 יוני 2009 18:08
  • This makes no sense. The above query should generate the following error message:

       NotSupportedException: Method 'Int32 Parse(System.String)' has no supported translation to SQL.

    because you cannot call int.Parse within a LINQ to SQL query. You can avoid this by declaring your skus as a string array instead:

       string[] skus = orderHistory.Select(o => o.Sku.ToString()).ToArray();

    Regarding your previous message, you need to finish your query locally. Something like this:

    var localQuery =
    from v in vendors
    select new
    {
        Vendor = v.Vendor,
        Summary =
           from h in orderHistory
           where h.Sku == v.Sku
           group h by new DateTime(h.Date.Year, h.Date.Month, 1) into skuHistory
           orderby skuHistory.Key
           select new { Period = skuHistory.Key, Quantity = skuHistory.Sum(s => s.Quantity) }
    }

    Joe

    Write LINQ queries interactively - www.linqpad.net
    • הוצע כתשובה על-ידי Joe AlbahariMVP, Editor יום שישי 10 יולי 2009 10:01
    • סומן כתשובה על-ידי KevinBurton יום שישי 10 יולי 2009 12:55
    שבת 13 יוני 2009 01:05
    משיב