Question about optimizing a query.

[Sethington]

I have a query that is taking about 10 seconds to execute. I'm trying to optimize the query. Here is the code:

SELECT ACCOUNT_LEVEL,PROGRAM,ANNUAL_FEE, APR_FLOOR_RATE,CASH_ADV_RATE,LATE_FEE,NSF_FEE,APR_RATE,APR_BASIS,LATE_GRACE_DAYS,
DELINQ_MSG_MERCH,DELINQ_MSG_CASH,ACCRUAL_DATE,PAPER_GRADE,REQ_PC_ELIG,STRATEGY,
BANK,SYSTEM,PRINCIPLE,AGENT,APR_BASE_VALUE,CASH_ADV_FEE_RATE,DELINQ_PURCHASE_MARGIN_RATE,
DELINQ_CASH_MARGIN_RATE FROM BANK.VW_PRODUCT_CATALOG WHERE OFFER_CODE = ? AND CARD_TYPE=? WITH UR

My question is would this query run faster if I create a view that contains only the columns that I need and rewrite the query as:

SELECT * FROM BANK.VW_PRODUCT_CATALOG WHERE OFFER_CODE = ? AND CARD_TYPE=? WITH UR

Or

 

[Mercury2]

Which OS are you working on ?

 

[MarcLodge]

Seth,

If Offer_code and Card_type are in the index, then you will gain no performance increase by creating a view keyed on these columns. If they are not in an index, then you probably would, but you'd be better off adding and index to the table based on those columns.

Marc

 

[blom0344]

10 seconds seems a pretty long time if indices do exist. Proper use of indices should give almost instanteneous results for such a straightforward query. Depending on the cardinality of offer_code and card_type, you should check out alternatives to regular B-tree indices.

http://www.db2mag.com/db_area/archives/2003/q2/deng.shtml

Ties Blom

 

[Sethington]

Thanks for your input. I think better indexing will fix our situation. I know some of these columns are already indexed but I think they are indexed too many times and not properly.