What does enabling enable "literal autoparam" for "only for ad hoc queries" mean?

[sladebe]

Using "literal autoparam" with the statement cache active is well explained.

But I don't understand the new "enable literal autoparam" setting of 2 ("converts literal values to parameter descriptions only for ad hoc queries")

"Only for ad hoc queries"?? What other choice is there? Does "enable literal autoparam" apply to stored procedures in the procedure cache? Does that even make sense? A literal constant in a stored procedure isn't going to change between different calls (like it might for ad-hoc SQL), why bother converting it to a generic parameter token?

Thanks in advance
Ben

[c_baker]

There is no behavior change if values of ‘0’ or ‘1’ are configured.

A value of '2' was introduced as this change is applied to both 'safe harbor' and 'innovation' codelines (as outlined in SAPNote 2531326). Customers of either codeline can take advantage of the new value with the assurance of being able to revert back to the previously used value, if desired or required.

Please review KBA 3150586 for an explanation of why using ‘2’ can be considered over '1'.

[ryan_hansen]

Hi,

Here is the description for the feature request:

When the configuration option 'enable literal autoparam' is set to 2, SAP Adaptive Server will disable literal auto parameterization for stored procedures.

I do not know the reason why we created a new configuration option instead of changing the existing option.

Regards,
Ryan

[sladebe]

Maybe they meant "will disable literal auto parameterization for stored procedure calls" ?

Ie., this works as expected:

create myproc @param1 int as select * from mytable where col=@param1

But the "123" in this call will not be auto-parameterized?

exec myproc 123

I could see how this would be important as the params for the first call of the stored proc are used to optimize it's query plan.