Support for DbInExpression

Support for DbInExpression

Postby nlz242 » Thu 02 Jul 2015 14:02

Hello,

I'm having some performance issues using Contains() on larger list.
After digging around, i found out that in Entity Framework 6.0, this has been adressed by adding DbInExpression. However, the provider has to support it. I dug around in my version of dotConnect (8.4.303.6) and found that the Provider's DbProviderManifest answers "false" to "SupportsInExpression()".

Here is the WorkItem for the changes in EF6 : http://entityframework.codeplex.com/workitem/245

So my question(s):
Are DbInExpression supported in more recent versions ? If not, are there plans to support it ?

This is a pretty big feature for us has we have data that comes from a third party source which is basically a list of IDs and we need to fetch all those items into a grid.
There are, of course, ways around this issues but most are not elegant.

EDIT: Installed 8.4.447 (trial) to look if "SupportsInExpression()" still answers false. It does.
Also found this interesting wiki page :
https://entityframework.codeplex.com/wikipage?title=Rebuilding%20EF%20providers%20for%20EF6
Which has a paragraph about this :

Native support for Enumerable.Contains

EF6 introduces a new expression type, DbInExpression, which was added to address performance issues around use of Enumerable.Contains in LINQ queries. The DbProviderManifest class has a new virtual method, SupportsInExpression, which is called by EF to determine if a provider handles the new expression type. For compatibility with existing provider implementations the method returns false. To benefit from this improvement, an EF6 provider can add code to handle DbInExpression and override SupportsInExpression to return true. An instance of DbInExpression can be created by calling the DbExpressionBuilder.In method. A DbInExpression instance is composed of a DbExpression, usually representing a table column, and a list of DbConstantExpression to test for a match.
nlz242
 
Posts: 9
Joined: Fri 17 Apr 2015 15:09

Re: Support for DbInExpression

Postby Shalex » Thu 02 Jul 2015 16:53

We will investigate the question to find out the performance gains if the DbInExpression feature is implemented and notify you about the result.

As for the generated SQL, our provider always creates IN for comparisons with all EF versions (starting from EFv1):
(column = :p0) OR (column = :p1) OR ...
-->
column IN (:p0, :p1, ...)
Shalex
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44

Re: Support for DbInExpression

Postby nlz242 » Thu 02 Jul 2015 17:20

Yes, indeed the query are generated using IN clauses and the query themselves execute quickly. The query generation is what is taking very long.
In my test, if my list contains 8000 items, it takes more than 100s to generate.
500 items starts taking a few seconds, 2000 reaches the 10s mark. The growth isnt linear too. 4000 items takes 30s,

Of course, if i use short list (10, 20 items) it's super fast and not an issue, the problem really lies in the time it takes to generate the queries once there are many items, which is what is adressed by the new DbInExpression (instead of many Or expressions).

Thanks for looking into it, i can't wait to know what kind of gains you guys achieve on large lists!
Last edited by nlz242 on Fri 24 Jul 2015 18:59, edited 3 times in total.
nlz242
 
Posts: 9
Joined: Fri 17 Apr 2015 15:09

Re: Support for DbInExpression

Postby Shalex » Thu 16 Jul 2015 14:09

The performance of generating SQL for Enumerable.Contains in LINQ queries is improved by supporting a new expression type, DbInExpression, in Entity Framework 6. This feature is available in the new (8.4.457) build of dotConnect for Oracle: http://forums.devart.com/viewtopic.php?f=1&t=32142.
Shalex
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44

Re: Support for DbInExpression

Postby nlz242 » Fri 24 Jul 2015 18:42

Hello,

We tested the changes with great results. Generating the query with a .Contains using a list with more than 14 000 items is done in less than a second. Previously we were above 100 seconds for 8 000 items.

Thanks alot for adding this in!
nlz242
 
Posts: 9
Joined: Fri 17 Apr 2015 15:09


Return to dotConnect for Oracle