dotConnect/Oracle Entity Model generates slow count sql
Posted: 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:
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. 
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