Performance Pitfall / Warning

[nl059810]

Situation: A script that used to run with Easycom's Toolkit is changed to run with db2* sql AND the Compatibility Wrapper.

After changing the code (db2 data access a few i5 functions like i5_data_area_read remained) the performance was not acceptable.

Db2 and the CW share a connection ([url]forums.zend.com/viewtopic.php?f=113&t=46688&p=115128&hilit=share+connection#p115128[/url])

The result of the sql instruction are a few hundred rows, which should take about 4 seconds to display in the browser.
Within the loop a data area is read (i5_data_area_read). So that is also executed a few hundred times. This was no problem with easycom toolkit, but has a huge performance impact when combining db2 with the CW. In this case I was able to change the code (data area access outside the loop), which brought the roundtrip of the script back from 40(!!!!) Seconds to an acceptable 4 seconds.

Conclusion: avoid calling CW functions in a db2 sql selection loop......

[rangercairns]

Yeah ... i5_data_area_ read uses RTVDTAARA, which is xmlservice through rexx in/out commands (slow). Personally, i would toolkit directly at programming API with PgmCall for speed ... and/or ... MAYBE chat Alan into doing a change Toolkit and CW.

Code: Select all

Retrieve Data Area (QWCRDTAA) API
Required Parameter Group:
1 - Receiver variable -- Output Char(*)
2 - Length of receiver variable -- Input Binary(4)
3 - Qualified data area name -- Input Char(20)
4 - Starting position -- Input Binary(4)
5 - Length of data -- Input Binary(4)
6 - Error code -- I/O Char(*)


[aseiden]

Ranger is correct. We could speed up data area reads by using the QWCRDTAA API. I'm not promising anything yet, but I'll look into it when I investigate the *LDA bug (the toolkit shouldn't qualify *LDA with a library) and the *CURLIB/*LIBL question that someone raised.

Thanks for all the feedback.

Alan

[aseiden]

Performance improvement in data area reads in 1.5.0:
http://174.79.32.155/wiki/uploads/XMLSE ... -1.5.0.zip

--Alan