Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2011
    Posts
    2

    Post Unanswered: temp table performance issue in sybase 12.5

    We use temp tables in out stored proc and have been experiencing performance issues in retrieving data joining these temp tables with the permanent tables.

    Few issues observed is
    1. Optimizer does not select all the required index keys of the index
    2. Optimizer does not show consitent query plan for the same parameter call.Once it generates a query plan selecting some set of indexes on tables and at other exec of the same call,other set of indexes

    I am posting first issue here
    There is a permanent table A with columns A1,A2,A3 having a nonclustered index I1 with index keys as A1,A2,A3 in addition to the clustered primary key

    When a select operation is performed on this table joining temp table as below,the optimizer selects index I1 with only index key A1 and not A2.This generates high I/O's

    select a.A1
    from A a,#t t
    where a.A1 = t.t1
    and a.A2 like t.t2+'%'
    and a.A3 between t.t3 and t.t4

    but if we do not join the temp table,the optimizer does select all index keys of I1 i.e A1,A2,A3 for the below query reducing I/O's considerably

    select a.A1
    from A a
    where a.A1 = t1_value
    and a.A2 like 't2_value%'
    and a.A3 between t3_value and t4_value

    Initially we thought the "like" operator is causing the issue.But the second query with like operator performs good.

    Can anyone please explain this behaviour and suggest solution if any.
    Last edited by akshriva; 10-27-11 at 12:15.

  2. #2
    Join Date
    Oct 2011
    Posts
    2

    Question

    Can anyone please suggest some solution/alternative if you have faced any similar situation.
    Quote Originally Posted by akshriva View Post

    We use temp tables in out stored proc and have been experiencing performance issues in retrieving data joining these temp tables with the permanent tables.

    Few issues observed is
    1. Optimizer does not select all the required index keys of the index
    2. Optimizer does not show consitent query plan for the same parameter call.Once it generates a query plan selecting some set of indexes on tables and at other exec of the same call,other set of indexes

    I am posting first issue here
    There is a permanent table A with columns A1,A2,A3 having a nonclustered index I1 with index keys as A1,A2,A3 in addition to the clustered primary key

    When a select operation is performed on this table joining temp table as below,the optimizer selects index I1 with only index key A1 and not A2.This generates high I/O's

    select a.A1
    from A a,#t t
    where a.A1 = t.t1
    and a.A2 like t.t2+'%'
    and a.A3 between t.t3 and t.t4

    but if we do not join the temp table,the optimizer does select all index keys of I1 i.e A1,A2,A3 for the below query reducing I/O's considerably

    select a.A1
    from A a
    where a.A1 = t1_value
    and a.A2 like 't2_value%'
    and a.A3 between t3_value and t4_value

    Initially we thought the "like" operator is causing the issue.But the second query with like operator performs good.

    Can anyone please explain this behaviour and suggest solution if any.

Posting Permissions

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