Faster is better when accessing large volumes of data. There are many ways to improve SQL performance, but here are four tips that are especially useful for high volume, read-only database access.

• Code a Set Option AlwCpyDta = *Optimize SQL statement (or the AlwCpyDta(*Optimize) parameter on the appropriate CL command). This lets the optimizer choose whether to create a new index or use a sort for a temporary copy of the data.
  
  Note that AlwCpyDta=*Yes actually means "use a copy only when it's required to perform the query." This allows the optimizer less latitude than the *Optimize option provides.



• Code a Set Option AlwBlk = *AllRead SQL statement (or the AlwBlk(*AllRead) parameter on the appropriate CL command). This maximizes system blocking when possible.



• Use a CL OvrDbF (Override with Database File) command with the SeqOnly(*Yes, mm) and/or the NbrRcds(nn) parameter(s) to specify system blocking for batch sequential Fetch's.



• Use multi-row fetches to read a set of records with each Fetch statement.