Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Optimising query pagination - Oracle 11g

LuckyLuke82Jul 24 2019 — edited Jul 29 2019

Hi,

I have a stored procedure that has a dynamic SQL, with 2 parameters for SELECT part and WHERE part, like this:

CREATE OR REPLACE PROCEDURE MYSCHEMA.GET_DATA (fields IN varchar2, condition IN varchar2, result OUT SYS_REFCURSOR)

IS

BEGIN

    OPEN result FOR

                    fields || ' from (...here is my query, which contains all possible fields...)) where ' || condition;

exception when others then

    OPEN result FOR

            'SELECT ' || '''' || SQLERRM || '''' || ' as some_error from dual';

END;

/

Because this query can have many rows and calculation till results can take some time I decided to do a pagination of query from C# app instead of loading all data into memory. My pagination works but not so quick as expected.

To see actual speed of paginated query I run some tests in Toad client. Results were:

1.) When I run query without any pagination in Toad, I get results instantly in around 50-100 msecs. Super quick;

2.) If I run paginated query from Toad I get results in around 6 seconds;

3.) If I run paginated query from  C# app I get results in around 12 seconds - which is 120% slower than from Toad without any pagination. Too slow for pagination;

I'm aware of time data spends going over network via ODP.NET, but less than a second from Toad vs. 12 second is quite big for me.

That means everytime user presses button for next page, he has to wait 12 seconds.

As I noticed Toad displays 500 records at a time in Datagrid, so I did same when running pagination query. Here is a sample of how I do pagination (black text is what I send to stored procedure)

with test as ( select row_number() over (order by id) rn, id, col_1, col_2, col_3 ||   ' from (...here is my query, which contains all fields...)) where ' || date_col between '01.01.2019' and '24.07.2019')

       select id, col_1, col_2, col_3 

       from test where rn between  0 and 500

       order by id;

So, based on how fast Toad retrieves data, Is It possible to optimize my pagination of query to be something near that in terms of speed ?

Thanks for any advices.

P.S.: As title says, I'm using Oracle 11g.

This post has been answered by LuckyLuke82 on Jul 26 2019
Jump to Answer

Comments

Post Details

Added on Jul 24 2019
9 comments
2,649 views