StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhy is there such a big difference in the execution time of a query ran by ADF and in SQL Developer
text
Body
copied!<p>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 "<code>select count(1) from (...query...)</code>" - the goal here is to obtain the total number of rows, and to display the "Next 10 results" navigation controls.</p> <p>So far, so good. Trouble comes from the outrageous performance I am getting from the second query (the one with "<code>count(1)</code>" in it). To investigate the issue, I copied/pasted/ran the query in SQL Developer and was surprised to see much better response.</p> <p>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.</p> <p>The traces I took for both sessions illustrate the situation:</p> <p><strong><em>Query ran in ADF:</em></strong></p> <pre><code>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 </code></pre> <p><strong><em>Same query in SQL Developer:</em></strong></p> <pre><code>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 </code></pre> <p>Thanks in advance for any comments or suggestions!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload