I've got a parameterized search query that doesn't perform too well, and I think it's because of parameter sniffing. I'd like to do something like the OPTION(RECOMPILE) mentioned in this answer, but I'm not sure what the Sybase equivalent is.
Is there a Sybase equivalent of OPTION(RECOMPILE)? Or would I need to switch to a stored procedure to get that functionality?
NOTE: I have no idea what 'parameter sniffing' is so fwiw ...
A few scenarios I can imagine (off the top of my head) that could explain poor performance for a query, where performance could be improved by forcing a (re)compile of the query:
1 - DDL changes (eg, column datatype change, index creation/modification) for a table, or updated stats, could lead to a better compilation plan; in the good ol' days it was necessary to run sp_recompile table_name after such a change, but in recent years (last 6-8 years?) this should be automatically performed under the covers; soooo, if you're running a (relatively) old version of ASE, and assuming a DDL/stats modification to a referenced table, it may be necessary to have the table's owner run sp_recompile table-name after such a DDL change
2 - with ASE 15/16 it's not uncommon for the DBA to configure the dataserver with statement cache enabled; this allows the dataserver to create light weight procedures (LWPs; aka 'temp' procedure) for queries that are oft repeated (the objective being to eliminate the costly compilation overhead for look-alike queries); the downside to using statement cache is that a difference in parameter values that could lead to large variations in record counts can cause follow-on queries to obtain/re-use a 'poor' query plan associated with a previous copy of the query; in this scenario the SQL developer can run set statement_cache off prior to running a query ... this will override any statement cache settings at the dataserver level and allow the query to be compiled with the current set of SARGs/parameters ... with the trade-off being that you'll now incur the overhead of compiling every query submitted to the dataserver
3 - if the application is using prepared statements to submit 'parameterized' queries, the process of 'preparing' the statement typically signals the dataserver to create a LWP (aka 'temp' procedure); in this scenario the first invocation of the prepared statement will be compiled and stored in procedure cache, with follow-on invocations (by the app) of the prepared statement re-using the query plan from the first invocation; again, one benefit is the elimination of costly compilation overhead for queries #2 through #n, but the downside of re-using 'poor' query plan if the parameter values can lead to a large variation in the number of records affected; the only 'fix' I can think of for this scenario is for the application to be recoded to not use prepared statements ... and if the dataserver is configured with statement cache enabled, then make sure 'set statement_cache off' is issued before submitting (non-prepared) statements to the dataserver; and of course the obvious downside is that each query will now incur the overhead of compilation
NOTES:
set statement_cache {on|off} is a session-level setting; you only need to issue it once to enable (on) or disable (off) statement cache for the rest of the session
If you know you have 2 (or 3/4/5) different sets of parameters that could lead to different optimization plans for the same query, you can trick the dataserver's statement cache lookup process by submitting slightly different versions of the same query; to the dataserver these will look like 'different' queries and thus be matched with different query plans in statement cache; with this little coding technique you could still benefit from the use of statement cache (or application-specific prepared statements) while limiting (eliminating?) the re-use of 'poor' query plans; for example, while these three queries are logically identical, the dataserver will treat them as 'different' queries because of the different table aliases ...
select count(*) from sysobjects where id = 1
select count(*) from sysobjects o where id = 1
select count(*) from sysobjects o1 where id = 1
... and if using application-side prepared statements then you would create/manage 3 different prepared statements
