1. Minimize network calls
The fewer number of times you need to make calls across the network the better your program will perform.
The fewer number of times you need to make calls across the network the better your program will perform.
2. Minimize passes through the data
Try to read each data item only once. The more times you access the same data the worse performance will be.
Try to read each data item only once. The more times you access the same data the worse performance will be.
3. Put the work into the SQL, not the
program
Let SQL do the work... DB2 can optimize SQL better than you can optimize your programming language of choice.
Let SQL do the work... DB2 can optimize SQL better than you can optimize your programming language of choice.
4. Unlearn the “flat file” mentality
SQL is a set-based language. Each SQL statement can operate on multiple rows of data at once. And joins are more efficient than opening multiple cursors and performing "master file" processing logic on them.
SQL is a set-based language. Each SQL statement can operate on multiple rows of data at once. And joins are more efficient than opening multiple cursors and performing "master file" processing logic on them.
5. Be sure data type and length match in
predicates
Even though DB2 no longer automatically degrades non-matching predicates to Stage 2, it is still a best practice to match the data type and length for columns and host variables that participate in predicates.
Even though DB2 no longer automatically degrades non-matching predicates to Stage 2, it is still a best practice to match the data type and length for columns and host variables that participate in predicates.
7. Document your code
Without proper documentation application maintenance becomes difficult... especially if you used any SQL tuning tricks/techniques.
Without proper documentation application maintenance becomes difficult... especially if you used any SQL tuning tricks/techniques.
8. Always check the SQLCODE or SQLSTATE
If you don't check for an error you may be processing with bad, incorrect, or missing data.
If you don't check for an error you may be processing with bad, incorrect, or missing data.
9. Analyze your access paths (and tune your SQL in test)
If you do not examine the access paths formulated by the DB2 optimizer for your SQL then how do you know how efficient (or not) your code is (or will be).
If you do not examine the access paths formulated by the DB2 optimizer for your SQL then how do you know how efficient (or not) your code is (or will be).
No comments:
Post a Comment