Performance Tuning – Scenario Based
#tmp > Table1 > Table2>
but query plan would be making join order as below
Table1 > Table2 > #tmp
and it would be causing a whole index scan on both the tables Table1 and Table2 (which is as per the people expectation) before we use temp table to filter out results. However, still we can get performance improvement.
Solution –> We can use “set forceplan on” to force the join order. Though we will have table scan on Table1 and Table2 because of “forcing”, we still could get the performance improvement by avoiding index scan on the large tables Table1 and Table2 because table scan on less number of records (due to filteration by #tmp table) can be faster than index scan on large number of records. Check the Sybase Wiki @ sybasewiki.com