Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297

    Question Unanswered: Sudden drastic decrease in single query performance

    Hey all,

    We are running Sybase ASE 12.5.3 for the database of a large scale java and web app. The program was running fine until about two days ago, when all of the sudden it started lagging horribly. It turns out that there was one query which was slowing everything down horrendously.


    Here is the original query:

    SELECT
    ...
    FROM
    Wtable w
    , SItable si
    , Stable s
    , Otable o
    WHERE
    s.OtableID=o.OtableID
    AND
    s.StableID=si.StableID
    AND
    ( si.StableID=
    value
    OR
    o.StableourceID=
    value
    )
    AND
    w.WtableID=si.WtableID
    ORDER BY
    w.WtableCode



    And this is the modified query which runs much much quicker and does not bog down the whole database:


    (
    SELECT
    ...
    FROM
    Wtable w
    , SItable si
    , Stable s
    , Otable o
    WHERE
    s.OtableID=o.OtableID
    AND
    s.StableID=si.StableID
    AND
    o.StableourceID=
    value
    AND
    w.WtableID=si.WtableID
    )
    UNION
    (
    SELECT
    ...
    FROM
    Wtable w
    , SItable si
    , Stable s

    WHERE
    s.StableID=si.StableID
    AND
    si.StableID=
    value
    AND
    w.WtableID=si.WtableID
    )
    ORDER BY
    w.WtableCode



    Apparently OR is bad news (of course, I didn't write the queries....) Does anybody have any idea as to why the first query would suddenly slow down "overnight?" Does it have anything to do with tempdb size, the database location on the segment/device/?, locking schemes??? I'm just worried that more of the queries used by the program will soon face a similar problem and am hoping to fine tune sybase to avoid this.

    (In case you can't tell, I'm not a sybase DBA)

    Thanks for any help,
    -Jesse

  2. #2
    Join Date
    Sep 2005
    Location
    France, Paris
    Posts
    34
    Hello,

    To analyse what's happen, it could be interresting to have a look to the query plan for each of your queries.
    Try to run:

    set noexec on
    go
    set showplan on
    go
    your query
    go

    This will tell you how ASE is handling your queries (indexes used, table scans etc...)
    Last edited by jflebon; 10-01-05 at 15:43.
    Regards.

  3. #3
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    Here's the output generated by showplan for the "slow version" of the query:


    QUERY PLAN FOR STATEMENT 1 (at line 3).


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

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

    FROM TABLE
    Wtable
    w
    Nested iteration.
    Using Clustered Index.
    Index : w_idx
    Forward scan.
    Positioning by key.
    Keys are:
    WtableID ASC
    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.

    FROM TABLE
    Stable
    s
    Nested iteration.
    Using Clustered Index.
    Index : s_idx
    Forward scan.
    Positioning by key.
    Keys are:
    SignID ASC
    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.

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

    STEP 2
    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 2 Kbytes for data pages.
    With MRU Buffer Replacement Strategy for data pages.

    The sort for Worktable1 is done in Serial




    And this is the output for the "fixed" query:


    QUERY PLAN FOR STATEMENT 1 (at line 1).


    STEP 1
    The type of query is INSERT.
    The update mode is direct.

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

    FROM TABLE
    Stable
    s
    Nested iteration.
    Index : s_cid_idx
    Forward scan.
    Positioning by key.
    Keys are:
    OtableID 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
    SItable
    si
    Nested iteration.
    Index : si_sid_idx
    Forward scan.
    Positioning by key.
    Keys are:
    SignID 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
    Wtable
    w
    Nested iteration.
    Using Clustered Index.
    Index : w_idx
    Forward scan.
    Positioning by key.
    Keys are:
    WtableID ASC
    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.
    TO TABLE
    Worktable1.

    STEP 1
    The type of query is INSERT.
    The update mode is direct.

    FROM TABLE
    Stable
    s
    Nested iteration.
    Using Clustered Index.
    Index : s_idx
    Forward scan.
    Positioning by key.
    Keys are:
    SignID ASC
    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.

    FROM TABLE
    SItable
    si
    Nested iteration.
    Index : si_sid_idx
    Forward scan.
    Positioning by key.
    Keys are:
    SignID 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
    Wtable
    w
    Nested iteration.
    Using Clustered Index.
    Index : w_idx
    Forward scan.
    Positioning by key.
    Keys are:
    WtableID ASC
    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.
    TO TABLE
    Worktable1.

    STEP 1
    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 2 Kbytes for data pages.
    With MRU Buffer Replacement Strategy for data pages.

    The sort for Worktable1 is done in Serial


  4. #4
    Join Date
    Sep 2005
    Location
    France, Paris
    Posts
    34
    Hello,

    So to answer your first question:
    "Does anybody have any idea as to why the first query would suddenly slow down "overnight?"

    With the second query, you give "more search argument" to the optimizer, the way you design the query, and the optimizer can use indexes: there is no table scan.

    With the first query, optimizer decided to do a tablescan on Otable and SItable. Because of the way the "or" clause is implemented in the query I guess. And depending the size of your tables, if statistics are updated or not, it could take a long time. Tablescans consume lot of I/O, are time consuming that leads to performance issue for a dataserver.

    I'm not a developper, so people are welcome to give their opinion or correct me, but I think the first query could be rewritten like this (you can try to see what is the showplan output):

    set showplan on
    go
    set noexec on
    go

    SELECT
    ...
    FROM
    Wtable w
    , SItable si
    , Stable s
    , Otable o
    WHERE
    (s.OtableID=o.OtableID
    AND
    s.StableID=si.StableID
    AND
    si.StableID=value
    AND
    w.WtableID=si.WtableID
    )
    OR
    (s.OtableID=o.OtableID
    AND
    s.StableID=si.StableID
    AND
    o.StableourceID=value
    AND
    w.WtableID=si.WtableID
    )
    ORDER BY
    w.WtableCode
    go
    Regards.

  5. #5
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    Is there any way to tell if other queries may be affected? Any recommended tweaks? (I believe the locking scheme is set to all pages for every table...this could be a potential problem, right?) This system does a lot of things and I'm sure there must be more queries that should be rewritten, but I don't want to have to wait until another problem creeps up to rewrite the sql. So...any suggestions as to how to get Sybase to run queries quicker without rewriting them? Indices, locking, cache size, etc.??? I can't really tell from the showplan output.

    Thanks a bunch.

  6. #6
    Join Date
    Sep 2005
    Location
    France, Paris
    Posts
    34
    80% (or so) of the ressources consumed by a RDBMS is directly linked to query execution. So most of the time (not always) the answer to performance issue is query or model optimisation.

    So there is no real tricks in general to run queries quicker without improve the sql code. (life will be too easy)

    But you can still make sure you run "update statistics" as often as possible, you can build a 16K pool in default data cache (or dedicated cache) to take advantage of large I/O during query processing, this will help but not solve your issue in short or mid terms.

    Locking scheme is not necessarly an issue until you have deadlocks. In this case you can switch to row locking schema (but think about "number of lock", and table reorganisation in the future...)

    It could be interresting to rebuild indexes from time to time as well (this avoid index fragmentation, forwarded rows...)
    Regards.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •