Performance of ordered queries with the Skip() and Take()
Performance of ordered queries with the Skip() and Take()
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
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
Any new information available for this problem???
Hi devart team,
Any new information available for this problem???
Roman
Any new information available for this problem???
Roman
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
-
- Posts: 3
- Joined: Mon 27 May 2013 06:48
Re: Performance of ordered queries with the Skip() and Take()
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.
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.
Re: Performance of ordered queries with the Skip() and Take()
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.
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.
-
- Posts: 3
- Joined: Mon 27 May 2013 06:48
Re: Performance of ordered queries with the Skip() and Take()
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?
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?
Re: Performance of ordered queries with the Skip() and Take()
The solution will be available only for Oracle 12.rolandjoebstl wrote:Is then a solution only available for oracle 12 or will you change the generation behavior for older oracle versions too?
-
- Posts: 1
- Joined: Fri 12 Sep 2014 12:11
Re: Performance of ordered queries with the Skip() and Take()
This is a huge problem for us in Oracle 11. Please fix this in dotConnect.
Re: Performance of ordered queries with the Skip() and Take()
We have contacted you by e-mail.bobcat1506 wrote:This is a huge problem for us in Oracle 11. Please fix this in dotConnect.