Why is there such a big difference in the execution time of a query ran by ADF and in SQL Developer

Answer 1

The query with count is slower because it has to read all the data (to count it).

When you run the other query, you are only fetching a first page of data, so the execution (reading from the cursor) can stop after you have your first ten results.

Try loading to 100th page with your first query, it will likely be much slower than the first page.

If selecting a count online is too expensive, a common trick is to select one item more than you need (11 in your case) to determine if there is more data. You cannot show a page count, but at least a "next page" button.

Update: Are you saying the count query is only slow when run through ADF, but fast through SQL Developer?

Answer 2

If it is the same query, i can think of:

• Different settings in ADF vs SQL Developer (have you tried with SQL*Plus?)
• Binding variables of incorrect type in the slow case

But without the execution plans or the SQL, it is hard to say

Answer 3

Over the years I've found that "SELECT COUNT..." is often a source of unexpected slowdowns.

If I understand the results posted above, the query takes 153 seconds from JDeveloper, but only about 4.5 seconds from SQL Developer, and you're going to use this query to determine if the "Next 10 Results" control should be displayed.

I don't know that it matters if the runtime is 4.5 seconds or 153 seconds - even the best case seems rather slow for initializing a page. Assume for a moment that you can get the query to respond in 4.5 seconds when submitted from the page - that's still a long time to make a user sit and wait when they're only a mouse-click away from going off to do Something Else. In that same 4.5 seconds the app might be able to fetch enough data to load the page a few times.

I think @Thilo's idea of fetching one more record than is needed to fill the page to determine if there is more data available is a good one. Perhaps this could be adapted to your situation?

Share and enjoy.

Answer 4

Ok, I finally found the explanation of this ghastly behaviour. To make the long story short, the answer is in the definition (Tuning parameters) of my ViewObject in JDeveloper. What I was missing were these two important parameters:

• FetchMode="FETCH_AS_NEEDED"
• FetchSize="10"

Without them, the following happens - ADF runs the main query, binds the variables and fetches the results. Then, in an attempt to make an estimate of the rowcount, it launches the same query enclosed in "select count(1) from (my_query)", but ...(drum roll)... WITHOUT BINDING THE VARIABLES!!! It really beats me what is the use of estimating the rowcount without taking into account the actual values of the bind variables!

Anyway, it's all in the definition of the ViewObject: the following settings needed to be set, in order to get the expected behaviour:

• All Rows in Batches of: 10
• (checked) As Needed
• (unchecked) Fill Last Page of Rows when Paging through Rowset

The execution plan could not help me (it was identical for both ADF and SQL Developer), the difference was only visible in a trace file taken with binds.

So, now my problem is solved - thanks to all for the tips that finally led me to the resolution!