So, if you had asked me yesterday whether YetaWF data is stored as UNICODE in SQL tables, I would have said "Of Course!". Since YetaWF supports localization, we have to.

Well, assumptions are a death sentence for any developer. But how could I be wrong? I wrote all of YetaWF (well, except for all the add-ins, courtesy of the open source community). I "meant" for data to be stored in UNICODE. And it seemed to work just fine. Until...

I was working on some project (not an open source project, sorry) based on YetaWF that essentially scans your entire site. This of course makes heavy use of SQL to store all data. It continually accesses one SQL table in particular by "DomainIdentity" (an integer value) and the "PageUrl" (a string). Both DomainIdentity and PageUrl make up the composite primary key and are indexed.

Looking at the Activity Monitor in Microsoft SQL Server Management Studio I noticed that retrieving a record by DomainIdentity and PageUrl took +300ms. That's an eternity (!) for a primary key. 

Looking at the execution plan revealed this:

Well something is seriously wrong... What's with the CONVERT_IMPLICIT? I thought SQL was on drugs. Of course we use NVarChar.

But I checked the source code, and almost had a heart attack. Well, there are some VarChar sprinkled throughout the SQL data provider code. OMG! #IAMSTUPID  We pass the PageUrl as NVarChar (@_tempParam1) but the column PageUrl is defined as VarChar. Not good.

So now, how do I fix this??? Many of the VarChar strings are used by indexes and foreign keys. So can't just ALTER them. You would get an awesome failure there. Fortunately, YetaWF already dynamically alters tables when a model changes (yes, automatically when upgrading packages, without any data loss). But this type of change was not supported (who would need that?).

I added the support to drop all indexes, foreign keys, constraints, when upgrading to YetaWF 2.0.3. YetaWF already automatically recreates these when models change so it was just a matter of dropping all of this before ALTERing the tables. And there is absolutely no data loss (which would be unacceptable).

This could have been a lot worse... Still, egg on my face, and wasted a Sunday afternoon. That'll teach me.

For the good news, the same query that prompted me to look into this now is a LOT faster.

And some of YetaWF's tables with indexed strings also benefit from this fix.