Solved

sybases indexes and performance

Posted on 2006-06-29
5
349 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Today’s effective marketing is about coming down to the customers’ level and engaging in a whole new way. A text message is one of the most effective and influential ways that you can engage your customers. Here are eight ways that you can utilize t…
Make the most of your online learning experience.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

687 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