SQL Server – How to Denormalize Data Efficiently

sql server

I've been working at a company for a while now creating Web Applications with their own databases which I have been comfortable with.

They've now asked me to rewrite some of the financial applications in the company which are built upon some legacy SQL databases. These databases have been designed I assume quite well as I can see how performant they are but they just dont lend themselves well for application development as they have been highly normalized.

In general I use ORM's (Object Relationial Mapper – builds entity models in the application based on db objects) to build the data model in my applications which expects tables to be setup in a certain way. However, the design of the database in these legacy databases all follow a certain pattern as explained below:

Generally each entity has 3 tables (lets use Job as an example). I have omitted some of the fields and attributes just to make it easier to read.

CREATE TABLE [dbo].[Jobs](
     [ID] [int] IDENTITY(1,1) NOT NULL,
     [JobId] [nvarchar](10) NOT NULL,
     [Active] [int] NOT NULL
)
CREATE TABLE [dbo].[JobFieldTypes](
     [JobFieldTypeId] [int] NOT NULL,
     [Description] [nvarchar](50) NOT NULL,
     [FieldDataType] [int] NOT NULL,
     [Active] [int] NOT NULL
)
CREATE TABLE [dbo].[JobFields](
     [Jobid] [nvarchar](10) NOT NULL,
     [JobFieldTypeid] [int] NOT NULL,
     [ValueString] [nvarchar](200) NULL,
     [ValueInt] [int] NULL,
     [ValueFloat] [float] NULL,
     [ValueDate] [datetime] NULL,
     [Active] [int] NOT NULL,
     [ID] [int] IDENTITY(1,1) NOT NULL
)

Rather than having the 1 table for Job with multiple columns holding data. There are 3 tables

  1. Jobs – Base table holds the JobId and if it is active or not
  2. JobFieldTypes – This describes the column information for the table. Description is the column name and FieldDataType describes which field the data is stored in the JobFields table eg. If the FieldDataType = 1 THEN the data is stored in the ValueString field in JobFields table.
  3. JobFields – This holds all the data for the Job table it holds the data for each column in one of the four value fields i.e. ValueString, ValueInt, ValueFloat, ValueData as noted in point 2 this is determined by the FieldDataType value in JobFieldTypes

My first question is – Is there a name for this pattern/strategy? If so what is it..I would like to do more reading of this.

As I've said before the ORM likes to have slightly better structured data than this. So I've written a script that creates a sql query to create an object better stuctured to ORM's.

It is as follows:

