ExecuteSQL on Microsoft SQL Server can be drastically slower with “Parameterize Expression Language” enabled

What you’ll see

When using ExecuteSQL against Microsoft SQL Server, the same query can run 10x to 1000x slower when Parameterize Expression Language = true versus false.

Example:

select sccna.mac_address

from snow_cmdb_ci_network_adapter sccna with (NOLOCK)

where 1=1 and sccna.cmdb_ci = ${device_identifier}

Parameterize Expression Language = true is much slower than Parameterize Expression Language = false

Why this happens 

With Parameterize Expression Language = true, Clockspring uses a parameterized / prepared statement under the hood (JDBC parameters).

With Microsoft SQL Server a very common performance trap is a data type mismatch between the parameter and the indexed column, which triggers an implicit conversion during the comparison. Once SQL Server is forced to convert types during the predicate it often can’t use the index efficiently and falls back to an index scan (or worse), instead of an index seek.

A classic case:

• Column is VARCHAR(...)

• JDBC parameter is treated as NVARCHAR(...) (Unicode)

• SQL Server does an implicit conversion at runtime

• Result: optimizer picks a bad plan, index seek becomes an index scan

Fix options (recommended order)

Option A (best workaround inside the query): force the parameter to the right type

If your column is VARCHAR(32), explicitly convert the parameter to VARCHAR(32) in the query:

SELECT *
FROM nvdb_cve
WHERE cve = CONVERT(VARCHAR(32), ${cve});

This keeps the query as a single SELECT, so Clockspring still detects it as a SELECT and you keep the no_data relationship behavior.

Why this works: it forces SQL Server to treat the parameter as VARCHAR(32), avoiding the implicit conversion that ruins the index usage.

Option B: disable parameterization 

Set Parameterize Expression Language = false and use quoted substitution:

... WHERE cve = '${cve}'

This often restores fast “literal-style” execution plans.

Risk: this is no longer a prepared statement. If the value can be influenced by untrusted input, you’re opening the door to SQL injection. If the value is controlled (internal IDs, not user-provided), this may be acceptable, but don’t pretend it’s “safe.”

Practical guidance for Clockspring users

• If you’re using Microsoft SQL Server and you see big performance drops when parameterizing, first assume type mismatch and update your query to match types deliberately:

  • VARCHAR column: use CONVERT(VARCHAR(n), ${param})

  • NVARCHAR column: use CONVERT(NVARCHAR(n), ${param})