As always it depends:
1. FETCH FIRST 50 ROWS ONLY
If the SELECT statement has an order by the complete query must be executed
before returning the first 50 rows. In this way you'll not get a better
performance
If the SELECT statement has no order by you may get a better performance
because only a part of the result set will be returned.
2. OPTIMIZE FOR x ROWS (optimization goal)
Dynamic SQL is per default performed with optimization goal *FIRSTIO, that
means the first block of data is returned as fast as possible.
Static SQL is per default performed with optimization goal *ALLIO, that
means the complete query is performed as fast as possible.
The optimization goal is important if the optimizer has to decide using an
index access or a table scan.
With optimization goal *FIRSTIO an index access may be preferred even though
if the index is only sub-optimal.
With optimization goal *LASTIO a table scan may be preferred if there are
only sub-optimal indexes.
To change the optimization goal replace X in the OPTIMIZE FOR X ROWS.
Specifying a small integer means optimization goal *FIRSTIO is used (it does
not matter if you choose, 1, 3 or 17 for X).
Specifying a large integer or ALL means optimization goal *ALLIO
For Static embedded SQL for example to fill subfiles Optimization goal
*FIRSTIO may result in a better performance.
3. FOR READ ONLY
Can be specified for SELECT-Statements. If an select statement is not read
only (by nature for example a select statement with a join or order by)
specifying FOR READ ONLY may result in a better performance, because blocked
reads can be used.
4. WITH NC
Means only you do not want to use commitment control for this insert/update
or delete statement even though you are working with journaling and
commitment control.
For more information there are several sources in the online library:
1. Database and Query Optimization
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/rzajq/rzajq.pd
f
2. SQL Performance Diagnosis on IBM DB2 Universal Database for iSeries
http://www.redbooks.ibm.com/abstracts/sg246654.html?Open
3. OnDemand SQL Performance Analysis Simplified on DB2 for i5/OS in V5R4
http://www.redbooks.ibm.com/abstracts/sg247326.html?Open
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Ashish Kulkarni
Gesendet: Wednesday, 01. September 2010 23:04
An: Midrange Systems Technical Discussion
Betreff: Question about SQL and performance
Hi
Is there any better performance in SQL query if i add below lines to my SQL
statement, suppose i know that my sql will return a maximum of 40 rows, is
there any advantage of adding these lines, i can understand adding these
lines if the SQL result may be 500 rows then this will bring only first 50
rows and thus save getting extra data,
Any ideas or thoughts on adding these lines to SQL
FETCH FIRST 50 ROWS ONLY
OPTIMIZE FOR 2 ROWS
FOR READ ONLY
WITH NC;
midrange.com
