SQL Server - Handling localization of strings in nested non-deterministic view stacks

Question

While profiling a database I came across a view that is referencing some non-deterministic functions that get accessed 1000-2500 times per minute for each connection in this application's pool. A simple SELECT from the view yields the following execution plan:

That seems like a complex plan for a view that has less than a thousand rows that may see a row or two change every few months. But it gets worse with the following other observances:

1. Nested views are non-deterministic, so we cannot index them
2. Each view references multiple UDFs to build the strings
3. Each UDF contains nested UDFs to get the ISO codes for localized languages
4. Views in the stack are using additional string builders returned from UDFs as JOIN predicates
5. Each view stack is treated as a table, meaning that there are INSERT / UPDATE / DELETE triggers on each to write to the underlying tables
6. These triggers on the views use CURSORS that EXEC stored procedures which reference more of these string building UDFs.

This seems pretty rotten to me, but I only have a few years experience with TSQL. It gets better, too!

It appears the developer who decided that this was a great idea, did all this so that the few hundred strings that are stored can have a translation based on a string returned from a UDF that is schema-specific.

Here's one of the views in the stack, but they are all equally bad:

CREATE VIEW [UserWKStringI18N]
AS
SELECT b.WKType, b.WKIndex
    , CASE
       WHEN ISNULL(il.I18NID, N'') = N''
       THEN id.I18NString
       ELSE il.I18nString
       END AS WKString
    ,CASE
       WHEN ISNULL(il.I18NID, N'') = N''
       THEN id.IETFLangCode
       ELSE il.IETFLangCode
       END AS IETFLangCode
    ,dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex, N'WKS') AS I18NID
    ,dbo.UserI18N_Session_Locale_Key()  AS IETFSessionLangCode
    ,dbo.UserI18N_Database_Locale_Key() AS IETFDatabaseLangCode
FROM   UserWKStringBASE b
LEFT OUTER JOIN User3StringI18N il
ON    (
il.I18NID       = dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex, N'WKS')
AND il.IETFLangCode = dbo.UserI18N_Session_Locale_Key()
)
LEFT OUTER JOIN User3StringI18N id
ON    (
id.I18NID       = dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex,N'WKS')
AND id.IETFLangCode = dbo.UserI18N_Database_Locale_Key()
)
GO

Here is why UDFs are being used as JOIN predicates. The I18NID column is formed by concatenating: STRING + [ + ID + | + ID + ]

During testing of these, a simple SELECT from the view returns ~309 rows, and takes 900-1400ms to execute. If I dump the strings into another table and slap an index on it, the same select returns in 20-75ms.

So, long story short (and I hope you appreciated some of this sillyness) I want to be a good Samaritan and re-design and re-write this for the 99% of clients running this product who do not use any localization at all--end users are expected to use the [en-US] locale even when English is a 2nd/3rd language.

Since this is an unofficial hack, I am thinking of the following:

1. Create a new String table populated with a cleanly joined set of data from the original base tables
2. Index the table.
3. Create a replacement set of top-level views in the stack that include NVARCHAR and INT columns for the WKType and WKIndex columns.
4. Modify a handful of UDFs that reference these views to avoid type conversions in some join predicates (our largest audit table is 500-2,000M rows and stores an INT in a NVARCHAR(4000) column which is used to join against the WKIndex column (INT).)
5. Schemabind the views
6. Add a few indexes to the views
7. Rebuild the triggers on the views using set logic instead of cursors

Now, my actual questions:

1. Is there a best practice method to handle localized strings via a view?
2. Which alternatives exist for using a UDF as a stub? (I can write a specific VIEW for each schema owner and hard-code the language instead of relying on a variety of UDF stubs.)
3. Can these views be simply made deterministic by fully qualifying the nested UDFs and then schemabinding the view stacks?

Answer 1

Looking at the given code, we can say,

• First, this should not be a view but it should be a stored procedure, as it is not just reading from a table, but it uses UDFs.

• Second, the UDF should not be called frequently for the same column. Here, it's called once in the select

  ,dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex, N'WKS') AS I18NID 
  

  and Second time for joining

  .IETFLangCode = dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex, N'WKS')
  

One can generate values in a temporary table or use a CTE (Common Table Expression) to get those values in the first place before the join takes place.

I have generated a sample USP that will provide some improvements:

CREATE PROCEDURE usp_UserWKStringI18N
AS
BEGIN
    -- Do operation using UDF 
    SELECT b.WKType
        ,b.WKIndex
        ,dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex, N'WKS') AS I18NID
        ,dbo.UserI18N_Session_Locale_Key() AS IETFSessionLangCode
        ,dbo.UserI18N_Database_Locale_Key() AS IETFDatabaseLangCode
    INTO #tempTable
    FROM UserWKStringBASE b;

    -- Now final Select
    SELECT b.WKType
        ,b.WKIndex
        ,CASE 
            WHEN ISNULL(il.I18NID, N'') = N''
                THEN id.I18NString
            ELSE il.I18nString
            END AS WKString
        ,CASE 
            WHEN ISNULL(il.I18NID, N'') = N''
                THEN id.IETFLangCode
            ELSE il.IETFLangCode
            END AS IETFLangCode
        ,b.I18NID
        ,b.IETFSessionLangCode
        ,b.IETFDatabaseLangCode
    FROM #tempTable b
    LEFT OUTER JOIN User3StringI18N il
        ON il.I18NID = b.I18NID
            AND il.IETFLangCode = b.IETFSessionLangCode
    LEFT OUTER JOIN User3StringI18N id
        ON id.I18NID = b.I18NID
            AND id.IETFLangCode = b.IETFDatabaseLangCode
END

Please try this