dotConnect/Oracle Entity Model generates slow count sql

dotConnect/Oracle Entity Model generates slow count sql

Postby 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. :cry:
ckelley
 
Posts: 5
Joined: Wed 24 Mar 2010 04:10
Location: Berkeley,CA USA

Postby 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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby 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.
ckelley
 
Posts: 5
Joined: Wed 24 Mar 2010 04:10
Location: Berkeley,CA USA

Postby AndreyR » Mon 29 Mar 2010 14:25

You can make your tests using VS 2010 Release Candidate which is already available for a while.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle