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.