how to improve QV performance when select to lock 200,000 values in a field by a variable

[pjin_precima]

Hi,

I recently got a request to create a pre-defined fucntion in a big data application (more than 100,000,000 transactional data) for a client, which:

• allows user to limit the data to a list of customers (say 200,000 distinct customers) by
  • making selections from some list boxes (e.g. week, store location, etc)
  • and enter constraints into  to inputboxes (e.g. each customer buys at least 5 items,  each customer must have at least 3 transactions)
• and then lock this customer list, and make other selections to see the behavior of the same list of customer.

What i did is to create a variable (vCustomerID) which concatenates the distinct list of customer ids (CustomerID_Field) by limiting the set based on the constraints the user inputs, and then created a button with the list of actions:

• action1. use the 'select in field' action to select values in (CustomerID_Field) by searching strings in the variable (vCustomerID)
• action2. lock the (CustomerID_Field)

The above approach works fine when we want to lock less than 50,000 distinct customer ids. However, when the user want to lock the list of 200,000, it takes more than 25 minutes or even more, and the application sometimes gets unresponsive.

I am not sure if anyone experience similar issues when dealing with an application with big data. It will be really helpful if you can provide any recommandations based on the experience.

Thanks a lot,

Peihao