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
 

 

Help Needed in DB2 query Tuning

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

New User


Joined: 11 May 2007
Posts: 20
Location: Canada

PostPosted: Mon Jun 01, 2009 9:01 am    Post subject: Help Needed in DB2 query Tuning
Reply with quote

Hi,

I have the below cursor which needs to fine tuned. Will it help if I split the Query. i.e Having the Subquery as a different cursor and CUR1 as simple Sql. Any other suggestions plz let me know


DECLARE CUR1 CURSOR FOR
SELECT F1
,F2
,F3
,F4
FROM TAB1
WHERE F1 IN
(SELECT DISTINCT D2
FROM TAB2
WHERE D1 = :WS-D1
AND D2 > '' -
AND D3 = :WS-D3
AND D4 = :WS-D4
AND D5 > 0
AND D6 > 0
)
AND F5 = :WS-F5
AND F6 <> '10'
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 Jun 01, 2009 11:46 am    Post subject:
Reply with quote

Hello,

How does the subquery perform when it is not a subquery?

Depending on the volume of data needed to be examined to satisfy the subquery, the performance of the outer select may be dramatically different.

Suggest you work with your dba to look at the requirement and make sure your queries and your table/index definitions will properly support this requirement.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Jun 01, 2009 6:23 pm    Post subject:
Reply with quote

Try joining TAB1 and TAB2 using F1 and D2 instead of a subquery... but this depends on the respective index definitions and vol of data ... if you could post all data we might be able to help ...
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Tue Jun 02, 2009 3:11 pm    Post subject:
Reply with quote

One quick observation. You can remove the Distinct from your subquery.
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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am


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