iSeries performance question

tips and tricks
WebSphere Studio
Hello everybody, I'm trying to figure out the best way to count the number of records matching specified criteria in large PF. (The file has over 50 million records currently and with some purging we hope to bring it down to around 20 mil.) Anyway, I created the (SQL) view: create view largePF99 ( field1, field2, field3, count ) as select fldx, fldy, substr( fldz,120,10 ), count( * ) from largePF group by fldx, fldy, substr( fldz,120,10 ) I then declared a cursor for the view in my RPGLE pgm, as I'll need multiple rows within this view to get the count I ultimately want. The problem (as I'm sure you've already worked out) is that the *PGM runs runs for ages. Am I better off reading the base file in good old RPG or is there any other way I can improve this? Thanks in advance.

Answer Wiki

Thanks. We'll let you know when a new response is added.

I see your using create view, however
I’m thinking Create Index would be a better
way to go. Will keep my eye out for what others

Discuss This Question: 9  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • App1dak
    An SQL view will normally perform optimization and save access path information (such as what is the optimal index to use for accessing the data), but a view is not an access path itself. There is no sorted or ordered data stored with a view, only the view's SQL along with any cached access path info. On the other hand an SQL index stores an ordered or sorted subset of table columns that allows for fast searching on that particular subset of table columns. A view is normally used to provide data to some user group in a format other than how it is stored in the db tables, since many times the db is fully normalized and the data is not stored how the users logically think and process it. Try creating an index instead: CREATE INDEX LARGEPF99 ON LARGEPF (fldx, fldy) or CREATE INDEX LARGEPF99 ON LARGEPF (fldx, fldy, fldz) I don't believe including fldz in the index will help since you are doing a substring from the middle of the column. Adding that column may or may not help. You will have to do some timings. If fldz was included, it would allow index only access, but may require more disk reads depending on the cardinality of the data in those columns.
    0 pointsBadges:
  • Dushan
    Many thanks for replying as400programmer and app1dak. I did try creating an index (should have mentioned this earlier). It also took ages to create and unfortunately I can't afford to maintain a permenant index. The PF is part of a base application with its own set of LFs. I'm developing an application outside of the base which uses some of its files. As a result, I can't have permenant indexes/LFs as it would affect performance of the base application. That's why I'll need to create this index everytime this new app is executed and of course it takes far too long. I apologize, I should have mentioned all this earlier. That's why I was wondering if reading these large files using RPG would be more efficient.
    0 pointsBadges:
  • TomLiotta
    This suggestion: CREATE INDEX LARGEPF99 ON LARGEPF (fldx, fldy) ... is probably the best that can be done. And it probably won't matter whether you use SQL or native I/O in RPG. Either way, the substr( fldz,120,10 ) is where the big drain will always be. By creating the INDEX over the two fields, you provide SQL with a chance to use the index to speed up the GROUP BY. However, unless this is a permanent INDEX, there will be no benefit anyway. The time it takes to create the index will be the same time whether it's done by CREATE INDEX or by SQL creating a temporary index to perform the GROUP BY. I don't see that anything can help much except not trying to perform grouping operations on a substring, especially one that is embedded rather than being the left-most characters. By SQL or program, every row must be read and tested every time. A permanent index over the non-substring fields simply avoids spending the indexing time during the sub-select.
    125,585 pointsBadges:
  • Welcome
    You have not mentioned which database you are using. In case you use Oracle8i or higher, then there exists the possibility to build a FUNCTION BASED index, like this: CREATE INDEX ON largePF ( fldx, fldy, substr( fldz,120,10 ) ) / I don't think that the overhead imposed on maintaning such an index could seriously impact the performance, but anyway it could be a matter of decision, considering the case of your specific system. It could happen that sorts performed from a programming language, maybe RPG among them, could be faster than those peformed through SQL, but they require much more coding. It depends of course on the memory allocation that the database uses for sort operations, which is defined at database instance startup.
    10 pointsBadges:
  • AS400programmer
    This could possibly speed things up a little. I have not used it since the system38 days. Remember it is only for sequential/random processing as stated below. You would have experiment a little as too few records or two many records in the buffer could slow you down Records retrieved at once (NBRRCDS) - Help Specifies the number of records read from auxiliary storage as a unit and written to main storage as a unit. The amount of data actually read is equal to the number of records times the physical record length, not the logical record length. Valid values range from 1 through 32767. This parameter is valid for sequential or random processing and is specified only when the data records are physically located in auxiliary storage in the sequence in which they are processed. This parameter overrides the number of records value specified in the program, or in other previously issued OVRDBF commands.
    0 pointsBadges:
  • Puterbob
    You want to do an index scan vs at table scan. When you do a select or native I/O any time you can use an existing index or access path that will save your CPU. Are there any existing logic views (access paths) you can use? The expensive thing is building the access path. Many times a look into the existing access paths may shed some light on how you may accomplish this. You might have an index (view or access path) that used field4, field3, field1 & field2. Use what you have to create a summary then process the summary work file.
    0 pointsBadges:
  • Fanbot
    As previously stated the OVRDBF is part of the solution. OVRDBF using the nbrrcs to be retrieved = 32k/record length stating sequential processing. Then with an RPG program read your file sequentially (no keys)and do your evaluation & comparison in the RPG. OVRDBF FILE(FFFF) NBRRCDS(6000) SEQONLY(*YES)
    0 pointsBadges:
  • Phacops
    Dushan, have you considered an index with MAINTENANCE(*DLY). It is a compile option for a DDS created file; you would need to do a CHGLF for an SQL index. A *DLY index is not maintained until it is actually used, so it should have no affect on the current app, because none of the programs will be using the LF/index. Unless I missed something it is not the space but the affect on performance that you are concerned about. I don't know how many records are added to the large PF between runs of your new application; if there were a lot it might take some time to make the index current, and if there were too many changes the system will do a rebuild index which is what you are trying to avoid. If nothing else has worked yet I think I would try creating a DDS file, substringing fldz in the definition and using fldx, fldy, and new field as the key. At this point I'm not sure what next, but possibly doing all the counting in your program.
    0 pointsBadges:
  • bogeybetsy
    What if you use SQLRPGLE and do a Select count(*) into :@count . . . ? Regards, Allan
    560 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: