Geek asks and answers

Oracle query using Entity Framework is ridiculously slow

Here is my setup:

  • Compiling in .NET 4.0 (I cannot do this above)
  • Using EntityFramework for Oracle 11gR2 Database
  • Using ODP.NET 11.2.0.3.20
  • Using Visual Studio 2012 Ultimate and the Oracle Developer Tool for Visual Studio 11.2.0.3.20
  • Querying using LINQ to Entity

So, here is my problem: I have several objects that I created using Oracle Developer Tools for Visual Studio (11.2.0.3.20). Some of the objects return results pretty quickly. However, with others that request a view / table containing more than 20 million records, it takes 10 minutes sequentially to return results (I checked this time with unit tests) for this simple query:

var member = (from m in context.Members
              where m.MemberID.Equals(memberId, StringComparison.OrdinalIgnoreCase)
              select m).FirstOrDefault();

I used to use Devart dotConnect for Oracle , which worked very well ... but my company is not updating its license for this product and they told me to use the new Oracle developer tools for Visual Studio to complete the tasks.

, OracleCommand, ODP.NET(Oracle.DataAccess.dll), . .

, , , , , Entity , ... , , , .

- , , Entity, ?


UPDATE:
, 10 . ( ) EDMX:

...
<EntityContainer Name="MyStoreContainer">
  <EntitySet Name="MY_TABLE" EntityType="MyDB.Store.MY_TABLE" store:Type="Views" store:Schema="MYUSERNAME" store:Name="MY_TABLE">
    <DefiningQuery>
      SELECT
      "MY_TABLE"."COL1" AS "COL1",
      "MY_TABLE"."COL2" AS "COL2",
      "MY_TABLE"."COL3" AS "COL3",
      "MY_TABLE"."COL4" AS "COL4",
      "MY_TABLE"."COL5" AS "COL5",
      "MY_TABLE"."COL6" AS "COL6",
      "MY_TABLE"."MEMBERSHIP_ID" AS "MEMBERSHIP_ID",
      "MEMBERS"."EXTRA_INFO1" AS "EXTRA_INFO1",
      "MEMBERS"."EXTRA_INFO2" AS "EXTRA_INFO2"
      FROM "MYUSERNAME"."MY_TABLE" "MY_TABLE"
      LEFT JOIN "MYUSERNAME"."MEMBERS" ON "MY_TABLE"."MEMBERSHIP_ID" = "MEMBERS"."MEMBER_ID"
    </DefiningQuery>
  </EntitySet>
...

, LEFT JOIN 10 , . LEFT JOIN ... . catch, EntitySet EntitySet, , . 4-5 , OracleCommand.
- , Entity , ?

+5
4

EntityFunctions.AsNonUnicode(memberId).

var member = (from m in context.Members
          where m.MemberID.Equals(EntityFunctions.AsNonUnicode(memberId), StringComparison.OrdinalIgnoreCase)
          select m).FirstOrDefault();

. https://community.oracle.com/message/10725648

+5

- oracle! Odac 12c : https://community.oracle.com/message/11065799#11065799

EntityFunctions.AsNonUnicode()

0

. @CameronP EntityFunctions.AsNonUnicode, . EF6 Oracle 12.1 * Managed Data Access. EF, TypeName Entity Model, !

Public Class Table1
    <Column("COLUMN_1", TypeName:="VARCHAR2")>
    Public Property Column1 As String
End Class
0

- Lazy Loading.

, .

, Lazy Loading:

Customers. , - :

var obj = myCustomerList.FirstOrDefault().ORDERS.ToList()

, Lazy Loading

-1

More articles:


All Articles