|
View previous topic :: View next topic
|
| Author |
Message |
skrishnavijay
New User
Joined: 26 Sep 2007
Posts: 25
Location: chennai
|
|
|
|
Hello,
I have an performance issue with the following query
SELECT COUNT (DISTINCT APP_ID)
INTO :WS-CNT
FROM AO_APPL A, AN_APPL B
WHERE
A.APP_ID <> :HS-APPL-ID AND
A.TIN_NO = :HS-TIN-NO AND
B.APP_ID = A.APP_ID AND
A.AP_TMP_ST > :HS-TIME-STAMP
can I have any other way to represent the above query?
DISTINCT is causing for the more cpu utilization time.
Also please explain me the order in which DB2 executes this stament. According to my understanding, DB2 joins the tables first and then process the queries.
Thanks in advance,
Krishna |
|
| Back to top |
|
 |
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008
Posts: 107
Location: New York
|
|
|
|
Hi
1) It is (Rewriting of the Query) depending upon your Tables Primary Keys and data on the Two Tables. If you could provide Keys and sample data, some one can help to some extend. It would be better if you could contact your DBA, they will provide better solution.
2) Yes. That is true. First DB2 joins both the tables depending on your WHERE clause, then it processes the query. |
|
| Back to top |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL
|
|
|
|
Krishna,
From application programming point of view I don't see anything wrong with your query, other than a minor syntax error. You would need to qualify APP_ID in DISTINCT clause. For performance issue, you may want to contact the system DBA group and have them take a look on table structures.
And, I think you are right, it'll join the tables first. |
|
| Back to top |
|
|
skrishnavijay
New User
Joined: 26 Sep 2007
Posts: 25
Location: chennai
|
|
|
|
Thanks a lot for your suggestion.
Regarding table keys and others i will get back.
So whatever the order we specify in the WHERE clause, DB2 joins all the tables mentioned and process the queries in its own manner.
Please correct me if i am wrong.
Thanks,
Krishna |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| The order does not really matter ... DB2 is smart enough to rearrange it to choose the optimal access path .... here the join is done first and the result set is sorted in a sort file to remove the duplicates .. so using distinct will definitely require an extra sorting .... |
|
| Back to top |
|
|
bhushan.shete
New User
Joined: 14 Nov 2006
Posts: 17
|
|
|
|
Hi
| ashimer wrote: |
| the result set is sorted in a sort file to remove the duplicates .. |
Here on which keys(field/column) records are sorted? is it that columns mentioned in select clause are used for sorting?
Regards
Bhushan |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
The sort would be used to islolate the DISTINCT values. |
|
| Back to top |
|
|
bhushan.shete
New User
Joined: 14 Nov 2006
Posts: 17
|
|
|
|
hi dick
I did lot of combination with distinct, finally come to conclusion that when we use distinct in select, columns in select clause are only considered for sorting(i.e. to eliminate duplicates). Kindly confirm this.
Earlier I assumed it as first column after writing distinct is considered to get distinct value, which was of course a wrong assumption.
thanks
bhushan |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| Kindly confirm this |
Yes, for this case that is true.
For other SELECTs, there may be other reasons for sorting. |
|
| Back to top |
|
|
|
|
