Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Performance issue while using SELECT DISTINCT

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
skrishnavijay

New User


Joined: 26 Sep 2007
Posts: 25
Location: chennai

PostPosted: Fri Aug 01, 2008 9:12 pm    Post subject: Performance issue while using SELECT DISTINCT
Reply with quote

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
View user's profile Send private message

Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Fri Aug 01, 2008 11:11 pm    Post subject:
Reply with quote

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
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Fri Aug 01, 2008 11:13 pm    Post subject: Reply to: Performance issue while using SELECT DISTINCT
Reply with quote

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
View user's profile Send private message
skrishnavijay

New User


Joined: 26 Sep 2007
Posts: 25
Location: chennai

PostPosted: Sun Aug 03, 2008 3:36 pm    Post subject: Reply to: Performance issue while using SELECT DISTINCT
Reply with quote

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
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Aug 04, 2008 2:11 pm    Post subject:
Reply with quote

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
View user's profile Send private message
bhushan.shete

New User


Joined: 14 Nov 2006
Posts: 17

PostPosted: Sat Dec 20, 2008 1:58 am    Post subject: Sort key while using distinct
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Dec 20, 2008 2:46 am    Post subject:
Reply with quote

Hello,

The sort would be used to islolate the DISTINCT values.
Back to top
View user's profile Send private message
bhushan.shete

New User


Joined: 14 Nov 2006
Posts: 17

PostPosted: Mon Dec 22, 2008 6:54 pm    Post subject: Conclusion
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Dec 22, 2008 10:02 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts CONTIG for performance? JPVRoff JCL & VSAM 7 Fri Jun 09, 2017 8:39 am
No new posts Issue with NDM process to transmit ES... chetanambi All Other Mainframe Topics 6 Wed May 03, 2017 10:52 am
No new posts BC, BCR, BRC, BRCL performance steve-myers PL/I & Assembler 0 Fri Dec 23, 2016 7:44 am
No new posts Execessive parameter issue Sumeendar JCL & VSAM 5 Mon Dec 19, 2016 4:35 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us