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:
- Nested views are non-deterministic, so we cannot index them
- Each view references multiple
UDFs to build the strings
- Each UDF contains nested
UDFs to get the ISO codes for localized languages
- Views in the stack are using additional string builders returned from
- Each view stack is treated as a table, meaning that there are
DELETEtriggers on each to write to the underlying tables
- These triggers on the views use
EXECstored procedures which reference more of these string building
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:
- Create a new String table populated with a cleanly joined set of data from the original base tables
- Index the table.
- Create a replacement set of top-level views in the stack that include
INTcolumns for the
- 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
NVARCHAR(4000)column which is used to join against the
- Schemabind the views
- Add a few indexes to the views
- Rebuild the triggers on the views using set logic instead of cursors
Now, my actual questions:
- Is there a best practice method to handle localized strings via a view?
- Which alternatives exist for using a
UDFas a stub? (I can write a specific
VIEWfor each schema owner and hard-code the language instead of relying on a variety of
- Can these views be simply made deterministic by fully qualifying the nested
UDFs and then schemabinding the view stacks?