Optimising query pagination - Oracle 11g

LuckyLuke82

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.

LuckyLuke82

Well, I tried & tested many things, and I allways come down to slow query, It's Oracle problem 100%. I will also take a look at execution plan. And I didn't know about ODP.NET tracing, thanks for this, I'll take a look ...

Meanwhile I found a solution:

with test as ( select rownum as 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 rownum <= :size_page and rn > (:no_page -1)* :size_page

       order by id;

That is what I send to my stored procedure. It is super fast vs. my original query (12 seconds down to lees than a second), but It lacks ORDER BY inside selected fields ( row_number() over (order by id) rn). But I think users will not complicate about this.

Thanks for all your help.

Gaz in Oz

tOaD, like other UI's "usually" loads the first x rows, and then when you navigate through the data grid, then the next x rows are loaded so timing is not comparable.

You should run the query in sqlplus with "set timing on" to get a more indicative timing for no pagination. Better still "SQL_TRACE=TRUE" then use tkprof to analyze the trace file created and observe the detailed findings within the tkprof'd trace file. You can even trace the application itself with its "pagination".

As for everything else, who knows?... as only you have the code and know how you are trying to time the c# stuff.

LuckyLuke82

tOaD, like other UI's "usually loads the firs x rows, and then when you navigate through the data grid, then the next x rows are loaded so timing is not comparable.

Ok, maybe my question should be stated as : What kind of pagination does Toad use, in order to do something same ? Because my query does same thing, just much slower, which is comparable in Toad - my pagination vs no pagination.

C# code is not so relevant here - as I told, I'm aware of time data is being sent over network. But If query would be faster then C# app would show data faster - DataReader (which I use) is (imho) fastest for loading data from DB.

Is there any FETCH 500 or something simmilar available in 11g ?

Alex Keh-Oracle

I would recommend using an ODP.NET OracleDataReader to retrieve your results if you want to just fetch X many rows at a time as you do with Toad. If you use DataSet or any control that uses DataSet, .NET will fetch everything at once. You can also increase your OracleCommand.FetchSize to better match how much data you want to retrieve per round trip.

LuckyLuke82

Hi Alex Keh,

thanks for reply, but that doesn't solve anything, speed must be increased in query execution at DB point.

I allready have and use OracleDataReader (which I mentioned in previous post), and to "fetch X many rows at a time as you do with Toad"  I use upper query which is why I'm asking If there is anything faster than this. Also setting OracleCommand.FetchSize doesn't speed up things, as I allready limit fetch size in query Itself (did try It though). But good point If someone misses that out.

Finally, regarding fetching data with OracleDataReader vs. OracleDataAdapter : difference between speed of methods DataTable.Load() & DataAdapter.Fill() is minimum, and you never know in which case one will be faster over other one. There has been several topics about that and I tested that out myself, and to my surprise It's true - DataReader is not allways fastest one, even on large set of data. And DataAdapter.Fill() method is basically just a wrapper that uses DataReader inside, so there isn't much difference between them.

So once again, is there anything I can do to make query execution faster ? Toad (imho) uses something faster than querying via ROW_NUMBER for displaying data. Or maybe a better connection, caching etc....

But only thing strange here (and I mentioned that in 1st post allready) that TOAD get's query result faster WITHOUT pagination then It does WITH pagination as I created (and this is where my assumption that my query is slow originates from, because It looks like Oracle takes time to calculate row_number).

But from whatever I've seen people suggest a solution like I have, so that's why I turned to this Oracle specific forum.

Alex Keh-Oracle

You can turn on ODP.NET tracing to see where that 12 seconds of time is being spent. If you see large chunks of time spent on specific calls, then that would provide a clue of areas to focus on tuning. Otherwise, it's just guessing at what could be the issue. You can also see if most of the time is spent waiting on the DB side or specific ODP.NET calls.

I don't use TOAD myself, but I'm pretty sure it doesn't use the .NET FW nor ODP.NET. There's a lot of potential variables at work that makes it very hard to give you specific advice on what to do. If you're using TOAD in a different Oracle Home than ODP.NET, it could be something like SDU size. If you're retrieving LOBs and not using ODP.NET InitialLobFetchSize, then you could be making numerous extra unnecessary round trips.

LuckyLuke82

Well, I tried & tested many things, and I allways come down to slow query, It's Oracle problem 100%. I will also take a look at execution plan. And I didn't know about ODP.NET tracing, thanks for this, I'll take a look ...

Meanwhile I found a solution:

with test as ( select rownum as 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 rownum <= :size_page and rn > (:no_page -1)* :size_page

       order by id;

That is what I send to my stored procedure. It is super fast vs. my original query (12 seconds down to lees than a second), but It lacks ORDER BY inside selected fields ( row_number() over (order by id) rn). But I think users will not complicate about this.

Thanks for all your help.

AndrewSayer

LuckyLuke82 wrote:Well, I tried & tested many things, and I allways come down to slow query, It's Oracle problem 100%. I will also take a look at execution plan. And I didn't know about ODP.NET tracing, thanks for this, I'll take a look ...Meanwhile I found a solution:with test as ( select rownum as 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 rownum <= :size_page and rn > (:no_page -1)* :size_page order by id;That is what I send to my stored procedure. It is super fast vs. my original query (12 seconds down to lees than a second), but It lacks ORDER BY inside selected fields ( row_number() over (order by id) rn). But I think users will not complicate about this.Thanks for all your help.

Feel free to share the execution plan (the general database or SQL spaces may be best to advise here).

Are you executing that exact same SQL from Toad and it is faster? That seems a bit weird.

LuckyLuke82

Feel free to share the execution plan (the general database or SQL spaces may be best to advise here).

Our DBA allready optimised all they could (their words, not mine), so execution plan wouldn't help much. We even have Oracle expert for things like this... From what I've seen all tables that are in query are indexed on multiple column, and query doesn't do full table scans (in most cases, but when query date ranges are older It does sometime). I would say that most probable cause for all issues is that we have many servers on different locations, and query unfortunally uses JOINS via DB links. E.g. for one JOIN I have to query from DB that is 100 km away from server in primary location.

Are you executing that exact same SQL from Toad and it is faster? That seems a bit weird.

Yes, both queries were launched from same Toad client, I use only that for Oracle. It doesn't seem weird to me, I would say that ORDER BY is very time consuming operation, specially in our case (taking into account all problems I mentioned).

AndrewSayer

LuckyLuke82 wrote:Feel free to share the execution plan (the general database or SQL spaces may be best to advise here).Our DBA allready optimised all they could (their words, not mine), so execution plan wouldn't help much. We even have Oracle expert for things like this... From what I've seen all tables that are in query are indexed on multiple column, and query doesn't do full table scans (in most cases, but when query date ranges are older It does sometime). I would say that most probable cause for all issues is that we have many servers on different locations, and query unfortunally uses JOINS via DB links. E.g. for one JOIN I have to query from DB that is 100 km away from server in primary location.Are you executing that exact same SQL from Toad and it is faster? That seems a bit weird.Yes, both queries were launched from same Toad client, I use only that for Oracle. It doesn't seem weird to me, I would say that ORDER BY is very time consuming operation, specially in our case (taking into account all problems I mentioned).

It seems we’ve misunderstood each other, I was under the impression you were running the exact same SQL in Toad and it was faster than when you ran it via odp - ie it is an odp problem. When in fact you are comparing the performance of two different queries.

Execution plans will always be helpful, Ive come across many queries that have apparently been tuned to as fast as they can be and have already been looked at by experts and I’ve still managed to reduce the run time 100 times over. Importantly, the execution plan will tell us what’s going on so an explanation can be made about why one way is seemingly faster than another.

The existence of a DB link and joins would have been good to know about from the start. That said, it should still be possible for pagination to perform fine.