none
Different execution time Application vs SSMS RRS feed

  • Question

  • Hi,

    I'm trying to optimize some linqToEntities queries.

    During the process, suddenly one query started to take 8 minutes to execute.

    However, if I capture the query on SQL Profiler and execute the query on SSMS, it takes only 1 sec.

    I can confirm the execution time (8 minutes) capturing the batch starting/batch completed events in SQL Profiler.

    I'm executing on a test environment, no locks holding the query. I cleared the plan cache, It's quite impossible that the query is getting a different execution plan from the application.

    Finally, I managed to get it back to 2 sec.: The problem is that, during the optimization, I decided to project the result of the query to an anonymous type and this raised the query from 1 sec to 8 min.

    When I got back with the final existing class on the projection, it got back to 2 sec. The difference is because the final class requires some calculations during the projection. I was planning to remove these calculations from the SQL execution when this happened.

    So, the question is: Why using an anonymous type for projection can raise the execution time from 1sec to 8 min.?

    The amount of records is 32K.

    Thank you in advance!

    Dennes


    • Edited by Dennes Friday, December 21, 2018 2:10 PM
    Friday, December 21, 2018 2:09 PM

All replies

  • However, if I capture the query on SQL Profiler and execute the query on SSMS, it takes only 1 sec.

    No object materialization with objects off of the ORM's virtual object model is taking place with object materialization slowing things down.

    When I got back with the final existing class on the projection, it got back to 2 sec. The difference is because the final class requires some calculations during the projection. I was planning to remove these calculations from the SQL execution when this happened.

    It sounds to me that you should be projecting using a concrete type like a DTO that has no business logic in it, since I would assume that you know the public property types that as suppose to be within the DTO. You act upon the DTO with a business object if need be.

    https://en.wikipedia.org/wiki/Data_transfer_object

    https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

    So, the question is: Why using an anonymous type for projection can raise the execution time from 1sec to 8 min.?

    I don't know and maybe you should just use a custom type in the projection, like a DTO.


    • Edited by DA924x Sunday, December 23, 2018 8:08 AM
    Saturday, December 22, 2018 12:33 PM
  • Hi Dennes,

    >>So, the question is: Why using an anonymous type for projection can raise the execution time from 1sec to 8 min.?

    As far as I know, passing an anonymous type to an EF query will cause EF to only select the fields you defined. Under normal circumstances, it should be to improve performance. 

    Please compare two generated SQL statement and check the different with them. based on my understanding. entity framework generate SQL statement and execute it in SQL Server and map to entity via the result of SQL Server. I would suggest that you could analysis the execution in SQL profile and check what steps cause the issue.

    In addition, If the issue still exists, could you please share related steps to reproduce the issue.

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 24, 2018 2:35 AM
    Moderator
  • Hi,

    The comparison was done with the SQL Statement. It was captured on SQL Server Profiler, as explained, and executed in SSMS. The time compared was the time in Profiler with the time in SSMS, so this difference should not include any materialization work, although it's possible that entity framework has some kind of problem with open data reader over the data, but this is not appearing in profiler, I'm not sure about how to prove this.

    I thought I had solved the problem, but I hadn't: The problem disappear for a while when I make an IIS Reset, and than returns back again.

    This already happened before, but with way smaller duration. Now it's huge.

    Thank you in advance!

    Dennes


    Dennes [http://bufaloinfo.cloudapp.net] Inscreva-se em meu treinamento on-line de T-SQL - [http://bufaloinfo.cloudapp.net/Cursos/linguagemsql.aspx]

    Monday, December 24, 2018 8:44 AM
  • I thought I had solved the problem, but I hadn't: The problem disappear for a while when I make an IIS Reset, and than returns back again.

    So this is a Web application. Are you implementing  any SoC is the solution, like using a DAL?

    https://en.wikipedia.org/wiki/Separation_of_concerns

    How is EF being used in the Web application?

    Can it be that you are reading the same data more than once as it iterates in the loop, the DBconnection has not been destroyed resulting  in a connected collection of objects and EF is going back to read the data again an create each object again as the loop iterates over the objects in the collection?

    Monday, December 24, 2018 11:15 AM
  • Hi,

    I found the solution.

    I was quite sure the query plans used for the query on SSMS and on the application were the same, but they were different.

    The cause of the difference is that SSMS is opening sessions with SET ARITHABORT enabled while on EF this option is not enabled. So, when EF sends the query, it's compiled by SQL Server and the plan is re-used by the application. However, when SSMS sends the query, the existing query plan isn't re-used, a new compilation is made and a new plan created.

    After identifying this, the rest was simple. The problem of the plan was really parameterization/parameter sniffing. The query result changes a lot according the parameters, the plan was being created with bad parameters and then being re-used.

    The solution: Apply an (OPTION RECOMPILE) to the EF query. Some time ago I wrote a library for this, it's published here - https://github.com/DennesTorres/HintsInterceptor

    I could also apply OPTIMIZE FOR UNKNOW, may be even better than the recompile, but the library doesn't implement it yet and I had not enough time to do it, it's on my task list.

    Thank you all for the help and I wish all of you a great new year !

    Dennes


    Dennes [http://bufaloinfo.cloudapp.net] Inscreva-se em meu treinamento on-line de T-SQL - [http://bufaloinfo.cloudapp.net/Cursos/linguagemsql.aspx]

    Monday, December 31, 2018 12:45 PM
  • Hi Dennes,

    Happy new year!

    I am glad to know that you resolve the issue and thanks for sharing the solution to us, it will be beneficial to other communities who have the similar if you mark it as answer. 

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 1, 2019 2:22 AM
    Moderator