Performance

With each new version of our Devart dotConnect for Oracle ADO.NET Entity Framework provider we pay special attention to improving its performance and configurability. It is pertinent to note some recent improvements: batch updates and flexible customization of Oracle Entity Framework provider behaviour. And now we are glad to present Oracle optimizer hints support in Entity Framework – a new feature for flexible tuning of SQL queries, generated by a user application.

Oracle Optimizer Hints

Oracle supports more than 60 different Oracle optimizer hints. You can see the complete list of Oracle optimizer hints and recommendations on their use in the Oracle Database documentation.

It is better to perform query optimization in external application, such as Devart dbForge Studio for Oracle, or any other tool for Oracle that you prefer. Queries, generated by Entity Framework, can be traced with dbMonitor and then copied and pasted to your SQL development application, where you can evaluate query performance, study execution plan, and choose optimizer hints to use.

Using Oracle Optimizer Hints as Oracle Function

Entity Framework provides not so many extensibility points for 3rd-party Entity Framework providers, so, to implement support for Oracle optimizer hints, we use database-specific functions feature in a non-standard way by adding the Devart.Data.Oracle.HINTS(string hints) function.

You can call this function directly in LINQ to Entities queries using the OracleFunctions class from the Devart.Data.Oracle.Entity assembly

var originalQuery = from emp in ctx.Exployees
                    ...
                    select emp;
var queryWithHints = originalQuery.Where(e => 
    OracleFunctions.Hints("INDEX(employees index1)"));
var result = queryWithHints.ToList();

or using query builder functionality of ObjectQuery (only when using ObjectContext, not DbContext).

var originalQuery = ctx.Exployees
                    .Where("Devart.Data.Oracle.HINTS('INDEX(employees index1)')")
                    ...
                    .Select(emp => emp);

Using Oracle Optimizer Hints as Extension Method

If you use ObjectContext, not DbContext, you can wrap this Devart.Data.Oracle.HINTS function and create an extension method, using the query builder feature of ObjectQuery.

public static class OracleExtensions {

    public static IQueryable<T> OracleHints<T>(
        this System.Data.Objects.ObjectQuery<T> source, string hints
    )
      where T : class {
      if (source == null)
        throw new ArgumentNullException("source");
      return source.Where("Devart.Data.Oracle.HINTS('" + hints + "')");
    }
}

Then you can use it in the following way:

var query = from emp in ctx.Exployees.OracleHints("FULL(employees) PARALLEL(employees, 5)")
            ...
            select emp;
var result = query.ToList();

Specifics and Limitations of Oracle Optimizer Hints in dotConnect for Oracle

We decided to always place hints after the first, upper-level SELECT keyword because structure of a generated SQL query does not always correspond to the structure of the LINQ to Entities query structure, and there is no unambiguous way to specify hints for a specific subquery.

var query = from emp in ctx.Exployees.OracleHints("FULL(employees) NOCACHE(employees)")
            ...
            select emp;

SELECT /*+ FULL(employees) NOCACHE(employees) */ last_name
  FROM employees;
...

If several hints are used, they are united in a single one.

var query = ctx.Employees
            ...
            select emp;
query = query.Where(e => OracleFunctions.Hints("FULL(employees)"));
query = query.Where(e => OracleFunctions.Hints("NOCACHE(employees)"));

SELECT /*+ FULL(employees) NOCACHE(employees) */ last_name
  FROM employees;

You cannot use string variables as OracleFunctions.Hints parameters, only string literals are allowed. For example, the following literal can be used:

var query = ctx.Employees.Where(e => OracleFunctions.Hints(
     "PARALLEL_INDEX(employees, index1, 3)"
));

and the following variable cannot:

string hints = "PARALLEL_INDEX(employees, index1, 3)";
var query = ctx.Employees.Where(e => OracleFunctions.Hints(hints);

Afterword

Thus, now dotConnect for Oracle Entity Framework provider has a new feature for flexible customization of SQL generation. Surely, Oracle optimizer hints is not a silver bullet and cannot solve all your problem, besides, one need some specific knowledge to use them. However, Oracle optimizer hints can be very useful in some cases, especially when changing database structure (creating/editing/deleting indexes, etc.) cannot be done for some reason. Oracle optimizer hints should be considered as one of the useful tools for developing applications using Entity Framework.

• DML Options
  • Batch Updates
    • Batch Updates Limitations
    • Batch Updates Settings
  • Other DML Options
• SQL Formatting Settings
• Workarounds
• Database Script Generation Settings
  • DeleteDatabaseBehaviour
  • DBMS-Specific Properties
    • Oracle
    • MySQL
    • PostgreSQL
• Provider-Specific LIKE Implementation
• Oracle-Specific Improvements
  • Multiple Includes Support Improvement in Oracle
  • NumberMappings Support in EDM Wizard
  • Improved StoreGeneratedPattern Attribute Handling

In the new version of Devart dotConnect ADO.NET providers for Oracle, MySQL, PostgreSQL and SQLite we have significantly extended functional capabilities of Entity Framework. We maximally fulfilled requests of our users, including those received via our new UserVoice. Our users mainly requested new functional features, more flexibility in behavior and configuration as well as better performance.
(more…)

In this article we will compare the performance of both most used inheritance types – Table-per-Hierarchy (TPH) and Table-per-Type (TPT), supported by LinqConnect starting from version 2.00. For a better understanding of these inheritance types, we will use the Model First approach when the object model is developed first and after that the database scheme is generated.

To develop an object model, the Entity Developer tool will be used.

Our tests are performed on the following object model:

We have a hierarchy of three entity classes: the base class Gadget and its descendants – MobilePhone and Netbook.
(more…)