Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    20

    Unanswered: Sybase Optimizer question

    Currently, 99% of the our stored procedures has the following logic: creates temporary tables, creates indexes for these temp tables and uses these temp tables (w/ indexes) within the same stored procedure.

    The Sybase optimizer would never use the indexes for these temp tables. It would always perform a table scan on these temp tables. In short, the indexes that were created for these temp tables are totally useless. In order for the optimizer to use an index for queries involving these tables, the temp tables should be used on a called (nested) stored procedure.

    Is this true? If so, how could you prevent a gigantic web of nested stored procedures when optimizing.

    Thank you very much.

  2. #2
    Join Date
    Aug 2004
    Posts
    18
    How big are the temporary tables you are creating? If they are small, a table scan will be faster than an index scan, and the optimizer would correctly choose that method.

    Dave

  3. #3
    Join Date
    Oct 2003
    Posts
    20
    The temp tables would contain approximately 500K rows.....

Posting Permissions

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