If the stored proc last recompile found uncommon statistics,
or that execution happened to assign uncommon values to
@variable, then recompile it now. If the query plan (the
compiled in-memory version) stays a long time in the procedure
cache, ASE may be executing a bad plan chosen days ago, when
the situation was different.
For instance, I've had problems with tables which begun empty
but soon got millions of rows. ASE brought the query plan into
the procedure cache (that is, compiled the stored proc) when
it was empty, chose a tablescan, then kept table-scanning even
when the table was huge. We had to force recompilations to solve.
Remember the sintax: sp_recompile TABLE
where TABLE is one the tables in your stored proc; better use the
table having table scans.
thanks for your reply... But actually its the other way around... What stored procs were doing index scans is doing table scans now... And I did do the recompile just to be sure even before you said it , but no luck.
I use to have a similar problem where a proc will just fail to work after several months of no problems. Recompile didn't work but generating the ddl then drop and recreate the proc solved the problem every time. I still don't know why it happened and think it was on a 12.0 server.
The problem has to do with the default selectivity of the column, vs. the actual selectivity of the data in the variable. IIRC the default selectivity is 0.33 in this situation, so if the statistics on the index are too broad ASE will estimate that enough of the table will have to be read for a table scan to become "optimal".
You can use optdiag to get the statistics information for the table (and its indexes). Consider using update index statistics, and using more than the default number of steps to get better stats.
as Michael explained some time ago, when an index is created on a #temp table, the stored proc is optimised again. I'm not sure this kind of optimisation applies to the @variable, which may correspond to a very selective or a very broad value in the index.
But you can try, just as a possible alternative, to use a temp table instead of a variable. Then, build an index on the temp table. This may not help with the selectivity issue, but may force a re-optimisation of the stored proc.