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,
    subc.market_maker,
    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

Context
Subcontext

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
narravAsked:
Who is Participating?
 
Joe WoodhouseConnect With a Mentor Principal 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...
0
 
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
0
 
knel1234Commented:
narrav,

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

cheers
knel
0
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.

All Courses

From novice to tech pro — start learning today.