none
LINQ Query is too much time to execute . How to reduce the time ? RRS feed

  • Question

  • Hi,

    I am experiencing a strange issue when I use a method related to LINQ-SQL

    When I ran the LINQ query below from from the application in debug mode its taking 10 minutes to load the data.

           packUnpackAvtivityList = (from sm in dataContext.Baking.PackUnpackActivity
                                              where (sm.PackingDate >= startDate || sm.UnpackingDate >= startDate)
                                                       && (sm.PackingDate < endDate || sm.UnpackingDate < endDate)
                                                       && furnaceList.Contains(sm.FurnaceNo)
                                                       && (sectionNo == null || sm.SectionNo == sectionNo)
                                                 select sm).ToList();

    However, when the ran the same SQL query from SQL server its taking only 0 seconds to load.

    Any idea why I am getting this huge difference in time during the execution ??? Please help me with some thoughts.

    select * from  Baking.PackUnpackActivity sm
     
      where (sm.PackingDate >= '8/14/2015 12:00:00 AM' OR sm.UnpackingDate >= '8/14/2015 12:00:00 AM')
      AND  (sm.PackingDate < '8/19/2015 12:00:00 AM' OR sm.UnpackingDate < '8/19/2015 12:00:00 AM')                                                 
     AND  sm.FurnaceNo IN(1,2)
     AND ( sm.sectionNo = null OR sm.SectionNo = null)
    

     
    Tuesday, August 18, 2015 11:52 AM

Answers

  • What you can do is rather than the .ToList() use .ToString().  Then the variable will hold the actual SQL sent to the server.  Copy this and use SQL Server Management Studio to see what the difference in execution is.

    Lloyd Sheen

    Sunday, August 23, 2015 8:54 PM

All replies

  • Hi pepcoder,

    I suggest you adding a break point before this line when run it in application. which is to check if the time is consumed in this sentence.

    If the time is consumed in this linq, you could share the project with us via OneDrive, then we could help you better.

    If not, you need to improve the performace of the application, it is not related to Linq.

    Regards,
    Youjun Tang


    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, August 20, 2015 2:08 AM
  • Hi Youjun Tang,

    I have moved that logic into a SQL Stored Procedure and the execution time was reduced from 3.5 minutes to 6 seconds. Current project I am working is based on entity framework, Could be the LINQ-SQL code block I have mentioned would be an expensive one for the compiler i guess.

    Thanks for your comments.

    -Pep

    Sunday, August 23, 2015 4:27 AM
  • What you can do is rather than the .ToList() use .ToString().  Then the variable will hold the actual SQL sent to the server.  Copy this and use SQL Server Management Studio to see what the difference in execution is.

    Lloyd Sheen

    Sunday, August 23, 2015 8:54 PM