none
Slow query RRS feed

  • Question

  • I have a program that takes a long time to start up due to a query binding in WPF. Program start up is a datagrid that lists material items and date columns as an ObservableCollection. Filling the date columns with info on whether or not  an item has been booked on a mission is the bottle neck, the foreach statement.

    namespace BuildDataGrid
    {
        class CalendarViewModel : ModelBase
        {
            public ObservableCollection<CalendarItem> CalendarItems { get; set; }
    
            List<Mission> currentMissions = new List<Mission>(); // to store current mission data
            List<CurrentMissionData> missionData = new List<CurrentMissionData>();
    
            public CalendarViewModel()
            {
                // get the list of current missions from selected dates (default 14 day period) with helper method
                // getting the list of missions here I hope to reduce processor time when quering items in all missions.
                // The program will only query selected missions when looking for freight and missions dates for an item           
                currentMissions = HelperMethods.GetMissionsInDateRange(Variables.calendarStart, Variables.calendarEnd);
    
                CalendarItems = new ObservableCollection<CalendarItem>();
    
                using (MaterialEntities me = new MaterialEntities())
                {
                    // get the items data for the selected category
                    var itemList = from i in me.items
                                   where !i.isReturned
                                   where i.Category.categoryID == 1 // laptop category (81 laptops)
                                   orderby i.name
                                   select new CalendarItem
                                   {
                                       ID = i.itemID,
                                       Name = i.name,
                                       Model = i.model,
                                       Type = i.Type.typeName,
                                       Category = i.Category.categoryName,
                                       Status = i.Status.currentStatus,
                                       Note = i.note,
                                   };
    
                    // Fill the OClist with items from the category selected by the user
    		  // THIS IS THE BOTTLE NECK. ITERATING THROUGH EACH ITEM FOR EACH DAY AND CHECKING WETHER IN A MISSION
                    foreach (CalendarItem cal in itemList)
                    {
                        // get the missions data for each item as the method cannot be called in a linq select clause
                        List<CurrentMissionData> itemMissionDateData = new List<CurrentMissionData>();
                        if (currentMissions != null) // no need to process any items if there are no missions
                        {
                            for (DateTime dt = Variables.calendarStart; dt < Variables.calendarEnd; dt = dt.AddDays(1))
                            {
                                var result = (from m in currentMissions
                                              join link in me.LinkMissionItems on m.MissionID equals link.missionID
                                              where dt <= m.freightReturn && dt >= m.freightLeave
                                              where link.itemID == cal.ID
                                              select new CurrentMissionData
                                              {
                                                  ItemID = link.itemID,
                                                  MissionID = m.MissionID,
                                                  MissionName = link.Mission.MissionName,
                                                  MissionNumber = link.Mission.MissionNo,
                                                  FL = m.freightLeave,
                                                  FR = m.freightReturn,
                                                  MS = m.missionStart,
                                                  ME = m.missionEnd
                                              }).FirstOrDefault();
    
                                if (result != null)
                                {
                                    if ((dt.Date >= result.FL && dt.Date < result.MS) || (dt.Date > result.ME && dt.Date <= result.FR))
                                    {
                                        result.CellBackground = "Tan"; // in transport
                                    }
                                    else
                                    {
                                        result.CellBackground = "Orange"; // in mission
                                    }
                                }
    
                                itemMissionDateData.Add(result); // add to missions data list
                            
                            } // for (DateTime dt = Variables.calendarStart;..................
                        }
    
                        cal.MissionDates = itemMissionDateData; // add mission data list to Calendar item
                        CalendarItems.Add(cal); // update the calendar items list
                    
                    } // foreach (CalendarItem cal in itemList)
    
                } // using (MaterialEntities me = new MaterialEntities())
    
            } // CalendarViewModel()
    
        } // class CalendarViewModel 
    } // namespace BuildDataGrid

    Is there a more efficient way of writing this query or would it be more efficient to use an SQL query if it could be done?


    :-( Still trying to program

    Wednesday, February 18, 2015 3:16 PM

Answers

All replies

  • >>Is there a more efficient way of writing this query or would it be more efficient to use an SQL query if it could be done?

    For complex SQL queries you should execute raw SQL that you write yourself or call a stored procedure to fetch the data:
    http://stackoverflow.com/questions/20901419/how-to-call-stored-procedure-in-entity-framework-6-code-first
    https://msdn.microsoft.com/en-us/library/vstudio/bb896334(v=vs.100).aspx

    This will always be more efficient than letting EF generate complex queries for you.

    Also, in general the fewer roundtrips you make to the the database the better it is for the performance.

    Please remember to mark helpful posts as answer to close your threads and then start a new thread if you have a new question.

    • Marked as answer by Jonsey Friday, February 20, 2015 6:53 AM
    Wednesday, February 18, 2015 3:33 PM
  • You need to use a profiler to see if you are hitting the DB more than you should be hitting it unknowingly.
    Wednesday, February 18, 2015 10:41 PM
  • Hello Jonsey,

    Your query would perform an eager loading which could cause the master data to be repeated, you could check this link for details:

    http://stackoverflow.com/a/5522195

    You could have a try with lazy loading to see if performance of the query would be improved. Or check this link which has a summary for features would affect the performance.

    If you do not have a profiler, you could also use the one provided by EF as:

    db.Database.Log = Console.Write;

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, February 19, 2015 3:45 AM
    Moderator
  • Thank you all for your very helpful tips and advice. I would look at a profiler so I will investigate further. 

    I will try and use raw SQL. I am not so knowledgeable so I will start reading up on it and hopefully write more efficient queries.


    :-( Still trying to program

    Friday, February 20, 2015 6:55 AM