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
 

 

Alter index vs new index performance

 
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
kanha

New User


Joined: 05 Nov 2005
Posts: 28

PostPosted: Mon Oct 31, 2011 4:12 pm    Post subject: Alter index vs new index performance
Reply with quote

To tune a program, I had to add extra columns to 2 of the existing indexes in 2 different tables. I altered the existing indexes & the performance of the program is improved by 50-60%.

However, when I am creating new indexes not to hamper some of the existing application performance, the performance is not improved, rather detoriarated.


EXISTING:Index1 of Table1:
COL1 CHAR(4) ASC

Index1 of table2:
COL1 TIMESTAMP ASC

ALTER INDEX:Index1 of Table1:
COL1 CHAR(4) ASC
COL2 CHAR(8) ASC

Index1 of table2:
COL2 CHAR(8) ASC
COL1 TIMESTAMP ASC

NEW INDEX:Index2 of Table1:
COL1 CHAR(4) ASC
COL2 CHAR(8) ASC

Index2 of table2:
COL2 CHAR(8) ASC
COL1 TIMESTAMP ASC


Can anyone throw some light why this could occur.
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 Oct 31, 2011 9:20 pm    Post subject:
Reply with quote

Hello,

When you add additional keys, you add additional overhead.

How many keys to create and how they are defined are important considerations. There is no generic "right way" - the number of inserts/deletes and the ways the table will be queried must be considered.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Oct 31, 2011 9:43 pm    Post subject:
Reply with quote

Kanha,

To analyze or even just to look into it, you have provide some SQL query .

Tell us,
After creating new indexes, did the query use the new index ?
And after droping the new index, did the query use the old index and did you get the same performance you got earlier ?

Thanks,
Sushanth
Back to top
View user's profile Send private message
kanha

New User


Joined: 05 Nov 2005
Posts: 28

PostPosted: Tue Nov 01, 2011 2:28 pm    Post subject:
Reply with quote

Yes, the query is using the new indexes.

after dropping the new indexes, the query is using the old indexes & also performing as earlier.

Can't post the query beacuse of security issues. It's a inner join between 2 tables with WHERE clause
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10231
Location: italy

PostPosted: Tue Nov 01, 2011 2:44 pm    Post subject: Reply to: Alter index vs new index performance
Reply with quote

Quote:
Can't post the query because of security issues. ...

then You should not ask for help on a public forum,
and use only the resources of Your organization !
topic locked
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   This topic is locked: you cannot edit posts or make replies.    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 setting new limit Key values for inde... srilata83 DB2 1 Fri Feb 10, 2017 9:24 pm
No new posts Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts BC, BCR, BRC, BRCL performance steve-myers PL/I & Assembler 0 Fri Dec 23, 2016 7:44 am
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
No new posts What are the way we can improve CPU p... Gunapala CN DB2 10 Mon Oct 24, 2016 2:16 pm


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