sybases indexes and performance
Posted on 2006-06-29
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,
cha.id as charge_id, rc.charge_classname, rc.is_discretionary, rc.is_currency_specific,
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,
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 = (
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 ,
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..
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