pinarello
Member
- Joined
- Jun 21, 2019
- Messages
- 218
- Reaction score
- 4
- Points
- 18
- Location
- Germany
- Excel Version(s)
- Office 365
I have tested and optimized the individual versions very intensively in the last few days. Furthermore, I have found with the M-function "Table.SelectRows" a possibility to import for each record only the holidays that fall in the respective period. This is then already a touch of SQL and very elegant, but unfortunately not as fast as my variant, where I read all holidays for each record and then determine and count the relevant ones in 2 further steps.
Furthermore, I could see that the calculation of the net working days, if this is outsourced to a function, takes much longer.
In a test with 11,000 periods with a total of about 400 million net working days, my optimized version determined the result after 5.5 sec. The variants with the functions of Imke and Rick ran more than 3 min. My optimized calculation outsourced to a function ran about 70 sec. and the variant with "Table.SelectRows" needed about 10 sec.
When testing with and without "Table.Buffer", it has been shown that this has a negative effect in the versions with the functions, but a very positive effect in the calculations directly in the query.
Furthermore, I could see that the calculation of the net working days, if this is outsourced to a function, takes much longer.
In a test with 11,000 periods with a total of about 400 million net working days, my optimized version determined the result after 5.5 sec. The variants with the functions of Imke and Rick ran more than 3 min. My optimized calculation outsourced to a function ran about 70 sec. and the variant with "Table.SelectRows" needed about 10 sec.
When testing with and without "Table.Buffer", it has been shown that this has a negative effect in the versions with the functions, but a very positive effect in the calculations directly in the query.
