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

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

Postby HCRoman » Wed 05 Oct 2011 13:08

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 <= :take
ORDER BY "Filter1".BETRIEBSSTAETTEID ASC


if I change the query in toad to

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

The performance is acceptable < 1 second


Any Help?

Roman
HCRoman
 
Posts: 55
Joined: Wed 12 Aug 2009 05:47

Postby Shalex » Fri 07 Oct 2011 05:52

Thank you for your suggestion. We will investigate the issue and notify you about the results as soon as possible.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Any new information available for this problem???

Postby HCRoman » Thu 09 Feb 2012 07:30

Hi devart team,

Any new information available for this problem??? :) :)

Roman
HCRoman
 
Posts: 55
Joined: Wed 12 Aug 2009 05:47

Postby Pinturiccio » Thu 09 Feb 2012 14:17

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.
Pinturiccio
Devart Team
 
Posts: 1891
Joined: Wed 02 Nov 2011 09:44

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

Postby rolandjoebstl » Mon 27 May 2013 06:55

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.
rolandjoebstl
 
Posts: 3
Joined: Mon 27 May 2013 06:48

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

Postby Shalex » Mon 27 May 2013 16:38

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.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

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

Postby rolandjoebstl » Tue 28 May 2013 08:29

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?
rolandjoebstl
 
Posts: 3
Joined: Mon 27 May 2013 06:48

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

Postby Shalex » Tue 28 May 2013 14:58

rolandjoebstl wrote: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.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

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

Postby bobcat1506 » Fri 12 Sep 2014 12:14

This is a huge problem for us in Oracle 11. Please fix this in dotConnect.
bobcat1506
 
Posts: 1
Joined: Fri 12 Sep 2014 12:11

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

Postby MariiaI » Mon 15 Sep 2014 10:37

bobcat1506 wrote:This is a huge problem for us in Oracle 11. Please fix this in dotConnect.
We have contacted you by e-mail.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to dotConnect for Oracle