20

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:

enter image description here enter image description here enter image description here enter image description here

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?
  • 5
    Much as I appreciate the work you've put into your question with query plans and code examples - and I do feel your pain - please avoid shopping-list type questions where you ask a range of different questions on a topic. Also, most of your questions are very open-ended with lots of potentially correct answers, which is not optimal for a Q&A site like StackExchange. Have a nice evening! – Daniel Hutmacher Feb 19 '16 at 20:28
  • @Daniel, any suggestions to improve the questions? I don't have a blog to seek any help on this. – beeks Feb 19 '16 at 20:29
  • I was going to write some, but you've already improved the most obvious ones. :) More on "How to ask a good question". – Daniel Hutmacher Feb 19 '16 at 20:36
  • 5
    Try to convert the Scalar UDF to a Inline table valued UDF. Post your UDF definition as well. Also, refer to T-SQL User-Defined Functions: the good, the bad, and the ugly – Kin Shah Feb 19 '16 at 23:42
  • @Kin, thanks for the links. I'll take a good look over the weekend. – beeks Feb 20 '16 at 7:11
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

  • Hello MarmiK, thanks for taking the time to take a look at this post. This is unfortunately a view (in a series of nested views) so moving it to a stored procedure was out of the question. – beeks Feb 3 '17 at 18:35
  • well in that case we can use CTE in view as temp tables are not recommended in View. OR the rows of temp table can be generated by some stored procedure and can be called in view. – MarmiK Feb 4 '17 at 7:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.