Queryparams: varchar vs. nvarchar

During the last CFCamp Jochem van Dieten did a session on DB stuff and one item raised my interest. Basically it came down to this:

If your database uses unicode, your index uses unicode, too. Now, if you search in an indexed field and use the type “CF_SQL_VARCHAR”, the DB cannot directly use that on the index and does an index scan (or was it a table scan even?).

In order to prevent this, you should use the type “CF_SQL_NVARCHAR”. If you have a large database, that could improve performance considerably.

In my local app I switched the types to nvarchar and everything still worked (as I had expected). But of course in my local setup I don’t have enough data to really notice a difference.

What is the Pixl8 team’s opinion on this? Is this something worth considering when it comes to large Datasets? If so, should the preside core be refactored to use NVARCHAR, too?

Thank you!

Curious - I’d be surprised if this is happening in reality. We run a lot of queries with a lot of data. When we have issues with missing indexes, adding the indexes does resolve them indicating that those indexes are being used.

We are running MariaDB 11.4 (and previously 10.3 & 10.6) and use utf8mb4/utf8mb4_unicode_ci charset + collation.

@dominic.watson Thank you. I suspected as much, because I never encountered any problems in the past, but, better to ask around. :wink: