5 Replies Latest reply on Aug 10, 2012 6:04 PM by 905711

    Performance issue when ef generates sql casting number in where clause

    905711
      EF5 (runtime version=v4.0.30319), oracle dataaccess.dll v4.0.30319, oracle db 10.2.0.4, vs2010.
      We implement a C# class Account with key property AccountId declared as Int.64. The database table account.accountid field declared as number(18,0). When I use poco class and map my c# object to db table, ef creates a weird sql casting accountid to number(19,0) in where clause. This is a big performance issue due we get full scan table instead of pk index search. When I changed my c# Account.AccountId to decimal then ef doesn’t cast anymore. Are you aware of the casting in where clause? Is it going to be fixed? Any other way I can work around this issues?
      Thank you very much.
      Luda

      Db code
      CREATE TABLE ELVIS.ACCOUNT
      (
      ACCOUNTID
      ....
      }
      C# code
      ToTable("ELVIS.ACCOUNT");
      HasKey(p => p.AccountId);
      Property(p => p.AccountId).HasColumnName("ACCOUNTID");
      ....
      public class Account
      {
      [DataMember]
      public Int64 AccountId { get; set; }
      ....
      }

      AccountId as Int.64: SQL and Run time for 50 calls.
      SELECT CAST( "Extent1"."ACCOUNTID" AS number(19,0)) AS "C1", "Extent1"."ACCOUNTNUMBER" AS "ACCOUNTNUMBER", CAST( "Extent1"."APPLICATIONID" AS number(10,0)) AS "C2", "Extent1"."COMPANYCODE" AS "COMPANYCODE", "Extent1"."REGIONCODE" AS "REGIONCODE", "Extent1"."OFFICECODE" AS "OFFICECODE", "Extent1"."ACCOUNTTYPECODE" AS "ACCOUNTTYPECODE" FROM "ELVIS"."ACCOUNT" "Extent1" WHERE (1352840 = ( CAST( "Extent1"."ACCOUNTID" AS number(19,0))))
      ElapsedTime=00:06:01.19 TimeToComplete=361196



      Changed to decimal:
      public class Account
      {
      [DataMember]
      public decimal AccountId { get; set; }
      ....
      }
      SQL and Run time for 50 calls.
      SELECT "Extent1"."ACCOUNTID" AS "ACCOUNTID", "Extent1"."ACCOUNTNUMBER" AS "ACCOUNTNUMBER", "Extent1"."APPLICATIONID" AS "APPLICATIONID", "Extent1"."COMPANYCODE" AS "COMPANYCODE", "Extent1"."REGIONCODE" AS "REGIONCODE", "Extent1"."OFFICECODE" AS "OFFICECODE", "Extent1"."ACCOUNTTYPECODE" AS "ACCOUNTTYPECODE" FROM "ELVIS"."ACCOUNT" "Extent1" WHERE (1352840 = "Extent1"."ACCOUNTID")
      ElapsedTime=00:00:08.05 TimeToComplete=8057