Hi everybody,
I am in the need to build a pivot table from a bunch of different tables (tables non pf-dta since they are generated via ddl and not by dds).
To reach my gol i was thinking to build a UDTFs recycling some already existing rpg routine we use on such tables.
In the example provided by Scott the method to access the data is the legacy mode.
In my scenario i will use Exec Sql.
Do you think that there may be any performance issues? Tables contains millions of rows(records) to be analyzed and to minimize the effort i will re-use some views already in production.
UDTFs based on sqlrpgle programs performance issues
-
- Site Admin
- Posts: 796
- Joined: Sun Jul 04, 2021 5:12 am
Re: UDTFs based on sqlrpgle programs performance issues
Sorry, I'm not familiar with the term "pivot table." I've heard people say they make these in Excel, but never learned what they were, and not really sure how it relates to an RPG/database discussion.LUCAG wrote: ↑Wed Sep 20, 2023 3:59 pm Hi everybody,
I am in the need to build a pivot table from a bunch of different tables (tables non pf-dta since they are generated via ddl and not by dds).
To reach my gol i was thinking to build a UDTFs recycling some already existing rpg routine we use on such tables.
I assume by "legacy mode" you mean that I used native I/O for the database access? That was done to make the example simpler to an existing RPGer to learn.In the example provided by Scott the method to access the data is the legacy mode.
In my scenario i will use Exec Sql.
Do you think that there may be any performance issues? Tables contains millions of rows(records) to be analyzed and to minimize the effort i will re-use some views already in production.
There's certainly nothing wrong with using SQL -- you'll have to change the phrase "NO SQL" to the appropriate phrase (reads sql data or modifies sql data).
Performance problems aren't any different between UDTFs vs any other SQL statement. Native I/O when written the right way can be very efficient, but when written the wrong way can perform poorly. The same with SQL, statements where the proper indices and other things are built will perform well, those that aren't well built/configured won't. Really has nothing to do with whether a UDTF is involved.
Re: UDTFs based on sqlrpgle programs performance issues
Scott, thanks for your reply and suggestion.
I think you were being ironic about pivot tables
I think you were being ironic about pivot tables
-
- Site Admin
- Posts: 796
- Joined: Sun Jul 04, 2021 5:12 am