Solved

sybases indexes and performance

Posted on 2006-06-29
5
344 Views
Last Modified: 2008-03-17
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
0
Comment
Question by:narrav
5 Comments
 

Author Comment

by:narrav
ID: 17010947
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
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 135 total points
ID: 17015362
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
 
LVL 3

Expert Comment

by:knel1234
ID: 17145372
narrav,

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

cheers
knel
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An article on effective troubleshooting
There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

775 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question