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

Question

I have a strange issue with a query running in my JDeveloper ADF web application. It is a simple search form issuing a select statement to Oracle 10g database. When the search is submitted, ADF framework is (first) running the query, and (second) running the same query wrapped within "select count(1) from (...query...)" - the goal here is to obtain the total number of rows, and to display the "Next 10 results" navigation controls.

So far, so good. Trouble comes from the outrageous performance I am getting from the second query (the one with "count(1)" in it). To investigate the issue, I copied/pasted/ran the query in SQL Developer and was surprised to see much better response.

When comparing the query execution in ADF and SQL Developer, I took all measures to ensure representative environment for both executions: - freshly restarted database - same for the OC4J This way I can be sure that the difference is not related to caching and/or buffering, in both cases the db and the application server were freshly (re)started.

The traces I took for both sessions illustrate the situation:

Query ran in ADF:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.97       0.97          0          0          0           0
Fetch        1     59.42     152.80      35129    1404149          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3     60.39     153.77      35129    1404149          0           1

Same query in SQL Developer:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      1.02       1.16          0          0          0           0
Fetch        1      1.04       3.28       4638       4567          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      2.07       4.45       4638       4567          0           1

Thanks in advance for any comments or suggestions!

Answer 1

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!

Answer 2

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 3

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 4

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.