You are viewing limited content. For full access, please sign in.

Question

Question

Recommended ways to reduce performance hit with Forms lookup to large quantity of data?

Forms Version 10
Updated November 28, 2016
asked on November 20, 2016

Hi guys,

(Using forms 10.1 Update 3)  We need to use a Forms dropdown field in a table column to return a product item from a lookup that, even when using a Stored Procedure in SQL to reduce the number of rows being returned, still amounts to approx 100,000 rows.

The originating table only contains 4 columns: ProductCode, ProductName, LatestCost, and CompanyCode.  I have set the CompanyCode as a parameter for the SP but there's nothing else to filter it by as the users then need to select the ProductName dropdown for the item.

There's only a couple of seconds delay with the first two or three lines in the table in the Form (which isn't bad considering the amount of data being referenced), but it gets progressively slower the more rows that are added.  It hangs until the browser times out if a selection is subsequently changed.  

I've got multiple criteria for populating the ProductCode and LatestCost in the table in the Form once the ProductName has been returned, so the AutoFill button is presented and that works well, it's just the initial population of the ProductName dropdown that I would like to improve on if there's a better approach.  Or is this exceeding reasonable expectations of the system?

Thanks!

Mike

1 0

Answer

SELECTED ANSWER
replied on November 24, 2016

So there is a dropdown field in table on the form, and the dropdown field has around 100,000 options populated by lookup, right? I tested with lookup returning 100,000 rows on Chrome, and when I added 10 rows on table, it took 5 seconds to generate the new row, but Chrome would already take up to 2GB memory. The issue is the dropdown field contains too much data for browser to render.

I don't think this is good design for a form. If you expect user to select 1 option out of 100,000 options, the user must has already know the option exactly. So why not use a single line field directly? Or do another round of filter on the data?

 

1 0
replied on November 27, 2016

Hi Rui,

Thanks for that.  I'm not sure how else to filter it based on the limited number of columns, but I'll try the single line field and see how that goes.

Thanks!

Mike

0 0
replied on November 28, 2016

Hi Rui,

The single line field is the best option and works much better than the drop-down.  We will still encourage the customer to work at ways in which the data can be better filtered, but this is a great improvement.  Thanks very much.

Mike

0 0

Replies

replied on November 21, 2016 • Show version history

Hi Mike, we have experienced the exact same issue.

We have a form that has a fill rule to populate a field "Client Name" based on the "Account Manager" which is the users name. There are approximately 2500 available selections in the "Client Name" field per "Account Manager". Once this is selected 12 dependent fields are filled in, some of these are in a table with additional rows. We spent weeks playing with the fill rules to try and stop the browser from hanging. To us it looks like forms simply cant handle the volume of data contained within the lookup. When we strip the DB back to only contain a few entries the lookup performs well. We have even tried to recreate our DB's with different structures to improve the performance.

1 0
replied on November 20, 2016

You fill the a column in the table as new rows using lookup rule which have 10000+ rows in return? Can you change the lookup rule to not fill "as new rows" and just let end user add new rows when they need it?

0 0
replied on November 20, 2016

Sorry for being unclear, it doesn't fill "as new rows", the rows are added manually by the end user.

0 0
replied on November 21, 2016

Hi Mike, so to clarify the setup:

  1. The stored procedure in the lookup rule will take the CompanyCode column field as input.
  2. The rule populates the ProductName column field in the same row of the table.
  3. This field (text or dropdown) will have 10000+ options, with submitters able to add additional rows.
  4. There aren't actually 10000+ rows in the table.

Have I understood this correctly? If so, about how many rows in the table are required for the browser to hang/crash, and does this vary by browser?

Thanks

0 0
replied on November 21, 2016

Hi James,

Again, my apologies for having made things so unclear.  Here's the process, hopefully the screenshots below it helps clarify things:

  1. The stored procedure ("SP") in the lookup rule requires the CompanyCode as a parameter, which is provided in the URL content to automatically populate the form from a link.
  2. On executing the SP, the ProductName column is filtered by CompanyCode and returns approx 100,000 results - in the SP lookup results "behind" the ProductName" dropdown field only at this stage.
  3. The table in Forms isn't populated automatically, so there isn't anything in the table until the user selects the "Product Name" dropdown field in the first cell of the table.  This is what is populated from the SP lookup.
  4. As the user selects the initial Product Name entry it only takes approx 2 seconds for the dropdown to populate for the first 2-3 rows, but then the performance starts to degrade for subsequent rows, even if they haven't used "Auto fill" to populate the remaining fields for any of the rows.
  5. If the user selects an incorrect Product Name entry and tries to delete the row or change the selection, the browser hangs - this is tested in Chrome, Firefox, and IE 11.

 

Here's the lookup rule:

 

And here's the table in the form:

Please let me know if there's any additional information you require.

Thanks!

Mike

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...