SQL DataSource Equals Instead of Like

[aderosso]

SmartGWT Enterprise version 12.1-p20220107

We have a SQL data source, using MySQL. There is a table that uses a varchar as a primary key. We're able to execute DSRequests from a ListGrid just fine, but we're having performance issues with the query. Here's the criteria being sent:

 data:{
        operator:"and",
        criteria:[
            {
                fieldName:"price_book_id",
                operator:"equals",
                value:"211557947"
            },
            {
                fieldName:"hdw_type_id",
                operator:"equals",
                value:"LO"
            }
        ]
    },

And the where clause generated is:

WHERE
    ((hdw_line.price_book_id = 211557947
        AND hdw_line.price_book_id IS NOT NULL)
        AND (hdw_std.hdw_type_id LIKE BINARY 'LO'
        AND hdw_std.hdw_type_id IS NOT NULL))

The query using LIKE BINARY works correctly, but is much slower than using "=". Is there a way to get the query to use the equals operator instead of LIKE?

[Isomorphic]

Since Smartclient 13.0 we are using complex comparison for Mysql case sensitive equality check to ensure efficiency and accuracy:
- the regular "=" comparison to enable index usage and
- LIKE BINARY comparison to ensure case sensitivity

In Smartclient 12.1 this was a hidden setting disabled by default and not officially supported, but it is now. In the nightly builds since March 18 (tomorrow) this setting will be enabled by default, but you don't have to wait for it and configure it manually in your server.properties:

sql.mysql.optimizeCaseSensitiveCriteria: true

[aderosso]

Perfect. we're actually in the midst of upgrading right now to 13.0 so we'll check that out.

Is this enabled by default in 13.0?

[Isomorphic]

Yes, in 13.0 this is enabled by default.

[aderosso]

We've upgraded our application to 13.0. We also tried using the:

sql.mysql.optimizeCaseSensitiveCriteria: true

We're still getting the "LIKE BINARY" instead of "=".

Is there something else we can do in the ds.xml to control how the clause is generated?

[Isomorphic]

This is surprising. It worked for us locally as expected, i.e. for the case sensitive request (textMatchStyle=exactCase) we've got the following SQL condition generated (schematically):

table.column = 'value..' AND table.column LIKE BINARY 'value...'

Could you please share more details, ideally standalone use case (modified sample for example) showcasing the problem?

[aderosso]

We're getting what your describing, both "=" and "LIKE BINARY". How do we get just the = without LIKE BINARY?

[Isomorphic]

To get just an "=" you would need to use a case-sensitive operator (such as "equals" instead of "iEquals" or "contains" instead of "iContains"). "LIKE BINARY" is required to do a case sensitive comparison.

However, done how we do it, indices are used and there should not be a table scan, because the LIKE BINARY comparison is done only after the index has been used to find the case-insensitive match.