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
 

 

including all primary keys in where condition is cost eff?

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

New User


Joined: 16 Apr 2009
Posts: 4
Location: chennai

PostPosted: Fri Apr 17, 2009 6:05 pm    Post subject: including all primary keys in where condition is cost eff?
Reply with quote

i have a query Q in my cobol program which acess table X thru index I.

X has 5 primary keys(partition no,id, timestamp,p4,p5) and index I has all these 5 colums.

currently only 2 primary keys(partion number and id) are used in where conditon of the query Q.

Partition number is assumed using the timestamp i.e for every day a new partition number will be available, so these partition numbers is got from another table for the timestamp interval between say'2009-04-01-00.00.00.00000' to '2009-04-01-23.59.00.00000'.So timestamp filter is indirectly applied to the Q with partition no.

when i use only (partition no and id ) in where condition , Q consumes lot of CPU time(verified thru visual explain). But when i add timestamp interval ('2009-04-01-00.00.00.00000' to '2009-04-01-23.59.00.00000' )in Q,CPU time reduces drastically(visual explain tool).

Shortly will adding all the primary key in where condition of an query which acess thru primary key index improve CPU performace eventhough some of the where conditions are useless.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Apr 17, 2009 6:20 pm    Post subject:
Reply with quote

Quote:
eventhough some of the where conditions are useless.


why will they be useless?
Back to top
View user's profile Send private message
muthuvel

Active User


Joined: 29 Nov 2005
Posts: 217
Location: Chennai

PostPosted: Fri Apr 17, 2009 6:21 pm    Post subject:
Reply with quote

Quote:
will adding all the primary key in where condition of an query which acess thru primary key index improve CPU performace

Yes.It will reduce time.
Quote:

eventhough some of the where conditions are useless.


How do u say they are useless?will adding this in Where clause cause discrimination of any unwanted record?
What was the outcome cost using all the keys in EXPLAIN tool?
Back to top
View user's profile Send private message
prakashbk

New User


Joined: 16 Apr 2009
Posts: 4
Location: chennai

PostPosted: Fri Apr 17, 2009 7:27 pm    Post subject:
Reply with quote

muthuvel wrote:
Quote:
will adding all the primary key in where condition of an query which acess thru primary key index improve CPU performace

Yes.It will reduce time.
Quote:

eventhough some of the where conditions are useless.


How do u say they are useless?will adding this in Where clause cause discrimination of any unwanted record?
What was the outcome cost using all the keys in EXPLAIN tool?


the rows returned for the first 2 where conditions will 100 % satisfy the next three where condition i am going to add.
Improvemt was around 90% by adding all the keys in where condition compared to having only 2 keys in where
Back to top
View user's profile Send private message
prakashbk

New User


Joined: 16 Apr 2009
Posts: 4
Location: chennai

PostPosted: Fri Apr 17, 2009 7:28 pm    Post subject:
Reply with quote

dbzTHEdinosauer wrote:
Quote:
eventhough some of the where conditions are useless.


why will they be useless?


the rows returned for the first 2 where conditions will 100% satisfy the next three where condition i am going to add.
Back to top
View user's profile Send private message
prakashbk

New User


Joined: 16 Apr 2009
Posts: 4
Location: chennai

PostPosted: Fri Apr 17, 2009 7:42 pm    Post subject: Reply to: including all primary keys in where condition is c
Reply with quote

the rows returned for the first 2 where conditions will 100 % satisfy the next three where condition i am going to add.
Improvemt was around 90% by adding all the keys in where condition compared to having only 2 keys in where
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 Joinkeys with condition scdinesh DFSORT/ICETOOL 10 Tue Feb 14, 2017 12:20 am
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm
No new posts Joinkeys with duplicated keys juares castro SYNCSORT 19 Fri Sep 23, 2016 5:58 am
No new posts INCLUDE condition questions van bui DFSORT/ICETOOL 1 Thu Jun 30, 2016 9:52 am
No new posts SFTP to primary and secondary servers mallik4u JCL & VSAM 4 Tue Jun 07, 2016 12:52 pm


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