none
Entity Framework - Bad performance when querying remote databases RRS feed

  • Question

  • Hello community,

    I evaluated several .NET database access technologies for our new project and recognized a bad performance behavior when querying remote databases with the Entity Framework. The Entity Framework was 10 times slower than LinqToSql or the SqlClient. Maybe you can help me to explain or fix that?

    test parameters:

    - database:
        - SQL Server 2008 Enterprise
        - one table with 1000 records
        - table structure:

            [dbo].[Master](
                   [Id] [int] IDENTITY(1,1) NOT NULL,
                   [Value_Bit] [bit] NOT NULL,
                   [Value_Float] [float] NOT NULL,
                   [Value_DateTime] [datetime2](7) NOT NULL,
                   [Value_Uniqueidentifier] [uniqueidentifier] NOT NULL,
                   [Value_NVarchar100] [nvarchar](100) NOT NULL,
                   [Value_NVarchar1000] [nvarchar](1000) NOT NULL,
                   [InsertDate] [datetime] NOT NULL,
                   [UpdateDate] [datetime] NOT NULL,
                   [Version] [timestamp] NOT NULL)

    - benchmark application:
        - .NET Framework 4 and 4.5
        - hosted as a WinForms-Application

    - database access technologies:
        - Entity Framework 5.0 (RC) and 4.3.1
        - LinqToSQL
        - SqlClient

    - computers (client/servers):
        - three different computers with similar hardware
        - A and B are in the same subnet (e.g. 192.168.1.1 and 192.168.1.2)
        - C is in a different subnet than A and B (e.g. 192.168.2.1)


    I executed the sql query „select * from Master“ directly with each database access technology on different computers as client or server. The average time is the result of 1000 iterations.

    - test scenario 1:
        - client: A
        - server: A

        - Entity Framework: average time: 17 ms
        - LinqToSQL: average time: 20 ms
        - SqlClient: average time: 15 ms


    - test scenario 2:
        - client: A
        - server: B

        - Entity Framework: average time: 144 ms
        - LinqToSQL: average time: 141 ms
        - SqlClient: average time: 140 ms


    - test scenario 3:
        - client: A
        - server: C

        - Entity Framework: average time: 2145 ms
        - LinqToSQL: average time: 151 ms
        - SqlClient: average time: 156 ms


    - test scenario 4:
        - client: B
        - server: C
        
        - Entity Framework: average time: 2060 ms
        - LinqToSQL: average time: 141 ms
        - SqlClient: average time: 178 ms

     
    Why is the Entity Framework in test scenario 3 and 4 10 times slower than LinqToSQL or the SqlClient?

    I tested it with Entity Framework 4.3.1, 5 (RC) and under the .NET Framework 4 and 4.5, each time the same result. I disabled lazy loading and tracking, used compiled queries and pre-generation of view, but no difference.

    I used the SQL-Profiler to investigate the executed sql query and found out, that the query of the Entity Framework already took two seconds in the SQL Server (test scenario 3). If I execute the query from the Management Studio on computer A it took only 100 ms.

    I profiled my benchmark application with dotTrace (http://www.jetbrains.com) and found out, that the most of the execution time is consumed by the method "ToList". If I go deeper in the call stack I see the method "System.Data.SqlClient.SqlDataReader.GetString(Int32)" and at the end "SNINativeMethodWrapper.SNIReadSyncOverAsync(SafeHandle, IntPtr&, Int32)" that consumes all the time. LinqToSql uses the SqlClient too and has almost the same call stack, but the execution time is 10 times faster.

    I don't know what is going on under the hood. Maybe it has something to do with the computer name resolution, but I can ping computer C over IP-address and its computer name. Is there anyone who can explain that or have an advice how to make the execution faster?
     
    Thanks in advance

    Mathias
    Wednesday, September 5, 2012 12:14 AM

Answers

  • Hi,

    thanks for your suggestion. I have not tried it so far, because the problem respectively the huge time gap disappeared suddenly. I was told that the administrators changed something on the network, therefore I deduce that it has something to do with that, because I have not changed anything at the test parameters.

    Unfortunately, I have no idea what they have changed and guess that I will never be able to find out. Pretty dissapointing, because I know that I don't know what the reason is. As soon as I have further information I will post them.

    Thank you.
    Tuesday, September 11, 2012 10:37 AM

All replies

  • Try EnablePlanCaching = true on ObjectQuery..



    Best Regards, Please mark the answers which is helpful to you. thanks http://www.activebiz.co.uk

    Thursday, September 6, 2012 12:31 PM
  • Hi Mathias,

    Did you try Psdesai’s suggestion? Does it work? If you still have any problem, please feel free to let me know.

    Good day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, September 11, 2012 9:28 AM
  • Hi,

    thanks for your suggestion. I have not tried it so far, because the problem respectively the huge time gap disappeared suddenly. I was told that the administrators changed something on the network, therefore I deduce that it has something to do with that, because I have not changed anything at the test parameters.

    Unfortunately, I have no idea what they have changed and guess that I will never be able to find out. Pretty dissapointing, because I know that I don't know what the reason is. As soon as I have further information I will post them.

    Thank you.
    Tuesday, September 11, 2012 10:37 AM
  • Hi Alexander Sun,

    I am facing a similar issue and EnablePlanCaching  is set to True. Still facing the same issue (ie) most of the time is gone in SNIReadSyncOverAsync.

    Thanks

    Thursday, August 2, 2018 6:22 AM