Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2009
    Posts
    2

    Question Unanswered: Table scan when using like %

    I have this update query which I am trying to execute in Sybase and it takes a hell lot of time.

    update open_positions
    set security_id = p.security_id
    from positions op, product p
    where p.security_id like op. security_id + '%'
    and vendor = 'I'
    and inactive_d is null
    and op.match='Y'
    and op.segment is null


    The product table is a massive table and has a non-clustered index on security_id

    After I observed the Query plan, I saw the ‘product’ table was getting Table scanned.
    Please let me know why the index is not getting used here.

    Is it because
    1. I am using a like on the join clause?
    2. The where clause filter has other columns which don’t have indexes on them?

    Please help!
    Thanks

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    It is possible to use an index with like provided the wild card is not in the beginning
    So should be OK in your case
    You have 3 tables but only join 2
    I don't see a join to open_positions

  3. #3
    Join Date
    Dec 2009
    Posts
    2
    Sorry, my bad. It was a typo.

    update open_positions
    set security_id = p.security_id
    from open_positions op, product p
    where p.security_id like op. security_id + '%'
    and vendor = 'I'
    and inactive_d is null
    and op.match='Y'
    and op.segment is null


    and the fun part is - even if I remove the other filtering conditions in the where clause like (on columns vendor & inactive_d), I still get a Table scan.

    ..Is Sybase [INTERNALLY] doing something while trying to optimize the query for performance?
    Last edited by mike_bike_kite; 12-10-09 at 12:04. Reason: fixing tags for user

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by dineshdb View Post
    ..Is Sybase doing something while trying to optimize the query for performance?
    Sybase will look at what you've provided then decide if your criteria restrict the search to approx 10-20% of the table and, if not, it will table scan. In this particulr case your doing an operation on the field ( field + "%" ) so it will most likely give up guessing and just do a table scan anyway.

    I think you'd do best adding a new field to the table to contain the smaller security_id field and doing an exact match on these fields. It would also be better naming your fields so you don't have two fields called security_id - one that contains the whole field and one that's just a part of the field. If you're only pulling data on one security id then you could add the "%" to the field in your program.

    If this doesn't work then I think you should provide the create table and index statements for the tables involved and also give us an idea of what the data security_id field looks like.

  5. #5
    Join Date
    Nov 2002
    Posts
    207
    Also, optdiag output for that column on that table would help.

Posting Permissions

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