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.