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
Adaptive Server version 15.0.1 allows you to automatically convert literal values in SQL queries to parameter descriptions (similar to variables). An sp_configure option supports this feature, which is called enable literal autoparam.
To enable or disable enable literal autoparam server-wide, use:
sp_configure "enable literal autoparam", [0 | 1]
Where 1 automatically converts literals to parameters, and 0 disables the feature. The default is 0.
You can set literal parameterization at the session level with the set command:
set literal_autoparam [off | on]
If you enable literal auto parameterization, the SQL text
of the earlier select count(*) example
queries 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 (@@@V0_VARCHAR, @@@V1_VARCHAR)
is transformed to:
select substring (name, 3, 4) from sysobjects where name in (@@@V0_VARCHAR, @@@V1_VARCHAR)
Any combination of values that replace the literals, 3, 4, systypes and syscolumns are
transformed to the same SQL text with the same parameters and share
the same query plan when you enable the statement cache.
Literal auto parameterization:
Reduces compilation time on the second—and subsequent—executions of the query, regardless of the literal values in the query.
Reduces storage space SQL text, 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 literal auto 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 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. Transforming the literals in the SQL statement into variables can significantly expand the size of the SQL text (especially if there are a large number of literals). Enabling literal auto parameterization may result in Adaptive Server not caching some SQL statements that it would otherwise have cached.
univarchar and unichar literals are not supported.
If you are using a multibyte character set, enable literal autoparam is automatically disabled.
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 of the different datatypes:
select name from sysobjects where id = 1 select name from sysobjects where id = 1.0
Their parameterized versions are:
select name from sysobjects where id = @@@V0_INT select name from sysobjects where id = @@@V0_NUMERIC
