Solved

Sybase query performance problems

Posted on 2006-06-28
8
1,712 Views
Last Modified: 2008-01-09
Hi All,

would appreciate your help please. I am not at all a database person, so please be patient with me..

trying to learn while trying to accomplish this task..

I have a query performing bad.. might be indexes etc are missing..

below is the query:

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, priority,exe_capacity,booking_type,acs_code,market_maker, gen.id as general_id, price_type, price_precision, ticketing_type, buy_settlement_type, sell_settlement_type, sales_initials, hide_order, base_principal_precision, commission_precision, last_user
FROM
Context con, Subcontext subc, General gen, RefExeCountry rec
WHERE  
con.id = subc.context_id
AND gen.context_id = subc.context_id
AND  gen.subcontext_id = subc.subcontext_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 subc.exe_capacity IN ('*', '*')
AND subc.booking_type IN ('*', '*')
AND subc.acs_code IN ('*', '*')
AND subc.market_maker IN ('*', '*')
AND gen.version = (select max(version) from General where id = gen.id and valid = 1)
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

and below is stats I am getting:

Execution Time 4.                                                            
SQL Server cpu time: 400 ms.  SQL Server elapsed time: 380 ms.                                          

so ofcourse it is taking huge  time..

Looks like this is happening due to the SubContext table where index is not being used..

any help optimizing this would be appreciated..

sorry, i don't have expereince with databases.. so please be patient while I am learning this as well..

thanks                                                
0
Comment
Question by:narrav
  • 3
  • 2
8 Comments
 

Author Comment

by:narrav
ID: 17005935
if you want, I can send the query plan for these query..

please help
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 17006408
400ms doesn't seem like a huge time to me! 8-)

Can you send the table & index structure (sp_help [table]), and the query plan/showplan (set showplan on [...] set showplan off)?
0
 

Author Comment

by:narrav
ID: 17009573
Joe,

Requirement is to process the request in 3 ms -- and there are many queries .. the one above is just one of the query of many queries..

ofcourse other queries may be perfoming bad as well, but this is one of the query that was recently changed due to addition of new table SubContext ---

and then query started performing bad..

so, something one of the guy working on this (left now) didn't do it correctly due to which atleast I know this query is taking long time...


ideally -- the old query (without SubContext table etc..) in prod is taking:


Execution Time 0.                                                            
SQL Server cpu time: 0 ms.  SQL Server elapsed time: 50 ms.                                          


sometimes cpu time is coming out to be 100 ms, and sometimes 0 ms (as above).. if I keep running it again and again...

but ofcourse it is much less than new query.. so something is wrong..
i cannot reach 3 ms.. but atleast perform better than this right?

sorry for the clobbered output, but is it readable or you want me to put it in some other format for sp_help on the table:


sp_help Subcontext
                                          
                                                      
Column_name      Type      Length      Prec      Scale      Nulls      Default_name      Rule_name      Access_Rule_name      Identity
context_id      int      4      [NULL]      [NULL]      0      [NULL]      [NULL]      [NULL]      0
subcontext_id      int      4      [NULL]      [NULL]      0      [NULL]      [NULL]      [NULL]      0
priority      int      4      [NULL]      [NULL]      0      [NULL]      [NULL]      [NULL]      0
exe_capacity      char      10      [NULL]      [NULL]      0      Subcontext_exe_ca_1513105450      [NULL]      [NULL]      0
booking_type      char      10      [NULL]      [NULL]      0      Subcontext_bookin_1529105507      [NULL]      [NULL]      0
acs_code      char      10      [NULL]      [NULL]      0      Subcontext_acs_co_1545105564      [NULL]      [NULL]      0
market_maker      char      10      [NULL]      [NULL]      0      Subcontext_market_1561105621      [NULL]      [NULL]      0
                                                      
index_name      index_description      index_keys      index_max_rows_per_page      index_fillfactor      index_reservepagegap
index_created                  
PK_Subcontext             clustered, unique located on default                           context_id, subcontext_id      0      0      0      Jun  3 2006  1:56PM                  
No defined keys for this object.                                                      
Object is not partitioned.                                                      
Lock scheme Datarows                                                            


Also, query plan for above query is as below:


