Geek Answers Handbook

Entity Framework 5 and Oracle: Closing expression that affects performance when querying a true indexed field

I have a performance issue with Entity Framework 5 and Oracle DB.

I have a simple SQL selection: SELECT * FROM NOTE WHERE NOTENUMBER = '1A23456'

NOTENUMBER included in the index in a table named NOTE, but the field is NOT a primary / unique key.

  • When I execute the statement with Oracle SQL Developer, the results are returned quickly and the query plan shows that the RANGE SCAN is being used as it should.

  • When I use the Entity Framework, the generated SQL takes a lot longer (5 seconds versus 30 ms).

  • When I use the Entity Framework and the query with the primary key field (NOTE_KEY), the results are returned as quickly as with SQL Developer.

I suspect 2 things:

  • There are some issues with the EF and Oracle.DataAccess provider that do not use inaccessible unique code. This would help if I had debugging symbols for Entity Framework 5, but I can't find them anywhere.

  • The performance issue is somewhere in EF, regarding closures and / or how I use the shared repository pattern with EF:

    If I invoke my repository as follows:
    var notenumber = "1A23456";
    var notes = repository.All(n => n.NOTENUMBER == notenumber).ToList();
    The predicate enters the All method as:
    {n => (n.NOTE == value(Tester.Program+<>c__DisplayClass0).notenumber)}
    And the EfProf profiler tracks the resulting SQL as:

    SELECT "Extent1"."NOTE_KEY" AS "NOTE_KEY",
    "Extent1"."NOTENUMBER" AS "NOTENUMBER",
    "Extent1"."NOTETEXT" AS "NOTETEXT",
    FROM "NOTE_DBA"."NOTE" "Extent1"
    WHERE ("Extent1"."NOTENUMBER" = '1PSA0500237500' /* @p__linq__0 */)

    And the request takes ~ 5500 ms .


    On the other hand, if I call my repository as follows:
    var notes = repository.All(n => n.NOTENUMBER == "1A23456").ToList();
    Then the predicate comes as:
    {n => (n.NOTENUMBER == "1A23456")}
    And the EfProf profiler tracks the resulting SQL as:

    SELECT "Extent1"."NOTE_KEY" AS "NOTE_KEY",
    "Extent1"."NOTENUMBER" AS "NOTENUMBER",
    "Extent1"."NOTETEXT" AS "NOTETEXT",
    FROM "NOTE_DBA"."NOTE" "Extent1"
    WHERE ('1PSA0500237500' = "Extent1"."NOTENUMBER")

    And the request takes ~ 30 ms .

    Thus, the only difference is the order of the condition in the WHERE clause and the fact that the latter does not seem to have a parameter replaced by EF


I am using VS2010 and .NET4, as well as the EF5 link (v4.4.0.0). Universal repository:

 public IQueryable<NOTE> All(Expression<Func<NOTE, bool>> predicate = null) { var setOfNotes = GetDbSet<NOTE>(); var notesQuery = from note in setOfNotes select note; if (predicate != null) { notesQuery = notesQuery.Where(predicate); } return notesQuery; }

I tried to create CompiledQuery, I tried to use setOfNotes.AsNoTracking() , and I tried to configure .NET 4.5 - without any performance differences.

One of the ways I was able to quickly get this particular query was to use Oracle Data Data for .NET (ODB.NET) and create the query manually, but I would prefer not to stick with this solution. Again, if I use the main field in the where clause, the query is executed quickly even with EF and the same All-method.

So the problem seems to be somewhere in EF. I feel like I can learn a lot more if I only had characters for EntityFramework.dll.

There may be a problem with how EF calls predicates? How to replace the parameter "@ p_linq_0" inside EF?

+4
source share
3 answers

I had a similar problem. The reason the index was not used in my case was because I was passing a string (unicode) from .NET as a parameter. This was mapped to a database field other than unicode.

The solution was to convert the string parameter to non-unicode before passing it to the where clause:

 using System.Data.Objects; EntityFunctions.AsNonUnicode( myUnicodeParam)
+5
source

To find out the generated SQL Entity Framework uses the ToTraceString method, see http://msdn.microsoft.com/en-us/library/system.data.objects.objectquery.totracestring.aspx

It is possible that the NOTE table has relationships and active download is enabled. In this case, the created EF SQL will try to load all the associated data.

0
source

I see several design errors and problems in the code.

Firstly, the custom method is "Everything." A line of code that selects all notes in notesQuery does nothing.

 var notesQuery = from note in setOfNotes select note;

Remember that 'setOfNotes' is already an IQueryable of type Note. The statement effectively says: "Select all notes to select all notes." LINQ will completely remove this statement from the inside (with little performance), so it can be safely removed from your code.

Try changing the function to:

 public IQueryable<NOTE> All(Expression<Func<NOTE, bool>> predicate = null) { var setOfNotes = GetDbSet<NOTE>(); return (predicate == null) ? setOfNotes : setOfNotes.Where(predicate); }

There are big fundamental problems in design. For .NET collections, the All method is used to determine whether the predicate is true for each element in the collection. This does not mean "return all items." Of course, you create your own repository, but naming conflicts with standard use of .NET. I would actually suggest you completely get rid of your custom repository.

LINQ is a "language integrated query" - the whole point is to integrate it with your code. Wrapping another layer around it is pointless and contradicts intuition. DbSet implements the IQueryable interface - exposes this directly and requests it in your code. This allows LINQ to cache queries more efficiently and avoid redundancy. IQueryable already has a repository, it makes no sense to create another. You simply threw out LINQ power and returned to the days of stored procedures.

If you have complex queries used in many places, just write an extension method on IQueryable that returns another IQueryable. Then you can GetDbSet (). SliceAndDice ()!

Returning to the original problem, you can mix assignment with comparison. The code you included included:

 var notes = repository.All(n => n.NOTENUMBER = notenumber).ToList();

This is not true, its attempt to assign "notenumber" to the n.NOTENUMBER property, not an equality check. Perhaps it was a typo in the column. Try instead:

 var notes = repository.All(n => n.NOTENUMBER == notenumber).ToList();

Or even better:

 var notes = GetDbSet<NOTE>().Where(n => n.NOTENUMBER == notenumber).ToList();

I suspect that the equality error was just a typo, but the problem is probably that you "overlay" the custom repository on the IQueryable repository and pass the expression between them and discard the EF's ability to cache correctly.

0
source

More articles:


All Articles