Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sybases indexes and performance

Posted on 2006-06-29
5
Medium Priority
?
351 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 540 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

Tech spooks aren't just for those who are tech savvy, it also happens to those of us running a business. Check out the top tech spooks for business owners.
Let's take a look into the basics of ransomware—how it spreads, how it can hurt us, and why a disaster recovery plan is important.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

604 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