Adaptive Server version 15.0.1 and later allow you to automatically convert literal values in SQL queries to parameter descriptions (similar to variables).
To enable or disable enable literal autoparam server-wide, use:
sp_configure "enable literal autoparam", [0 | 1]
Where 1 automatically converts literal values to parameter descriptions, and 0 (the default) disables the feature.
Set literal parameterization at the session level using:
set literal_autoparam [off | on]
In versions of Adaptive Server earlier than 15.0.1, two queries that were identical except for one or more literal values resulted in the statement cache storing two separate query plans, or two additional rows, in sysqueryplans. For example, the query plans for these queries were stored separately, even though they are almost identical:
select count(*) from titles where total_sales > 100 select count(*) from titles where total_sales > 200
If you enable automatic literal parameterization, the SQL
text of the select count(*) example
referred to above is converted to:
select count(*) from titles where total_sales > @@@V0_INT
Where @@@V0_INT is
an internally generated name for the parameter that represents the
literal values 100 and 200.
All instances of literal values in the SQL text are replaced by internally generated parameters. For example:
select substring(name, 3, 4) from sysobjects where name in
('systypes', 'syscolumns')
is transformed to:
select substring(name, 3, 4) from sysobjects where name in (@@@V0_VCHAR1,@@@V1_VCHAR1)
Any combination of values that replace the literals, 3, 4, systypes and syscolumns is
transformed to the same SQL text with the same parameters and shares
the same query plan when you enable the statement cache.
Automatic literal parameterization:
Reduces compilation time on the second—and subsequent—executions of the query, regardless of the literal values in the query.
Reduces the amount of SQL text storage space, including memory usage in the statement cache and the number of rows in sysqueryplans for abstract plans and query metrics.
Reduces the amount of procedure cache used to store query plans.
Occurs automatically within Adaptive Server, when enabled: you need not change the applications that submit the queries to Adaptive Server.
Usage issues for automatic literal parameterization include:
Adaptive Server parameterizes the literals only for select, delete, update, and insert. For insert statements, Adaptive Server parameterizes only insert ... select statements, not insert ... values statements.
Adaptive Server does not parameterize literals in queries that include a derived table.
Adaptive Server does not parameterize queries similar
to select id + 1 from sysobjects group
by id + 1 or select
id + 1 from sysobjects order by id + 1 because
of the expressions (“id + 1”) in
the group by and order by clauses.
Adaptive Server does not cache SQL statements with text longer than 16384 bytes in the statement cache (SQL statements over 16K are not cached). Transforming literals in the SQL statement into variables can significantly expand the size of the SQL text (especially if there was a large number of literals). Enabling automatic literal parameterization may result in Adaptive Server not caching some SQL statements that it would otherwise have cached.
If two SQL statements are the same except that their literal values have different datatypes, they are not transformed into matching SQL texts. For example, the following two SQL statements return the same results, but are parameterized differently because they use the different datatypes:
select name from sysobjects where id = 1 select name from sysobjects where id = 1.0
The parameterized versions of these statements are:
select name from sysobjects where id = @@@V0_INT select name from sysobjects where id = @@@V0_NUMERIC
