5

I am working on a project where many of the entities in the data model do not have a name column, but instead the "name" needs to be constructed from a concatenation of multiple columns. Initially I thought this might be a good use case for a computed column, as it would allow me to define this logic in the database and only once.

However with a fully normalized data model, the computed columns often require values from other tables. Querying other tables can be accomplished through a UDF, but I have read in several places that using UDFs in computed columns prevents parallel execution (see here).

I could use views to handle the formatting of these names, but would like to define the logic only once, and if the logic is in a view it would likely result in nesting views, which can also cause problems with performance.

I'm hoping someone has an idea for how I might define this formatting logic only once in the database without causing a performance problem.

  • 1
    I'll write a bit more up in a bit, but start here for some potential relief. – Erik Darling Nov 27 '17 at 22:28
  • Nested views don't cause performance problems. They just make it difficult for you to troubleshoot performance problems. – Joe Obbish Nov 28 '17 at 2:25
  • Unless you have some very strict - and unusual- requirements, it's okay to not have full normalization where clarity and performance would benefit. – S M Nov 29 '17 at 9:06
3

You could use an indexed view for this. Assuming that the relationships are simple (foreign key) relationships, I don't see why this would cause any issues.

Simple example, tested in dbfiddle.uk:

create table game
( game_id int not null primary key,
  game_name varchar(100) not null  
) ;

create table area
( area_id int not null primary key,
  area_name varchar(100) not null 
) ;

create table player
( player_id int not null primary key,
  player_name varchar(100) not null 
) ;
insert into game
values
  (1, 'chess'),
  (2, 'go'),
  (3, 'reversi'),
  (4, 'backgammon'),
  (5, 'hex'),
  (6, 'havannah'),
  (7, 'pacman') ;

insert into area
values
  (11, 'usa'),
  (12, 'russia'),
  (13, 'greece'),
  (14, 'uk'),
  (15, 'france'),
  (16, 'hungary'),
  (17, 'ukraine'),
  (18, 'belgium'),
  (19, 'canada'),
  (20, 'new zealand')  ;

insert into player
values
  (7, 'John'),
  (8, 'Mary'),
  (9, 'Alex'),
  (10, 'Anna'),
  (11, 'Fred'),
  (12, 'Fay') ;
23 rows affected
create table playground
( playground_id int not null identity primary key,
  game_id int not null references game,
  area_id int not null references area,
  player_id int not null  references player,
  various_stuff varchar(100) null default 'abcdefghijklmnopqrstuvwxyz-abcdefghijklmnopqrstuvwxyz-abcdefghijklmnopqrstuvwxyz',
  constraint playground_uq 
    unique (game_id, area_id, player_id)
) ;
create view dbo.play 
  (game_id, area_id, player_id, name)
WITH SCHEMABINDING 
as
select 
    pg.game_id,
    pg.area_id,
    pg.player_id,
    name = g.game_name + '-' + a.area_name + '-' + p.player_name  
from dbo.playground as pg 
  join dbo.game   as g on g.game_id   = pg.game_id 
  join dbo.area   as a on a.area_id   = pg.area_id
  join dbo.player as p on p.player_id = pg.player_id ;
 -- create an index on the view  
create unique clustered index play_cix   
    on play (game_id, area_id, player_id) ;
insert into playground
  (game_id, area_id, player_id)
select game_id, area_id, player_id
from game, area, player ;
420 rows affected
--------------------------------------------------------------------------------
-- Or use XML to see the visual representation, thanks to Justin Pealing and
-- his library: https://github.com/JustinPealing/html-query-plan
--------------------------------------------------------------------------------
set statistics xml on;

select -- top (10) 
   game_id, area_id, player_id, name 
from play 
   WITH (NOEXPAND)            -- Hint used because we are in Express edition
  ;
set statistics xml off;
game_id | area_id | player_id | name                       
------: | ------: | --------: | :--------------------------
      1 |      11 |         7 | chess-usa-John             
      1 |      11 |         8 | chess-usa-Mary             

   --- a few hundred rows omitted

  7 |      20 |        11 | pacman-new zealand-Fred    
  7 |      20 |        12 | pacman-new zealand-Fay     
| Microsoft SQL Server 2005 XML Showplan | | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.8" Build="14.0.3015.40"><BatchSequence><Batch><Statements><StmtSimple StatementText="select -- top (10) &#xa; game_id, area_id, player_id, name &#xa;from play &#xa; WITH (NOEXPAND)" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.00522548" StatementEstRows="420" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0xBA9BAED6D700FD27" QueryPlanHash="0xE0086846AA2CCE7F" CardinalityEstimationModelVersion="140"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="88"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2073864"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="15" CpuTime="15"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="420" EstimatedRowsRead="420" EstimateIO="0.00460648" EstimateCPU="0.000619" AvgRowSize="174" EstimatedTotalSubtreeCost="0.00522548" TableCardinality="420" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Column="game_id"></ColumnReference><ColumnReference Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Column="area_id"></ColumnReference><ColumnReference Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Column="player_id"></ColumnReference><ColumnReference Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Column="name"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="420" Batches="0" ActualExecutionMode="Row" ActualElapsedms="15" ActualCPUms="15" ActualScans="1" ActualLogicalReads="5" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="420" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="1" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Column="game_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Column="area_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Column="player_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Column="name"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Index="[play_cix]" IndexKind="ViewClustered" Storage="RowStore"></Object></IndexScan></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |

dbfiddle here

and the execution plan of the query that selects from the view, showing that the clustered index on the materialized view is used:

execution plan

0

I don't think there is one correct answer to this. As hinted at in some of the comments, you will almost always have some trade-off between performance and complexity/maintainability.

We had a similar case where the use of views was not an option, and we opted for a UDF in the computed column. The problem we encountered is that you can't persist the column, which is needed to index it if the function references other objects such as tables.

It seems that you are aware of the options available to you, and just need to find the best solution for you and your stakeholders.

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.