Word of caution when enabling Unicode ODBC

One feature we’ve been anxiously awaiting when VisualWorks 7.5 was in development is support for Unicode ODBC.

(self connection getSession)
unicodeEncoding: #’UCS-2′;
unicode: true;
yourself.

Naturally as soon as it became available my inclination was to turn it on and over time migrate our database schema from varchar() to nvarchar() and from char() to nchar() etcetera. Sounds reasonable? I thought so myself, but it took about a month of running this small change in production to prove me wrong. Take a look at the server CPU graph below (Hint: Unicode was enabled late August and disabled late October),

Looking back, it all kind of makes sense. I suspect ODBC layer is encoding everything on the way out, so running a query against a plain old varchar() column with a Unicode parameter SQL ended up having to encode all row values on the fly, essentially resulting in endless table scans and lots of CPU wastage for most basic scenarios. Turning off Unicode or changing the offending column to n*char() fixes the problem.

What this means, however, is that you can’t conceivably go the Unicode route without converting all *char() columns to n*char() columns at the same time. You can certainly afford to have a small performance impacting window to ensure availability, but it would most certainly be unreasonable to stretch this transition period. So much for piecemeal approach, sigh.

Precise it aint

We knew for a while here that storing timestamps into SQL Server and selecting them back often resulted in loss of precision in the milliseconds part. There was even a regression test in the suit to make sure we hadn’t forgotten about it, the idea was that someone would find time to look a little more into why this was happening at some point. Here’s a sample of what we were seeing,

June 29, 2006 16:01:25.013 (insert)
June 29, 2006 16:01:25.014 (select)

June 29, 2006 16:03:42.535 (insert)
June 29, 2006 16:03:42.537 (select)

June 29, 2006 16:04:19.966 (insert)
June 29, 2006 16:04:19.967 (select)

Well, I finally did get around to asking the question on a VisualWorks Mailing List last night and both Reinout Heeck and Steven Kelly came back suggesting that was a SQL Server issue with a reference to back it up,

datetime

Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.

Example:
01/01/98 23:59:59.999
Rounded:
1998-01-02 00:00:00.000

Example:
01/01/98 23:59:59.995,
01/01/98 23:59:59.996,
01/01/98 23:59:59.997,
01/01/98 23:59:59.998
Rounded:
1998-01-01 23:59:59.997

Example:
01/01/98 23:59:59.992,
01/01/98 23:59:59.993,
01/01/98 23:59:59.994
Rounded:
1998-01-01 23:59:59.993

Example:
01/01/98 23:59:59.990
01/01/98 23:59:59.991
Rounded:
1998-01-01 23:59:59.990

But lets look at the bright side – life never gets boring in IT.