Perform Find/Filter "Parent" based on "Child" quantities

[ddmol]

To begin, I have about 100,000 records (and growing) in a "Parent" table. Each Parent will have at least one "Child" (usually about 2 or 3, but can be 5 or 6). Each Child has a "Quantity" calculation field {sum( in - out)} which is related to the "Transactions" table.

 

I'm trying to filter a table (in list view) of the "Product" aka. parent to show only products that have any "Inventory" items aka. child that have quantity > 0.

 

I'm able to generate the results, my problem is the speed at which it calculates

 

For instance, "Prod A" might have three children:
A_1, with qty 1

A_2, with qty 0

A_3, with qty 0

 

"Prod B":

B_1, with qty 0

B_2, with qty 0

 

"Prod C":

C_1, with qty 0

C_2, with qty 0

C_3, with qty 5

C_4, with qty 7

 

Therefore, I created a calculation field called "invQty"in the Product table as such: sum (inv::quantity). I have a button on my product layout attached to a script that looks for a value > 0 in the invQty field.

 

The result of the above example will display "Prod A" and "Prod C" only, which is great but it takes about 45 seconds. (Script is performed on client, file is hosted on FMS)

 

Would this be a time to use executeSQL, if so, how would the calculation be written (I have yet to use this function) from what i've read it isn't much faster? Otherwise, is there a faster way to do this? You guys always come up with some clever stuff, hope you can help me.

 

Additional thoughts:

I've been working with PSoS lately, and it's been amazing, but not sure if this can work here to get a found set to display on client.

I've also been looking into the "MasterDetail" setup by Todd Geist. Pretty awesome stuff, just not sure if it's the right solution for me yet.

 

Thanks in advance to everyone.. I also attached an image of the relationship for this example. I put a lot of information for this simple task, just wanted to be clear as possible.

 

 

 

[Fitch]

Searching on a related unstored calculation is predictably slow. ExecuteSQL is not inherently any faster. PSoS would almost certainly be faster. What you'd do after the Find is: gather the IDs of the found set, e.g. using the Summary field "List of" option; and exit the script with that as the Result. Back on the client, use Get(ScriptResult); set it into a global field; and go to related records -- you'll need a relationship from that global field to the Product ID.

 

Another approach could be to create a qty calc in the Transaction table (which could be a stored calc since it wouldn't reference related data); do your Find in that table; and go to related records from there.

 

You might also consider scripting the inventory in/out process so that it stores the current quantity at the Inventory or Product level. Finds are super fast on non-calculated number fields.

[ddmol]

I'm using the PSoS recommendation. It gives me the result in about 11 seconds, but at least it's consistent and not reliant on the clients computer/internet speed. More importantly, your clear explanation helped me wrap my head around this function to use in other situations.

 

Thank you Tom!

[Wim Decorte]

Keep in mind that your test results may not hold up in real-life depending on the # of users that will execute this at the same time and the specs of the server.

 

Using PSoS forces processing onto the server that used to be distributed on the clients.  While it may be faster in testing mode with a development server that is not busy and single instances of PSoS being triggered, things may look very different when multiple PSoS sessions are spawned on a server that is otherwise already busy (backups, schedules, regular client-server activity,...)

[comment]

I have about 100,000 records (and growing) i

 

You should consider what grows faster: your data size or your hardware speed. If the former, start thinking about denormalization.

[ddmol]

Agreed. Thank you guys for the follow up input. The number of users and data should be increasing faster for sure. In this case, Tom's 3rd recommendation is probably best (scripting current quantity value and store on product level). I will make this script step a PSoS (uncheck, wait on server), so it doesn't slow down the user while the in/out process takes place.