How much of a performance boost do I get pulling multiple rows with one fetch, over using the optimize clause ( e.g. "optimize for 50 rows" ). From a programming point of view, I would rather use the optimize clause, because the code is simpler.
Assuming a large number of rows are returned by the request, multiple row fetches will have a substantial performance advantage over the Optimize clause. The OPTIMIZE FOR n ROWS clause is only an indicator to the optimizer & database manager how many rows out of the total result that the application is interested. If N is relatively small, the optimizer tends to choose a data access method that will return the first N rows the fastest. If N is relatively large, the optimizer will tend to choose the method that returns all of the rows the fastest.
Multiple-row fetches minimize the number of trips to the database engine to retrieve a set of rows. Trips into the database engine are fairly expensive, so this can dramatically improve performance when a large number of rows are fetched.
MORE INFORMATION ON THIS TOPIC
Search400.com's targeted search engine: Get relevant information on DB2/400.
The Best Web Links: tips, tutorials and more.
Check out this online event, Getting the Most out of SQL & DB2 UDB for the iSeries.
Dig Deeper on iSeries SQL commands and statements
Related Q&A from Kent Milligan
To solve the SQL error -321 on IBM i6.1, use the new values statement to overcome the error. If you are using an older release, declare a cursor ... Continue Reading
When working with DB2 files with columns that have both short and long names, there is no option choose which column names are returned via ODBC ... Continue Reading
When developing tables in a parent-child relationship, use a primary key and a foreign key along with a unique ID to make your database easier to ... Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.