• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

sybases indexes and performance

hi all,

Having a big issue, looks like index is not being used on some of my dataservers...

Below is one of the query performing bad:

SELECT con.id, client, block_acc_mnc, sub_acc_mnc, prod_type, trade_type, exe_country, exe_point,
    exe_channel , isNull(rec.ordinal, (select max(ordinal) from RefExeCountry) + 1) as 'ordinal',
    subc.context_id, subc.subcontext_id, subc.priority, subc.exe_capacity,
    subc.booking_type, subc.acs_code,
    cha.id as charge_id, rc.charge_classname, rc.is_discretionary, rc.is_currency_specific,
    rc.charge_precision, rc.charge_precision_type,
    cha.version as version, rc.short_description, effective_date, min_charge,
    max_charge, basedOn, is_sliding_scale, is_calc_at_block, is_for_buy,
    is_for_sell, prorata_algorithm, currency, is_zero_for_MM , calc_type, calc_val,
    lower_thres, upper_thres
FROM Context con, Subcontext subc, Charge cha, RefCharge rc, Tier t, RefExeCountry rec
WHERE con.id = subc.context_id AND
    cha.context_id = subc.context_id AND
    cha.subcontext_id = subc.subcontext_id AND rc.id = cha.ref_charge_id
    AND con.exe_country *= rec.code AND con.client IN ('*', '*')
    AND con.block_acc_mnc IN ('*', '81QTYERR') AND con.sub_acc_mnc IN ('*', '81QTYERR')
    AND con.prod_type IN ('*', 'EQ') AND con.trade_type IN ('*', 'CA')
    AND con.exe_country IN ('*', 'JPN','*', '*') AND con.exe_point IN ('*', '*')
    AND con.exe_channel IN ('*', '*')
    AND cha.version = (
        select max(version)
        from Charge
        where id = cha.id AND valid = 1 AND effective_date <= '06/28/2006'
    AND t.charge_id = cha.id AND t.charge_ver = cha.version
ORDER by con.sub_acc_mnc desc, con.block_acc_mnc desc, client desc, exe_channel desc,
    exe_point desc, ordinal, exe_country desc, trade_type desc, prod_type desc , t.id ,
    priority desc

This query uses 2 tables


I see the index on Subcontext as below:

PK_Subcontext             clustered, unique located on default                           context_id, subcontext_id      0      0      0      Jun  3 2006  1:56PM                  

but this is not being used by my query. i don't see in query plan..

that might be one of the reason why query is taking long time...

I am not sure how to recreate the index.. just so that to be sure if I can just recreate and it will be used..

I tried

drop index Subcontext.PK_Subcontext

but error was

cannot drop because PK_Subcontext is a constraint

so i tried

 alter table Subcontext
    drop constraint PK_Subcontext

but now I am getting

cannot drop index because it still have referntial integrity constraints..

I don't want to effect all of things..
just want to recreate the index to see if it helps..

please help me optimizing this query
1 Solution
narravAuthor Commented:
atleast I was able to drop the index and recreate it by dropping all the constraints...

but still of no help..

please help me using the indexes and performance tuning the queries
Joe WoodhousePrincipal ConsultantCommented:
Just because an index is there doesn't mean that it will be faster to use it... although I agree here that the optimiser should probably have used it.

Usually this implies the optimiser statistics are stale or missing. Try running "update index statistics [table]" on *all* tables in the query? (ie. not just on subcontext)

This could help your other queries too...

Where are you at on this issue?  Do you still need help?

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now