QUERY PLAN FOR STATEMENT 1 (at line 1).


    STEP 1
        The type of query is SELECT.
        Evaluate Ungrouped MAXIMUM AGGREGATE.

        FROM TABLE
            RefExeCountry
        Nested iteration.
        Table Scan.
        Forward scan.
        Positioning at start of table.
        Using I/O Size 4 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

    STEP 2
        The type of query is INSERT.
        The update mode is direct.
        Worktable2 created for REFORMATTING.

        FROM TABLE
            RefExeCountry
            rec
        Nested iteration.
        Table Scan.
        Forward scan.
        Positioning at start of table.
        Using I/O Size 4 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.
        TO TABLE
            Worktable2.

    STEP 3
        The type of query is INSERT.
        The update mode is direct.
        Worktable1 created, in allpages locking mode, for ORDER BY.

        FROM TABLE
            Subcontext
            subc
        Nested iteration.
        Table Scan.
        Forward scan.
        Positioning at start of table.
        Using I/O Size 4 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            Context
            con
        Nested iteration.
        Index : Context_idx1
        Forward scan.
        Positioning by key.
        Keys are:
            id  ASC
        Using I/O Size 2 Kbytes for index leaf pages.
        With LRU Buffer Replacement Strategy for index leaf pages.
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            General
            gen
        Nested iteration.
        Index : General_idx1
        Forward scan.
        Positioning by key.
        Keys are:
            context_id  ASC
        Using I/O Size 2 Kbytes for index leaf pages.
        With LRU Buffer Replacement Strategy for index leaf pages.
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            Worktable2.
        Nested iteration.
        Using Clustered Index.
        Forward scan.
        Positioning by key.

        Run subquery 2 (at nesting level 1).
        Using I/O Size 4 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.
        TO TABLE
            Worktable1.

    STEP 4
        The type of query is SELECT.
        This step involves sorting.

        FROM TABLE
            Worktable1.
        Using GETSORTED
        Table Scan.
        Forward scan.
        Positioning at start of table.
        Using I/O Size 16 Kbytes for data pages.
        With MRU Buffer Replacement Strategy for data pages.
    STEP 1

NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 1.

  QUERY PLAN FOR SUBQUERY 2 (at nesting level 1 and at line 30).

    Correlated Subquery.
    Subquery under an EXPRESSION predicate.


    STEP 1
        The type of query is SELECT.
        Evaluate Ungrouped MAXIMUM AGGREGATE.

        FROM TABLE
            General
        Nested iteration.
        Using Clustered Index.
        Index : General_18720066691
        Forward scan.
        Positioning by key.
        Keys are:
            id  ASC
        Using I/O Size 4 Kbytes for index leaf pages.
        With LRU Buffer Replacement Strategy for index leaf pages.
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

  END OF QUERY PLAN FOR SUBQUERY 2.



Regards
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:narrav
ID: 17011005
please help!!!


also, i found out on internet that denormalizing of the data is a good idea..

but how can i denormalize it without much effecting my application.. i don't want to change the application all over the place..

so need to make minimum changes..

please help
0
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 135 total points
ID: 17015301
How many rows does this return? Even if it always returned just one row, 3ms is not a reasonable expectation. It will probably take longer than that just to send one row over the network to the client app!

> sometimes cpu time is coming out to be 100 ms, and sometimes 0 ms

This is always rounded to the nearest clock tick, and then converted to ms (default clock tick is 100ms). It will always be a multiple of 100ms (for the default clock tick length). 49ms will be reported as 0ms and 51ms reported as 100ms.

So before we go any further, we need to talk about this requirement for 3ms. Where did that come from, and how was it reached?

Next - which version of ASE is this?

Also, WHERE clauses like these:

> AND con.client IN ('*', '*')

just make no sense. "IN" forces special processing which is usually slower. Was there a reason this can't be written as:

   AND con.client = '*'  

?

There are many opportunities to rewrite this as faster code... for example, the ORDER BY. Do you actually have a business requirement to get the rows back in a consistent order? Do we even need this at all? Even if we do, where are the rows being sent? Can they be sorted in the client rather than in the server?

You have an outer join:

> AND con.exe_country *= rec.code

Do you really mean get every context even if it doesn't have a matching RefExeCountry?

We're getting a table scan on SubContext because it has no indexes at all. How many rows are in the table, and how many are likely to match your query?

Can you also post sp_helpindex results for all the tables in the query?
0
 
LVL 19

Expert Comment

by:grant300
ID: 17063415
I agree with Joe whole heartedly and would suggest you take his advice in the long run.
That whole deal with the In ('*','*') is really not good...

Looking at the addition of the Subcontect table, it does not look as if it should introduce
that much additional overhead.
In the mean time, there may be a quick trick to get you back to where you where before.

Rearrange the order of the FROM clause from this:
  FROM Context con, Subcontext subc, General gen, RefExeCountry rec
to this:
  FROM Context con, General gen, RefExeCountry rec, Subcontext subc  

Then bracket the entire SELECT statement with:
  SET FORCEPLAN ON
  SELECT .......

  SET FORCEPLAN OFF

This is a giant optimizer hint that tells it that the order in which the tables appear in the
FROM clause is the order in which the query plan should be built.  The reason I suggest
this is that the now-defunct coder put the Subcontext table in the middle of the FROM
instead of adding it to the end as one might expect of a later addition.  That plus some
arcane (and possible obsolete) knowledge about how the query optimizer works led me
to this conclusion.

Try that and see if you get (most) of the speed back.

BTW, there is a Clustered Unique PK Index on the Subcontext table so you don't need
to add another index.

Let us know what happends and ship us a new ShowPlan output.
Also let us know what version of Sybase you are running.  There may be some other things
you can try if you are running 12.5.1 or higher.  For instance, the embedded MAX subquery
and implied group by on General could be done with a derived table.  I'll explain next round.

One last thing.  When you have outer joins involved, it is usually preferrable to use the ANSI
JOIN syntax.  It is clearer and less ambigous than the Sybase specific synctax you (the greater you)
are using now.


Bill
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

A safe way to clean winsxs folder from your windows server 2008 R2 editions
We have come a long way with backup and data protection — from backing up to floppies, external drives, CDs, Blu-ray, flash drives, SSD drives, and now to the cloud.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now