AnsweredAssumed Answered

Bad performance in NSQL query in sorted Portlet

Question asked by dtietze on Nov 29, 2010
Latest reply on Dec 10, 2010 by Chris_Hackett
Hi.

[The following is done on Oracle 10g]

I'm having performance headaches with an NSQL query I'm using in a Grid Portlet.

I have a - rather complex - query. In pure SQL mode, this executes in roughly ten seconds. Used in a Grid Portlet, it is appallingly slow.
I've activated SQL Trace and checked the SQL trace performance. Here's what I found.
I've extracted the entire query run for the Grid Portlet from the SQL Trace.

Assume my complex query is "CPLX_QUERY".
Using an SQL client, this query executes in 14 seconds. Which would be OK.

But now, Clarity does its Grid Portlet magic and wraps this query in several layers, as follows:

select * from (select row_number() over ( order by prj_name asc) row_num, count(*) over () num_rows, q.* from (
CPLX_QUERY )wrap ) q) q order by q.row_num


(the CPLX_QUERY part is again, inline, my complex query, converted from NSQL - removed for brevity).

This auto-generated query now runs in 123 seconds (!!)

Simply removing the "order by q.row_num" from the generated query, so that it reads

select * from (select row_number() over ( order by prj_name asc) row_num, count(*) over () num_rows, q.* from (
CPLX_QUERY )wrap ) q) q


again brings the performance down to 14 seconds.

So, the sorting magic, which should actually only sort by a numeric value, introduced by Clarity in order to support the Portlet sort makes the execution time skyrocket tenfold.

Has anyone every experienced something similar and can someone provide a solution or workaround for this? Can I somehow tell the Grid Portlet to NOT sort, or to sort by something else?

Any help greatly appreciated.


Daniel

Outcomes