Query performance issue : Dedicated Client Vs Thin Client

Very strange behaviour I observed today while working on a performance issue where whenever user do a query in Purchase Order Pick applet, query was taking more than 1 minute to execute in thin client but the same query behaves perfectly fine in dedicated client.

So while working on any performance issue, you always want to see the performance on dedicated client as well, so I did the same.

1. Took the same SRF running in thin client, and connect it to the Dedicated client.
2. Followed the same steps: open the pick applet and queried the Purchase Order#

To my surprise, on dedicated client the PO# query took only 6 seconds which is quite acceptable performance. So, this is the issue I faced first time when the query is running slow in thin client but not in dedicated. Now, lets try to find out what exactly is going on behind the scene.

I referred the spool file and took out the culprit query. Execution time mentioned in the query was same around 6 seconds. I ran the "Alter Sessions" commands as mentioned in my earlier post and ran the query. It again took only 6 seconds.

***** SQL Statement Execute Time: 6.014 seconds *****

hmmm..... now it looks like everything seems working fine from configuration perspective but why is there a difference in the query execution when run in Dedicated client Vs Thin client???

I was looking into the issue more and found that when I did a query in PO# field with value "1234" and it returned multiple records with PO# like: 1234, 12345, 1234a etc. So, it is quite clear that "AutomaticTrailingWildCards" parameter in the CFG was not set to False. But, anyways the query should perform similar in both the dedicated and thin client, no matter this parameter is set to False or not. Though, this thought is quite logical but even then I wanted to give a try by disabling it. So, I thought of disabling it only on the "PO#" field on the pick applet, so that whenever user do a query on PO# field, system should not put the "like" keyword in the SQL generated at the backend.

I put the following User Property on the business component:

Name : Disable Automatic Trailing Wildcard Field List
Value : Purchase Order Number

I compiled the SRF, tested it in dedicated client, it worked fine. Now it is the time to test it in thin client as well. At this moment I was not sure how it would behave. I put the same SRF on thin client and performed the same steps for querying the Purchase Order#, I got the result in around 6 seconds. A big smile and relief too, but again I don't have the correct explanation yet for this problem why is it happening, but this user property came as the life saver :)

If you ever faced this kind of performance issue and know the exact reason please comment.

Constraining Business Component to accept unique value for some fields

One of the interesting requirements I heard recently to put a check on the business component to accept only those records which has the unique value in specified fields. Actual requirement goes this way:

“Opportunity Product” is the child business component of “Opportunity” Business Component. System should not allow adding the Opportunity Product record with the same “Product” associated with it. It should prompt a message; you cannot add same product to more than 1 line item. Something like below screenshot:

Immediate reaction could be put a script on PreWrite event of “Opportunity Product” business component (Non-Active Instance) to check if there is any other record with the same Product value and RowId not = Current record rowid (this is important because you want to check for the other record, if exists). And then prompt the message RaiseErrorText.

Okayy fine, the above solution would work for sure but back in mind people always think for some script less solution. Frankly speaking, I won’t prefer calling a workflow process here for just sake of providing the script less solution like invoke a WF process on PreWrite event to check if there any record exists with the same Product and then thru the error.

A friend of mine, Varun Gulati suggested a nice way to achieve this requirement. So, the function which will do this wonder is “BCHasRows()”. Here below is the solution:

1. Create a calculated field on “Opportunity Product” BC:

a. Name = Duplicate Product Calc
b. Calculated = True
c. Calculated Value =
BCHasRows("Opportunity", "Opportunity Product", "[Product] = '"+[Product]+"'"+"AND [RowId] <> '"+[RowId]+"'" + "AND [Oppty Id] = '" + [Oppty Id] + "'", "All")

2. Put the validation on “Product Id” field :

a. Validation =
[Duplicate Product Calc] = 'N'
b. Validation Message – String Override =
You cannot add same Product to more than 1 line item.

And that’s it. This solution looks very easy and clean isn’t it? Not a single line of code is required.

Basically, “BCHasRows()” take the Business Object, Business Component, Search Spec and Visibility as the input. Evaluates the Search spec on the BC and returns Y or N. Please note that the validation message has been put on “Product Id” field, and not on “Product” field, because I observed that “Validation” is getting fired on the base fields only (not on Join fields).
Give it a try!!