SELECT b.JobId
,t1.ValueString as [Name] 
,t2.ValueString as [Description] 
,t3.ValueString as [Unused1] 
,t4.ValueString as [Unused2] 
,t5.ValueDate as [Start Date] 
,t6.ValueDate as [Completion Date] 
,t7.ValueString as [Client] 
,t8.ValueString as [Country] 
,t9.ValueString as [Location] 
,t10.ValueString as [Category of Work] 
,t11.ValueString as [Type of Service] 
,t12.ValueFloat as [Estimated Revenues] 
,t13.ValueString as [Actual Value] 
,t15.ValueString as [Site] 
,t16.ValueString as [Purchase Order] 
,t17.ValueString as [Supervisor] 
,t18.ValueString as [Completed] 
,t19.ValueString as [Alliance] 
,t20.ValueString as [JCADJ] 
,t21.ValueString as [Contract Number] 
,t22.ValueString as [Invoice Basis] 
,t23.ValueString as [Invoicing Terms] 
,t24.ValueString as [Quote Number] 
,t25.ValueString as [Quote Provided] 
,t26.ValueString as [Quote Details] 
,t27.ValueString as [Contact] 
,t28.ValueString as [Include in Schedule] 
,t29.ValueString as [Major Project] 
,t30.ValueString as [Job Category] 
,t33.ValueString as [Quoted By] 
,t34.ValueInt as [Supervisor Staff] 
,t35.ValueInt as [Chemical Staff] 
,t36.ValueInt as [Mechanical Staff] 
,t37.ValueInt as [Catalyst Staff]
,t38.ValueInt as [Labour Staff] 
,t39.ValueInt as [Other Staff] 
,t40.ValueString as [Ongoing Job] 
,t41.ValueString as [Timesheet Only] 
,t42.ValueString as [Bookings Only] 
,t43.ValueInt as [Project Manager] 
,t44.ValueInt as [Safety Advisor] 
,t45.ValueInt as [Administrator(s)] 
,t46.ValueInt as [HPW] 
,t47.ValueInt as [Rope Access] 
,t48.ValueInt as [Painter] 
,t49.ValueInt as [Boiler Operator] 
,t50.ValueInt as [Pigging Tech] 
,t51.ValueString as [Estimated Duration] 
,t52.ValueString as [Offshore] 
,t53.ValueString as [Employee Planning Status] 
,t54.ValueString as [Operations Manager] 
,t55.ValueString as [Sales Person] 
,t56.ValueFloat as [Estimated Margin] 
,t62.ValueString as [Included in Budget] 
,t63.ValueFloat as [Budgeted Value] 
,t64.ValueString as [Budget Item] 
,t65.ValueInt as [Superintendent] 
,t66.ValueInt as [Project Manager] 
,t67.ValueInt as [Welder] 
,t68.ValueInt as [Project Planner] 
,t69.ValueInt as [Safety Manager] 
,t70.ValueInt as [Catalyst Foreman] 
,t71.ValueInt as [Medical Superintendant] 
,t72.ValueInt as [Medical Supervisor] 
,t73.ValueInt as [Medical Foreman] 
,t74.ValueInt as [Machine Operator] 
,t75.ValueInt as [Dense Loading Technician] 
,t76.ValueInt as [QAQC] 
,t77.ValueInt as [Forklift Operator] 
,t78.ValueString as [Administrator] 
,t79.ValueInt as [Inert Catalyst Technician] 
,t80.ValueInt as [Acid Work Supervisor] 
,t81.ValueInt as [Acid Work Technician] 
,t82.ValueString as [Payroll Labour Code] 
,t83.ValueDate as [Pick List Date] 
,t84.ValueDate as [Equipment Inspection Date] 
,t85.ValueDate as [Equipment Mobilisation Date] 
,t86.ValueDate as [Equipment Demobilisation Date]
FROM Jobs b
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=1) as t1 ON t1.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=2) as t2 ON t2.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=3) as t3 ON t3.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=4) as t4 ON t4.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=5) as t5 ON t5.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=6) as t6 ON t6.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=7) as t7 ON t7.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=8) as t8 ON t8.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=9) as t9 ON t9.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=10) as t10 ON t10.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=11) as t11 ON t11.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=12) as t12 ON t12.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=13) as t13 ON t13.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=15) as t15 ON t15.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=16) as t16 ON t16.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=17) as t17 ON t17.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=18) as t18 ON t18.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=19) as t19 ON t19.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=20) as t20 ON t20.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=21) as t21 ON t21.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=22) as t22 ON t22.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=23) as t23 ON t23.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=24) as t24 ON t24.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=25) as t25 ON t25.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=26) as t26 ON t26.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=27) as t27 ON t27.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=28) as t28 ON t28.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=29) as t29 ON t29.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=30) as t30 ON t30.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=33) as t33 ON t33.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=34) as t34 ON t34.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=35) as t35 ON t35.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=36) as t36 ON t36.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=37) as t37 ON t37.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=38) as t38 ON t38.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=39) as t39 ON t39.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=40) as t40 ON t40.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=41) as t41 ON t41.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=42) as t42 ON t42.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=43) as t43 ON t43.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=44) as t44 ON t44.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=45) as t45 ON t45.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=46) as t46 ON t46.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=47) as t47 ON t47.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=48) as t48 ON t48.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=49) as t49 ON t49.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=50) as t50 ON t50.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=51) as t51 ON t51.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=52) as t52 ON t52.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=53) as t53 ON t53.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=54) as t54 ON t54.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=55) as t55 ON t55.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=56) as t56 ON t56.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=62) as t62 ON t62.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=63) as t63 ON t63.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=64) as t64 ON t64.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=65) as t65 ON t65.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=66) as t66 ON t66.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=67) as t67 ON t67.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=68) as t68 ON t68.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=69) as t69 ON t69.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=70) as t70 ON t70.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=71) as t71 ON t71.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=72) as t72 ON t72.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=73) as t73 ON t73.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=74) as t74 ON t74.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=75) as t75 ON t75.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=76) as t76 ON t76.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=77) as t77 ON t77.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=78) as t78 ON t78.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=79) as t79 ON t79.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=80) as t80 ON t80.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=81) as t81 ON t81.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=82) as t82 ON t82.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=83) as t83 ON t83.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=84) as t84 ON t84.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=85) as t85 ON t85.JobId=b.JobId
LEFT JOIN (SELECT * FROM JobFields WHERE Active=1 AND JobFieldTypeId=86) as t86 ON t86.JobId=b.JobId
WHERE b.Active=1 and b.JobId = 'j6236'

So this works but the problem is that it takes 20+ seconds to load one record. I suspect this is because of the 86 joins it needs to make to get this data…

Question 2 – Can someone suggest a better way of doing this? I know I could probably write a stored procedure that would produce this a lot faster but again the ORM prefers to model off a table/view and not a SP. I also know that I could batch this up and run over night etc but we would like to have this data realtime.

Best Answer

To answer your second question, you can try to rewrite the select query like this:

SELECT
    j.[JobID]
    , j.[Active]
    , jf.[JobFieldTypeId]
    , jf.[ValueString]
    , jf.[ValueInt]
    , jf.[ValueFloat]
    , jf.[ValueDate]
    , jf.[ID]
INTO 
    #Temp
FROM
    [dbo].[Jobs] AS j
INNER JOIN
    [dbo].[JobFields] AS jf
ON 
    jf.[Jobid] = j.[jobid]
WHERE
    j.[Jobid] = 1   
AND j.[Active] = 1
AND jf.[Active] = 1;

CREATE NONCLUSTERED INDEX [IX_temp] 
ON #temp (JobFieldTypeId) 
INCLUDE (ValueString,ValueInt,ValueFloat,ValueDate);

SELECT
    [Name] = (SELECT ValueString FROM #temp WHERE JobFieldTypeId = 1)
    , [Description] = (SELECT ValueString FROM #temp WHERE JobFieldTypeId = 2)
    , [StartDate] = (SELECT ValueDate FROM #temp WHERE JobFieldTypeId = 3)
    .......................................................................
    , [Equipment Demobilisation Date] = (SELECT ValueDate FROM #temp WHERE JobFieldTypeId = 86);

DROP TABLE #temp;

You should see a big performance improvement because all the necessary data is fetched once at the beginning.Good luck!

Related Question