Substitute Function Consumes More Time

tamhas said:

Have you considered other mechanisms altogether? Like populating a temp-table and then exporting it as JSON?

Click to expand...

We just completed phase I of a migration project and started with phase II which will run for another 3 to 4 weeks. Once we are done with the phase II we are planning for this kind of a revamp because we have so many customer complaints on the slowness. During the analysis I was surprised to see concatenation using SUBSTITUTE consuming more time and was thinking of providing a temporary fix by replacing the function with '+' operator.

tamhas said:

So, are you now saying that there is DB access in this loop? And suggesting that the concatenation is slower than the DB access?

Click to expand...

I am 100% sure that the db reads are not consuming time. I checked the results of profiler and ran once again with etime to cross verify the results.

To keep it simple I wrote the code snippet with DO loop and ran it on a procedure editor without db connection which took 3 seconds and that proves SUBSTITUTE function is the culprit in this case.

Can you expand your snippet so that it can actually be run? For example at least include the variable definitions with the data types you are using.

I recommend trying (and sharing) your snippet at ABL Dojo

If the variable you are repeatedly growing is a longchar - beware - see Longchar sucks. Introducing BigCharacter