Speed in SQLRPG, or lack thereof

[K.Forsythe]

For better performance, I'd suggest a couple things: 1) Review the syntax of your SQL statement. There might be a more efficient way to write it. 2) Create indexes, either the standard binary radix tree indexes or encoded vector indexes to improve performance. Kevin

[Guest.Visitor]

Speed in SQLRPG, or lack thereof

It is best for SQL to find/use an existing access path on "big" files. Put your program in debug and the query optimizer will write lots of performance data, hints to the job log. Is priceless. The book might be "SQL at Work" by Howard F Arner Jr. Is a bit outdated now but the chapter 6 on performance alone is worth the price of the book, for me anyway. www.sqlthing.com Chris

[lujate]

Speed in SQLRPG, or lack thereof

Avoid dynamic SQL whenever possible.

[Guest.Visitor]

Speed in SQLRPG, or lack thereof

lujate: Good advice. Thankfully the files I'm using will be small. Thanks for the responses. I'll check out the book.

[2ndtim]

Speed in SQLRPG, or lack thereof

Karen, another tool would be to turn on debug before your SQL statements, either in interactive or in an HLL language, run the program or HLL, then turn debug off. Take a look at your job log, and the query optimizer will give you some suggestions. It might say that it needs a logical file (view) and it will also suggest the fields (columns) you need to build it on.

[Guest.Visitor]

Speed in SQLRPG, or lack thereof

About performance: To improve your performance you can create duplicate object from you data file into qtemp and pre select record into new file from your physical file using some broad criteria ( division; company, etc. ) It should cut number of records to process for your SQL statement and improve your program performance.

[DaleB63]

Speed in SQLRPG, or lack thereof

Several good comments so far. There are books and classes on query optimization. I believe there's a section in the Information Center. The basic guideline is that the ORDER BY in your query is only of minor importance. The query engine looks for indices that have high order keys matching the columns in your WHERE tets. If you want to read a table by account number, you need to have an index on the account number column, same as with traditional CHAIN. When debug is active, the query engine (doesn't matter if it's SQL, RUNQRY, or other query interfaces) puts a lot of useful information into your joblog. Specifically, it will tell you what logical files and SQL indexes it looked at, which one it selected, which it didn't select, and the reasons why. Note that there are only so many reason codes in the first message, so sometimes you need to look at a 2nd message to get the meanings of the codes. Sometimes, it won't find any index that is useful to the query, and the job log will tell you that it is either building an index (which is slow, unless the file is small), or is doing a table scan (I forget the exact wording of how this appears in the joblog; probably something about sequential or arrival sequence access). Note that scanning the entire table is not necessarily a bad thing. If a significant percentage of rows would be selected (surprisingly, it's a fairly small number, something like 15%), reading the entire table may have better performance overall, if an existing index couldn't be used. The query engine can double buffer the input, take advantage of record blocking, and so forth, just like traditional I/O.

[dford@pmi.delta.org]

Speed in SQLRPG, or lack thereof

Before placing (almost) any SQL in a program, always run it through iSeries Navigator's "Visual Explain" first. Once you know how to use it and read its results, it will alert you to most performance bottlenecks (and give you suggestions on how to fix it). In SQL performance optimization, there are lots of tips and techniques, but the main one is ... create good indexes. Someone mentioned "SQL at Work", but don't forget Paul Conte's "SQL/400" book. It's also a bit outdated, but it's a good start. HTH, DaveSlash

[Guest.Visitor]

Speed in SQLRPG, or lack thereof

Hi, I'd suggest first to read the literature already published for free: 1.Modernizing IBM eServer iSeries Application Data Access - A Roadmap Cornerstone http://www.redbooks.ibm.com/abstract...6393.html?Open 2. SQL Performance Diagnosis on IBM DB2 Universal Database for iSeries (based on release V5R3) http://www.redbooks.ibm.com/abstract...6654.html?Open 3. OnDemand SQL Performance Analysis Simplified on DB2 for i5/OS in V5R4 http://www.redbooks.ibm.com/abstract...7326.html?Open 4. Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS http://www.redbooks.ibm.com/abstract...6598.html?Open 5. DB2 Universal Database for iSeries Database Performance and Query Optimization http://publib.boulder.ibm.com/infoce...zajq/rzajq.pdf BTW the books you mentioned before written by Howard Arner and Paul Conte are written sometimes ago and may not reflect the newest database technologies on the system i, i.e. the tips and recommendations are based on the classic query engine and not on the newer SQL query engine. Optimization for either query engine may be different. BTW creating a DDS described logical file temporaryly and using it in an SQL statement is almost the worst thing that can be done. 1. Creating an access path (either keyed DDS described logical file or SQL index) requires several table scans (reading all records in the physical file), for determining the distinct keys and building the bit map with the information which record belongs to which key. 2. Specifying a DDS described logical file in a SQL statement will not force the optimizer to uses the access path stored in this logical file. On the contrary the optimizer looks at the description of the logical file, takes the selected fields, the join information and the select/omit clauses and rewrites the SQL statement based on the physical file with the selected information. After the optimizer checks ALL access paths (in either logical files or SQL index) to find the optimal one. Only the classic query engine can work with DDS described logical files, that means all SQL statements with specified DDS described logical files must be rerouted from the SQE to the CQE. This rerouting costs between 10 and 15% performance and the advantages of the SQE cannot be used. Birgitta

[Guest.Visitor]

Speed in SQLRPG, or lack thereof

I've just started using SQL in RPG programs and so far I'm thinking it is a batch program language. If I'm using it like a chain file, it looks to me like it reads every record in the file (even those that qualify for my omits) each time. No way to go for a large file. And yet, the beauty of not creating a logical file for a one time use is irresistable. Is there a trick or tip to gain some speed? Also, back before I was looking at using SQL, I saw a book, with a title and author I can't remember. But the blurb said it was written by a non RPG programmer who wrote programming on an iSeries. I think he did SQL in RPG. Anyway, his book was about performance of SQL on the iSeries. Does anyone know the name of this book?

[bill.austin@infor.com]

Speed in SQLRPG, or lack thereof

---

Code