Get started Bring yourself up to speed with our introductory content.

Multiple-row fetches

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.


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

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.