We like to create a database that stores CHARs as "UTF16-LE", but literally ALL (!) collations listed with -l say they are incompatible with "UTF-16LE". So what is the magic trick...?
AFAIK, in SQL Anywhere neither CHAR nor NCHAR data types can use UTF-16 to store values. When using UNICODE you are restricted to UTF-8 (which will often need much lesser storage) - in contrast to say, MS SQL Server using UTF-16 for NCHAR data IIRC.
Volker is correct. The database does not store data in UTF16. It is supported only on the client side and as an encoding for LOAD, UNLOAD, CSCONVERT, etc. Use a UTF-8 encoding to store Unicode data in the database.
Actually we must store Chinese text AND English / German text in the same database due to the nature of the application. While this is possible with UTF-8, we expect that particularly the asian characters will bloat the .db file with significant overhead due to UTF-8's bias for latin characters. We further expect a performance drawback due to that. So we wanted to benchmark, what happens if UTF-16LE is use -- and asked dbinit -le whether it is supported (which says, it IS supported).
So the question is not "how to store unicode" but "how to use UTF-15"? 🙂
dbinit -le lists all encodings known to SQLAnywhere, not just the encodings that can be used in a database. GB18030 is not supported in databases either because you cannot determine the size of a character from the first byte; however, it is supported as a client charset, and in LOAD, UNLOAD, CSCONVERT.
I guess the output from DBINIT -le(+) leaves some questions on its own... - obviously it shows more collations/encodings than are allowed for with DBINIT.
Why then does dbinit -le list so many UTF16 entries?
There are (just) two, one for "UTF-16BE" and one for "UTF-16LE". In my understanding, each row lists one encoding/collation name in SA naming standard and its appropriate name in 5 other categories (and the first row seems to miss an "ASE" name...).
FWIW: The output seems quite identical for current versions like 12.0.1.4216.
To summarize: SQL Anywhere cannot store any other unicode encoding besides UTF-8, right?
This effectively implies a substantial overhead in .db file size for mixed database which have to store Chinese and Western characters at the same time. Is there a solution available to reduce this overhead or is the effect so small that SAP simply does not see any need to prevent the effect?
Depending on the mix of Chinese and Western data, there might be storage overhead or there might be storage savings: Western data will take 1 byte per character and Chinese data will be 3 bytes per character. For UTF16, it is (almost) always 2 bytes character for both Western and Chinese. The storage overhead would be greatest when storing only Chinese characters. For a fairly even mix of Western and Chinese, you will break even.
Whether the effect is too small or too large depends on the application, the text stored, the absolute size of the database and, above all, your own particular requirements.
Would UTF16 storage be advantageous for storage and/or performance in some circumstances? Sometimes yes, sometimes no. And, many times, it doesn't matter if one approach outperforms the other when both solutions provide completely adequate space & time performance for the given situation.
I can't say whether SQLA will ever support the use UTF16 as a database storage encoding but it does not currently do so. However, it's entirely possible that SQLAnywhere as it exists today will still completely meet your needs.
ASE does not have endian-specific UTF16-encodings: 'utf16' is always the same endian as the server (or presumably the endian of the client if the client requests 'utf16').
SQLA lets you specify a specific endian and 'utf16' is an alias for the platform endian. I believe you can even refer to 'UTF-16PE' (for Platform Endian) on SQLA as an alias. It's not listed above because it's not the canonical name for any of the categories listed in the chart. It's just an extra alias.
On a big-endian platform, FWIW, the 'utf16' ASE alias would appear on the first line.
Apparently I can't speak for John, but given his response, I don't see that he has stated to be "convinced there will not be a performance problem" in your case.
In my understanding, he has told "it depends" whether UTF-16 may be more performant or not (or correctly: need more or less storage space) than UTF-8, and that even a performance difference may not really be noticeable for users.
As SA does not offer both UTF-8 and UTF-16 for storage, in my understanding, you could attempt to test whether storage and performance do behave worse with UTF-8 compared to using only non-unicode character sets, i.e. to a single-byte charset for western and a fitting chinese character set for chinese data. IMHO, that would be the trade-offs to check for.
(I assume Chinese and Western data will belong to the same tables and columns, so it would not be possible to use CHAR for one and NCHAR for the other - or to use two different databases with different character sets?)
Volker is correct: I did not say that there would not be a performance problem in some cases. Some cases will have better space performance when using UTF16 and some will have better space performance when using UTF8. Some cases will have better time performance when using UTF16 and some will have better time performance when using UTF8 (and those are not necessarily the same cases). Whether the performance is a "problem" is completely subjective. Often, either approach yields completely acceptable storage & time performance characteristics for the job at hand. Only you can measure the system and determine if it meets your needs.
I think measuring this makes no sense anymore, as apparently SQL Anywhere cannot store UTF-16 anyways. Either the performance is good with UTF-8 or we have to use a different database product.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.