|
Based on my experience using SQL on the AS/400, I have used it since it first became available (slow as mud back then), that since V4 for the OS/400 SQL performance has been so close to the tradition access methods and with each release after that the difference has become so small that it isn't an issue of speed anymore. The real issues should be what makes the best sense for what you are trying to accomplish with the application. I have used the AS/400 for data warehousing and I used a mixture of SQL and Native access. The performance was very important in moving and massaging the data. Here is just a summary of what I found. 1. Moving chucks of data from 1 file to another and excluding records during the read process using RPG with big fields and pointers beat CPYF and SQL. (It beat the CPYF very bad which surprised me. Even though it beat SQL it wasn't over huge, but the code looked cleaner and I didn't have to have any indexes to worry about creating for the load process so I went with RPG.) 2. When it came to massaging the data from several files to many other relational files, SQL was faster (and I mean way faster), less code, by looking at the SQL statement it easier to see what it was doing vs reading RPG that needed to loop and chain to how ever many files and having to code to skip records it didn't need. 3. Slow SQL performance that people fuss about on the AS/400 isn't from SQL being slow, it is from lack of knowledge of how SQL works and AS/400 programmers try to code SQL like RPG, COBOL, etc... Which frustrates them and SQL on the AS/400 gets a bad rap. Anytime someone has brought a slow running SQL to me, the performance was corrected with writing the SQL correctly or adding a correct index. 4. Poor coding methods with traditional RPG and COBOL using Logical Files with select/omits cause poor performance issues with SQL. The reason this causing poor SQL performance issues is that when an SQL statement of something like Select F1, F2, F3 from File1 Where F1 = 123456 and F8 > 0 Order by F1, F2, F3 - The query manager starts looking at the lf/indexes for the file/table. If the files/tables index aren't created right and it has too many lf/indexes it can't use because the lf have select/omits this can cause the query manager to quit looking a do a full table scan or build a temporary index. Some of these problems can be avoid by building all the lf/indexes that don't have select/omits first and creating lf/indexes that can force sharing of indexes between other indexes. 5. Poor database design causes other performance example. File1 is linked to File2 with four fields. In RPG, reading File1 and chaining to File2 performs well and we are all use to that model. There are three things wrong with this database design 1 is waste of disk space do duplicate data in File2, File1 should have one field that contains unique value that is the key to File2, the lf/indexes wastes disk space because of the key size where the 1 unique filed key value makes the index size smaller. We all database like this in some form or fashion. 6. Don't know why IBM did this but creating the indexes with SQL are better than the DDS. There are articles put out by IBM on why, but still seems like the DDS would yield the same result. But I have seen performance go up by doing this. 7. I see where several people have said that SQL isn't fast a chain to a file. I don't totally agree with that based on this fact. If you are using every field in a file that you do a chain on then I would say the chain would probably be faster. However, if you are only using 15 - 25 fields out of a 200 field table, then I would lean to SQL being faster because it doesn't have to return data that isn't being used. That is document pretty well in the SQL manuals and this relates back to bullet 1 and 2. It really depends on if the application needs all the I/O performance you can provide it that would lean me to pick one over the other. 8. Last but not least. If you have a multi-processor box, buy SMP. This feature alone can do miracles for SQL performance and if you need to rebuild LF after a reorg on the table it helps there as well. SMP allows the SQL statement to be split up among the processors. Example of performance - I had a table that once loaded I needed to build 10 lf/indexes on it. Without the SMP turn on it took about 2 hours to build. With SMP turned on, it took less than 30 minutes. That is a huge difference and those are numbers from back on V4. Kent M. with IBM has published a recent article new benchmark numbers with V5R2 located on the DB2 website. FYI doesn't do anything for the tradition access programs. -----Original Message----- From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Pluta Sent: Friday, August 22, 2003 3:20 PM To: 'RPG programming on the AS400 / iSeries' Subject: RE: SQL vs native access > From: Walden H. Leverich III > > I think that was true prior to 5.2. In <=5.1 SQL actually accessed data > through the same SLIC primatives as single record operations. However, in > 5.2 SQL has a new query engine (below the MI) and its own set of SLIC > primatives so I'm tempted to say that common sense may see a change. According to Jon Paris' post, this doesn't seem to be the case, Walden. Do you have any type of formal information on this, or are you just guessing? Joe _______________________________________________ This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/rpg400-l or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.