Error while running blank query on an applet !!

Today while working on one of the issue in UAT env, when I navigated to Assets screen to see list of all assets, ran a blank query, I got the following error :

There were more rows than could be returned. Please refine your query to bring back fewer rows(SBL-DAT-00500).

In the very first sight, it seems query is bringing a large number of records which Siebel can't handle. hmmm... okayy, lets try running a query which will bring less records so I put a query on "Type" field on the applet and ran the query and it worked fine. That means there is some limitation in Siebel while fetching the records and if the number of records goes beyond that limit it gives up. So, one might ask what is that upper limit??

Upper limit for number of records that Siebel can pull from a single query is equal to the value set for "MaxCursorSize" parameter available in your siebel.cfg file. Here below can be the values :

a) 0 : If this parameter set to 0 (zero), that means Siebel can fetch 10,000 records in a single query. This is the recommended value.

b) -1 : it indicates infinite number of records, results in low performance and not recommended by Siebel.

c) >0 : any number greater than 0 will fetch that many records in one query.

So, what happens is if you run a query on UI and you try to scroll the records and crosses the limit what has been set by "MaxCursorSize" parameter, you will get the above mentioned error. Moreover, the parameter is applied for each and every query that Siebel runs with the exception that it might override by "Maximum Cursor Override" property at the business component level.

I checked for the value of "MaxCursorSize" parameter in CFG and it was set to 0. Just to cross verify I also ran a query in the database and confirmed that number of records in S_ASSET records were more than 10,000 records. But, wait a minute, I didn't even scroll once on the Asset Screen and I just tried to navigate on the default view of the screen and still I received this error. This is something else is going on here, isn't it?

One more thing to notice here is that I realized that even I have more than 10,000 Accounts records in the application, but there is no issue when I navigate to All Accounts view. That means something special does exist with Asset business component which is causing this issue. And here below is the reason for it :

"Hierarchy Parent Field" property of business component was set to "Parent Asset Id" and due to this while running a blank query on the Assets UI, resulted in putting a "/*+ ALL_ROWS */" hint in the SQL that Siebel runs in the background. This is the difference I observed when I just removed the "Hierachy Parent Field" and compiled the SRF again and blank query worked fine this time. No issues. But this doesn't mean that this is the solution for the issue, you can't just remove this property to avoid this error because it get reflect in all the applet based on this business component. So here is the solution for this :

Use "Disable Buscomp Hierarchy", a user property available on the applet and set its value to True. Since it is applet based user property, only applies to the applet on which it is used.

Disable Buscomp Hierarchy = True

What it does is : it will just ignore the effect of "Hierarchy Parent Field" on the business component and run the query without "/*+ ALL_ROWS */" hint in the background to bring the records as per the normal process.

I put this user property on the All Assets List Applet and ran a blank query again, everything worked fine.

Hope it helps !!

.