dotConnect/Oracle Entity Model generates slow count sql

ckelley · Post by **ckelley** » Fri 26 Mar 2010 16:42

We're using dotConnect for Oracle 5.35.79 and the Entity Framework to support a paged Silverlight DataGrid. The application executes to queries against an Oracle view: the first to get the virtual item count for the pager and the second to get the details.

The sql generated for the virtual item count with two filter conditions looks like this:

Code: Select all

SELECT 
"Project1".C1 AS C1
FROM   ( SELECT 1 FROM DUAL) "SingleRowTable1"
LEFT OUTER JOIN  (SELECT 
	"GroupBy1".A1 AS C1
	FROM ( SELECT Count(1) AS A1
		FROM (SELECT 
      MyView.Column1 AS Column1, 
      MyView.Column2 AS Column2, 
      MyView.Column3 AS Column3, 
      MyView.DATE_VIEWED AS DATE_VIEWED, 
      MyView.USERID AS USERID, 
      FROM MyView MyView) "Extent1"
		WHERE (("Extent1".USERID = :p__linq__6) 
		AND ("Extent1".DATE_VIEWED >= ( CAST(:p__linq__8 AS TIMESTAMP(9)))))
	)  "GroupBy1" ) "Project1" ON 1 = 1

The performance of this query was so bad we had to materialize the view. I'm not an Oracle jock, but I believe this count query is inefficient because the view is nested inside a sub-select. Oracle is therefore unable to apply the where conditions directly to the view and must first get the entire view before filtering it. The sql generated is way more complicated that is necessary.

AndreyR · Post by **AndreyR** » Mon 29 Mar 2010 12:32

We just translate the expression tree returned by EF engine. If you are experiencing performance problems,
I recommend you to try switching to EF v4, where a number of performance improvements was implemented.

ckelley · Post by **ckelley** » Mon 29 Mar 2010 14:02

I assume that EF v4 is bundled with .NET 4.0 whose release date is 12 April 2010. Good timing.

AndreyR · Post by **AndreyR** » Mon 29 Mar 2010 14:25

You can make your tests using VS 2010 Release Candidate which is already available for a while.