Performance issues when using filters due to implicit conversion

[iCC-MK]

Description

I encountered performance issues at my customer upon using some provided filters (imx-qbm-dtbs/FilterData) when sending API requests.
Upon investigation I noticed that the generated SQL query uses an NVARCHAR in the comparison, even if the column I want to compare against is a VARCHAR. That leads to an implicit conversion and therefore to a really inefficient execution plan.
I unfortunately don't see an option to change that e.g. by setting a property on the FilterData specifying the correct type.

Steps to Reproduce

Call qer/IdentitiesService/getAllPersonAdmin(navigationState: CollectionLoadParameters) using the following navigationState:

{
    "filter": [
        {
            "CompareOp": 0,
            "Type": 0,
            "ColumnName": "IdentityType",
            "Value1": "ThisShouldBeAVarchar"
        }
    ]
}

Expected Behavior

Since IdentityType is a VARCHAR, I would expect the generated SQL query to use a VARCHAR for a performant comparison.

Actual Behavior

The generated SQL query uses an NVARCHAR instead of a VARCHAR for comparison, causing an implicit conversion in the execution and delaying the response massively on large datasets.

Environment

OIM Version 9.2 w. latest patches

If you need any more information (e.g. the exact query and execution plan), I'd be glad to help.

Thanks and have a great day!

[hannoquest]

Hello,

The construction of SQL code is not managed by code in this repository, so this needs to be fixed on the server side. I've created an issue with the ID 476360.

Thanks,
Hanno