| View previous topic :: View next topic |
| Author |
Message |
Catherine
Beginner
Joined: 30 Aug 2006
Posts: 32
Topics: 15
Location: Chennai
|
 Posted: Thu Dec 28, 2006 11:34 pm Post subject: Tuning the SQL QUERY |
 |
|
| Code: |
SELECT EMP_NAME, EMP_NO, EMP_SALARY
FROM EMP_DET EMP LEFT OUTER JOIN SALARY_DETAILS SAL
ON EMP.EMP_NO= SAL.EMP_NO
WHERE SAL.EMP_SALARY > 1000
|
Apart from using a sub-query instead of joins, Is there any way to improve the performance of this query. For larger no: of output data the query above performs Poorly |
|
| Back to top |
|
 |
CZerfas
Intermediate
Joined: 31 Jan 2003
Posts: 211
Topics: 8
|
| Posted: Fri Dec 29, 2006 5:06 am Post subject: |
|
|
As always in SQL, it's a matter of volume and structure:
Do you have indices supporting your predicates?
Do you have index-only access?
Is your data clustered, i.e. sorted as the indices are defined?
What is the volume of data you have to scan to create your answer set?
And last but not least: What is the EXPECTED elapse time of this query?
Sometimes it is not clear, what work DB2 has to accomplish and therefore the expectations about elapse times are to high.
By the way: To my experience a join is more "effective" than a subselect (if you do not know more about the data than the optimizer via current statistics about the involved DB2 objects).
You see: no simple answers to questions like these.
regards
Christian |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
