Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ALL,
I have a report here contains lots of transaction records. We made an accounting report in Summary View as well as in the Detail Transaction Record View. The report's loading speed is quite OK when in Summary View but is EXTREMELY SLOW after switched to Detail Transaction Record View.
The formulas are using AGGR function and as below:
Detail Transaction Record View
Dimension :
=
If(Type = 'Found',
If(aggr( Sum({$<Type ={ 'Found'}, [Category] = {'Storage'}>} REV_AMT), SectionB_KEY) > 0
OR
aggr( Sum({$<Type ={ 'Found'}, [Category] = {'Storage'}>} REV_AMT), SectionB_KEY) < 0,Type))
Expression:
=
sum(If(aggr( Sum({$<Type ={ Found'},[Category] = {'Service'}>} REV_AMT), SectionB_KEY) > 0
OR
aggr( Sum({$<Type ={ Found'},[Category] = {'Service'}>} REV_AMT), SectionB_KEY) < 0,
aggr(Sum({$<Type ={ Found'},[Category] = {'Service'}>} EXP_AMT), SectionB_KEY),0))
The above calculation couldn't be pre-calculated in the scripts because users will pick a period of transaction date from the report. After users' selection, we are going to group all the transaction records based on the key SectionB_KEY and if sum of REV_AMT > 0 or < 0, then sum the EXP_AMT based on SectionB_Key as well.
When run in a server, the speed is OK if a couple of month is selected. But if we pick Jan to Dec, the report will take like more than 1 minute to return the result.
What I am thinking is, the above formula couldn't be modified and so, we choose to upgrade our server as below and we hope it may run faster:
Machine : VM
CPU : 8 cores (Hyper-threading disabled and energy saving mode to HIGH POWER)
RAM : 48G Ram
Windows : 2008 R2
After the test, the overall performance is improved a bit but still, when we process a full 2012 year of data, it takes more than 1 min (the returned number of records is 156,401 records with 50 columns) 
Now I am trying to step backward a bit and think about, would the network speed affect the result ?
Since this VM is located in data center, I am not sure the bandwidth between data center to my office, but thinking since 156,401 with 50 data columns is a huge amount of data and transfer the result set from the server back to my office may take a bit of time.
Anyone could enlighten me on what's going on ?
Any experts may know how the data transfer from QV server to the web browser ?
Many thanks
