I inherited a large EAV (TRANSLATE_CODE_VALUE) that contained descriptions matched to a series of single-character codes across dozens of columns in several primary tables. The schema (and primary key) was ColumnName, Code, Description. One table had to join to the descriptions table nearly 20 times and performance obviously suffered. The surprisingly satisfactory solution was to write a procedure to convert all of those codes into a set of inline table valued functions, each consisting of a single CASE statement (see below).

It only takes a second or so to regenerate the entire set when the descriptions table is changed, and the query plan benefitted greatly from replacing joins to the EAV with CROSS APPLYS to the newly generated functions. Essentially replacing a lot of I/O with simple compute scalar operations.

This solution also preserves the only rational function I've ever had to concede for an EAV, NVP table, which is to keep a common repository of all definitions in one place for maintenance.

Below is a sample function that was created automatically by the translate procedure. The text description is also automatically generated to warn off future generations from making changes directly to the function rather than continuing to maintain the TRANSLATE_CODE_VALUE table.

ALTER FUNCTION [dbo].[tfn_TRANSLATE_APPR_ASSIGNMENT_TYPE] (@input varchar(10))

----------------------------------------------------------------------------------------------------

-- This function was automatically generated by dbo.stGenerateTranslateFunctions on

-- Apr 16 2014 11:37:20:807AM (Pacific). All codes and their translated long values

-- come from [dbo].[TRANSLATE_CODE_VALUE].

----------------------------------------------------------------------------------------------------

RETURNS TABLE

AS

RETURN

(SELECT CASE ltrim(rtrim(@input))

WHEN 'O' THEN 'Other'

WHEN 'P' THEN 'Purchase'

WHEN 'R' THEN 'Rental'

ELSE '??'

END as output_text_long);

Admittedly, this function could be improved on for cases where dozens of codes existed for a single column. Nested CASE statements could emulate a binary search to minimize the execution time of functions with a truly large number of codes. However the performance has been more than adequate so far without that modification.

Edited to add that yes I know this thread was almost five years old when I added this.