I am trying to move a calculation into my load script for performance reasons but am struggling a little bit with the best way to do so.
My problem is I have a master file with >1m rows of data and I want to count the number of occurrences of a product but only where a sum condition is met. I was able to do this with a two step process of:
1. Create a temporary table using a resident load of the master which sums the product with amount (i.e. consolidates multiple rows into 1 for each product in each year)
2. Create another resident load on this temp table to run a count and use where clause >1000
This gives me a new table with the count of products where the amount >1000 for each client, each year.
As the table in question is vast I am concerned about the performance of multiple loads of the data and was seeking a more elegant solution to perhaps combine the above into one step.
I know that this could also be accomplished with set analysis in my main dashboard but this was causing very slow performance I believe due to the complexity and size of the actual data model and not this sample.
Using a double-load is indeed the correct way to handle this. A million rows should not be a problematic number - this should have no issues running, unless you've got unusually large fields. I do similar things with >10m rows in a QVW that runs once an hour.
Given that the information is already in-memory, you typically don't need to worry about adding resident loads unless you're in an every-second-counts scenario.
Using a double-load is indeed the correct way to handle this. A million rows should not be a problematic number - this should have no issues running, unless you've got unusually large fields. I do similar things with >10m rows in a QVW that runs once an hour.
Given that the information is already in-memory, you typically don't need to worry about adding resident loads unless you're in an every-second-counts scenario.
