Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527

    Unanswered: indexes on temp tables inside procs - are they used?

    I always remember sybase would never use an index created on a temp table within a stored procedure. This seems to have changed recently but the online sybase docs still state that these indexes won' be used. I'm a bit confused. Has it changed recently? are the online sybase docs wrong? or are there certain conditions to be met before these indexes are used?

    Mike

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    I dont think it does. You can check it with the showplan. Also, try using two procs. creation in one and usage in another.

  3. #3
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Also, you can use dbcc traceon(302), 307 and 310 to get indepth optimiser decisions.

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    The index is definitely being used - it says it is in the showplan and the performance indicates it's being used. My issue is simply that the docs clearly state that these indexes are not used and this was certainly the case before. I just wondered what had happened and why sybase weren't advertising the fact that these indexes are now used. Just seems odd.

    Mike

  5. #5
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Check out the following solved case. Hope you have a support id.

    http://search.sybase.com/kbx/solvedc...umber=11092625

    Since I cant paste the content as it may be against copyright laws, basically it says indexes can be picked but its not guaranteed and its better to separate creation / usage and hence the docs wont be updated.

    Now, it will be interesting to know in what cases that is infact true.

Posting Permissions

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