Expand is SLOOOOOOOOOOW
Posted: Mon Sep 13, 2021 10:05 am
Recently stuck a blog post up over on the IBM Community https://community.ibm.com/community/use ... ing-expand but long story short is that if you are using Expand to concatenate your variables up into a big string (for exporting to flat file or doing an ODBCInsert etc.) it is slower than doing the concatenation (and conversions from number to string) manually.
Or at least those were my findings.
I was working with a very large dataset and calling the Expand function many times, the time saving I got by swapping it out was very significant.
If you are only running a small number of records or doing something on the prolog/epilog it is unlikely you will notice a big difference but for large data sets, you might manage to shave some minutes off longer running processes by getting rid of the Expand function.
It's a super simple example but you can see the differences in timing when running something like below (unhash 1 at a time and see the differences):
For me doing the export for 3 numeric variables took 105 seconds with expand vs 20 seconds without it.
Worth noting that in my actual process I was really using it for I had numerics that were not integers and I was just exporting to flat file to throw it all in a DB for further processing. I didn't need to worry about number formatting so NumberToString and/or Expand was sufficient.
I believe the biggest hit is the method used to convert numbers to strings in the Expand function is less efficient than that of NumberToString.
If you are only putting string variables in anyway there is a slight speed hit but not too significant... this make sense when you are essentially throwing an extra function into the mix that is arguably unnecessary.
It seems sort of obvious in hindsight that it would be slower but it's something I hadn't seen mentioned before or thought about until I needed to.
So thought I would share in case it could save someone else a little bit of a headache in the future.
Or at least those were my findings.
I was working with a very large dataset and calling the Expand function many times, the time saving I got by swapping it out was very significant.
If you are only running a small number of records or doing something on the prolog/epilog it is unlikely you will notice a big difference but for large data sets, you might manage to shave some minutes off longer running processes by getting rid of the Expand function.
It's a super simple example but you can see the differences in timing when running something like below (unhash 1 at a time and see the differences):
Code: Select all
cOutputFile = 'C:\test.txt';
sString = 'String';
nNumeric = 1;
iCount = 1;
iMax = 10000000;
While ( iCount <= iMax );
#TextOutput ( cOutputFile, Expand ( '%sString%, %nNumeric%' ) );
#TextOutput ( cOutputFile, sString, NumberToString ( nNumeric ) );
#TextOutput ( cOutputFile, Expand ( '%sString%' ) );
#TextOutput ( cOutputFile, sString );
#TextOutput ( cOutputFile, Expand ( '%nNumeric%' ) );
#TextOutput ( cOutputFile, NumberToString ( nNumeric ) );
#TextOutput ( cOutputFile, Expand ( '%nNumeric%, %nNumeric%, %nNumeric%' ) );
#TextOutput ( cOutputFile, NumberToString ( nNumeric ), NumberToString ( nNumeric ), NumberToString ( nNumeric ) );
iCount = iCount + 1;
End;
Worth noting that in my actual process I was really using it for I had numerics that were not integers and I was just exporting to flat file to throw it all in a DB for further processing. I didn't need to worry about number formatting so NumberToString and/or Expand was sufficient.
I believe the biggest hit is the method used to convert numbers to strings in the Expand function is less efficient than that of NumberToString.
If you are only putting string variables in anyway there is a slight speed hit but not too significant... this make sense when you are essentially throwing an extra function into the mix that is arguably unnecessary.
It seems sort of obvious in hindsight that it would be slower but it's something I hadn't seen mentioned before or thought about until I needed to.
So thought I would share in case it could save someone else a little bit of a headache in the future.