1

We have an application which uses an ORM which does not (easily) support parameterized queries. To combat this, I have enabled Forced Parameterization on our database (SQL2008 R2) in order to avoid cache bloat, and encourage plan re-use.

One of the more complex queries is not being completely parameterized, and I have narrowed it down to literal values being used in CASE statements:

SELECT
CASE WHEN auth_date > '2019-02-03 08:48:03' THEN authorisations.authorisation_amount END
FROM authorisations
WHERE auth_date > '2019-02-03 07:48:03'
AND merchant_id = 400000000031

(This query may not look intuitive, but it is a fragment of a larger set of nested subqueries, and we have to use this structure at the lowest level. This works as an example.)

The parameterized statement used by the query plan is:

StatementText="select case when auth_date > '2019-02-03 08:48:03' then authorisations . authorisation_amount end from authorisations where auth_date > @0 and merchant_id = @1"

You'll notice that the literals in the WHERE clause have been parameterized, but the date literal in the CASE statement has not.

Why is this? What am I missing? What could I try to fix this? The literals will change for every execution, so at the moment, even with forced parameterization, I will end up with a new plan per execution.

0

2 Answers 2

1

If you are able to change the query text, using APPLY and VALUES will allow forced parameterization of the CASE literal:

SELECT
    CA.authorisation_amount
FROM authorisations AS A
CROSS APPLY
(
    VALUES
    (
        CASE 
            WHEN A.auth_date > '2019-02-03 08:48:03'
            THEN A.authorisation_amount 
            ELSE NULL
        END
    )
) AS CA (authorisation_amount)
WHERE
    A.auth_date > '2019-02-03 07:48:03'
    AND A.merchant_id = 400000000031;

But explicit parameterization is better. ORMs can be ok for simple uses, but a decent one will allow you to use your own stored procedures etc. as necessary.

1
  • Great, non intuitive answer. I've verified locally: StatementText="select CA . authorisation_amount from authorisations as A cross APPLY ( values ( case when A . auth_date > @0 then A . authorisation_amount else null end ) ) as CA ( authorisation_amount) where A . auth_date > @1 and A . merchant_id = @2" No literals anywhere in the query!
    – TimBrown
    May 3 at 8:19
1

So I should have done some more digging before I posted this. The restriction is explained deep down on this page: Forced Parameterization | Microsoft Docs

Specifically, this section:

The <select_list> of any SELECT statement. This includes SELECT lists of subqueries and SELECT lists inside INSERT statements.

This tells me it's not specifically the CASE, but that I have included literals in the SELECT list.

Still, if anyone has any suggestions for working around this, I'd be interested.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.