Performance of ordered queries with the Skip() and Take()

[HCRoman]

Hi devart team,
in version 6.30 you anounced for LinqConnect
...
Performance of ordered queries with the Skip() and Take() methods is improved: the 'rownum' pseudo-column is now used instead of the 'row_number' function


is this also possible for dotConnect for Oracle

The performance of the generated SQL for Entity-Framework is verry poor under some circumstances (JOINS in the underlying query)

SELECT
...
row_number() OVER (ORDER BY "Extent1".BETRIEBSSTAETTEID ASC) AS "row_number" FROM BETRIEBSSTAETTE_V02 "Extent1"
WHERE "Extent1".QUARTAL = 20114
ORDER BY "Extent1".BETRIEBSSTAETTEID ASC
) "Filter1"
WHERE "Filter1"."row_number" > :skip AND ROWNUM :skip AND ROWNUM <= :take
ORDER BY "Filter1".BETRIEBSSTAETTEID ASC

The performance is acceptable < 1 second


Any Help?

Roman

[Shalex]

Thank you for your suggestion. We will investigate the issue and notify you about the results as soon as possible.

[HCRoman]

Hi devart team,

Any new information available for this problem???

Roman

[Pinturiccio]

We plan to implement some changes that should resolve this and other related optimization issues. However, we cannot provide any timeframe for these changes at the moment.

[rolandjoebstl]

Hi,
is this issue still existing?

I have the same performance problem in the version: 7.5.179.0 .
This issue is essential in our software solution.
Because we use a DevExpress-DataGrid, which supports paging. But when the datatable is very large the performance goes down. And the performance lack is caused through the same issue as here described.

[Shalex]

You are using Entity Framework, aren't you? The current situation is the same.

The solution of the problem will come with Oracle 12 in which the support of paging in SQL is announced. We, of course, will support the changed SQL generation in our provider just after Oracle 12 release.

[rolandjoebstl]

Hi,
yes we are using the entity framework.

Is then a solution only available for oracle 12 or will you change the generation behavior for older oracle versions too?

[Shalex]

Is then a solution only available for oracle 12 or will you change the generation behavior for older oracle versions too?

The solution will be available only for Oracle 12.

[bobcat1506]

This is a huge problem for us in Oracle 11. Please fix this in dotConnect.

[MariiaI]

This is a huge problem for us in Oracle 11. Please fix this in dotConnect.

We have contacted you by e-mail.