How do I create a view off a property value table in SQL

Question

I have a table in SQL that has four columns. Two of these are property and value columns that store performance metrics for one of our servers. We're constantly coming up with new performance metrics and we didn't want to keep redesigning our schema, so thats why we designed the table this way.

Trouble is, when I create a view to look at the table as if its properly normalized, I get a query that just screams "Oh my god this is crap code" since it involves a table joined to itself 12 times. Here is the query I used for the view.

Basically, it feels like I'm doing something really wrong, but I can't figure out a better way to solve the problem.

SELECT 
    astats.AQTORStatsID, 
    astats.ServerName, 
    astats.Remarks, 
    astats.StatsBeginDateTime, 
    astats.StatsEndDateTime,
    asi1.AQTORStatValue as 'QtCPU_Average',
    asi2.AQTORStatValue as 'QtCPU_TopQuintile',
    asi3.AQTORStatValue as 'QtCPU_TopOnePercent',
    asi4.AQTORStatValue as 'QtCl_Average',
    asi5.AQTORStatValue as 'QtCl_TopQuintile',
    asi6.AQTORStatValue as 'QtCl_TopOnePercent',
    asi7.AQTORStatValue as 'UpdPrcStd_Average',
    asi8.AQTORStatValue as 'UpdPrcStd_TopQuintile',
    asi9.AQTORStatValue as 'UpdPrcStd_TopOnePercent',
    asi10.AQTORStatValue as 'RcRsUPr_Average',
    asi11.AQTORStatValue as 'RcRsUPr_TopQuintile',
    asi12.AQTORStatValue as 'RcRsUPr_TopOnePercent'
FROM 
    tb_rAQTORStatsItem asi1
    INNER JOIN tb_rAQTORStatsItem asi2 ON asi1.AQTORStatsID = asi2.AQTORStatsID
    INNER JOIN tb_rAQTORStatsItem asi3 ON asi2.AQTORStatsID = asi3.AQTORStatsID
    INNER JOIN tb_rAQTORStatsItem asi4 ON asi3.AQTORStatsID = asi4.AQTORStatsID
    INNER JOIN tb_rAQTORStatsItem asi5 ON asi4.AQTORStatsID = asi5.AQTORStatsID
    INNER JOIN tb_rAQTORStatsItem asi6 ON asi5.AQTORStatsID = asi6.AQTORStatsID
    INNER JOIN tb_rAQTORStatsItem asi7 ON asi6.AQTORStatsID = asi7.AQTORStatsID
    INNER JOIN tb_rAQTORStatsItem asi8 ON asi7.AQTORStatsID = asi8.AQTORStatsID
    INNER JOIN tb_rAQTORStatsItem asi9 ON asi8.AQTORStatsID = asi9.AQTORStatsID
    INNER JOIN tb_rAQTORStatsItem asi10 ON asi9.AQTORStatsID = asi10.AQTORStatsID
    INNER JOIN tb_rAQTORStatsItem asi11 ON asi10.AQTORStatsID = asi11.AQTORStatsID
    INNER JOIN tb_rAQTORStatsItem asi12 ON asi11.AQTORStatsID = asi12.AQTORStatsID
    INNER JOIN tb_dAQTORStats astats on asi12.AQTORStatsID = astats.AQTORStatsID
WHERE 
    asi1.AQTORStatName = 'QtCPU_Average'
AND asi2.AQTORStatName = 'QtCPU_TopQuintile'
AND asi3.AQTORStatName = 'QtCPU_TopOnePercent'
AND asi4.AQTORStatName = 'QtCl_Average'
AND asi5.AQTORStatName = 'QtCl_TopQuintile'
AND asi6.AQTORStatName = 'QtCl_TopOnePercent'
AND asi7.AQTORStatName = 'UpdPrcStd_Average'
AND asi8.AQTORStatName = 'UpdPrcStd_TopQuintile'
AND asi9.AQTORStatName = 'UpdPrcStd_TopOnePercent'
AND asi10.AQTORStatName = 'RcRsUPr_Average'
AND asi11.AQTORStatName = 'RcRsUPr_TopQuintile'
AND asi12.AQTORStatName = 'RcRsUPr_TopOnePercent'

Answer 1

IMHO, this kind of code (as well as trouble designing indices) is part of the price you pay for the flexibility of the "property table" idiom -- you pays your money, and you takes your choice!-)

Answer 2

Have a table containing all the possible stats names (which I think you should have anyway, with an fkey constraint from tb_rAQTORStatsItem onto it)

Then you can have something like:

SELECT astats.QTORStatsID, astats.ServerName, astats.Remarks,
       astats.StatsBeginDateTime, astats.StatsEndDateTime,
       max(case item.AQTORStatName when 'QtCPU_Average' then AQTORStatValue end) as QtCPU_Average,
       max(case item.AQTORStatName when 'QtCPU_TopQuintile' then AQTORStatValue end) as QtCPU_TopQuintile,
       /* repeat for each statistic... */
FROM tb_dAQTORStats astats
     CROSS JOIN tb_rAQTORStatNames statnames
     LEFT JOIN tb_rAQTORStatsItem item
          ON item.AQTORStatName = statnames.AQTORStatName
             AND item.AQTORStatsID = astats.AQTORStatsID
GROUP BY astats.QTORStatsID, astats.ServerName, astats.Remarks,
         astats.StatsBeginDateTime, astats.StatsEndDateTime

Although this is still pretty ugly, at least you can cut the N-way join to a 3-way join. Yes, this is a case of flexibility and simplicity begin at odds. Adding columns is in a way a neater way to solve the problem, because the database schema will precisely match what stats you collect and what type they are: but you need to keep them in sync.

(nb syntax of query not tested, I'm assuming CROSS JOIN works in the way I expect)

Answer 3

this would probably run a lot better if you had another table to join to to get the names of the columns

Answer 4

Here's how you do it:

SELECT 
  astats.AQTORStatsID
, astats.ServerName
, astats.Remarks
, astats.StatsBeginDateTime
, astats.StatsEndDateTime

, QtCPU_Average           = max(case when asi.AQTORStatName = 'QtCPU_Average'           then asi.AQTORStatValue end)
, QtCPU_TopQuintile       = max(case when asi.AQTORStatName = 'QtCPU_TopQuintile'       then asi.AQTORStatValue end)
, QtCPU_TopOnePercent     = max(case when asi.AQTORStatName = 'QtCPU_TopOnePercent'     then asi.AQTORStatValue end)
, QtCl_Average            = max(case when asi.AQTORStatName = 'QtCl_Average'            then asi.AQTORStatValue end)
, QtCl_TopQuintile        = max(case when asi.AQTORStatName = 'QtCl_TopQuintile'        then asi.AQTORStatValue end)
, QtCl_TopOnePercent      = max(case when asi.AQTORStatName = 'QtCl_TopOnePercent'      then asi.AQTORStatValue end)
, UpdPrcStd_Average       = max(case when asi.AQTORStatName = 'UpdPrcStd_Average'       then asi.AQTORStatValue end)
, UpdPrcStd_TopQuintile   = max(case when asi.AQTORStatName = 'UpdPrcStd_TopQuintile'   then asi.AQTORStatValue end)
, UpdPrcStd_TopOnePercent = max(case when asi.AQTORStatName = 'UpdPrcStd_TopOnePercent' then asi.AQTORStatValue end)
, RcRsUPr_Average         = max(case when asi.AQTORStatName = 'RcRsUPr_Average'         then asi.AQTORStatValue end)
, RcRsUPr_TopQuintile     = max(case when asi.AQTORStatName = 'RcRsUPr_TopQuintile'     then asi.AQTORStatValue end)
, RcRsUPr_TopOnePercent   = max(case when asi.AQTORStatName = 'RcRsUPr_TopOnePercent'   then asi.AQTORStatValue end)

from tb_dAQTORStats astats
join tb_rAQTORStatsItem asi on asi.AQTORStatsID = astats.AQTORStatsID  

group by 
  astats.AQTORStatsID
, astats.ServerName
, astats.Remarks
, astats.StatsBeginDateTime
, astats.StatsEndDateTime

Notes:

1. A good text editor with column mode or rectangular edit really helps with this kind of thing. UltraEdit or Emacs come to mind. I created the above in UltraEdit in about a minute.

2. Your original query with the INNER JOINs would have dropped all readings for a given server if one type of reading was missing. Not good. This query will return one row per server for any server with at least one reading. If you want to return all servers, regardless of whether there are any readings, change the INNER JOIN to LEFT JOIN.

3. You don't need a separate table for your stat names, unless you need to enforce data integrity through a foreign key.

Answer 5

This kind of design is about halfway from relational to pure EAV (Entity-Attribute-Value) design. And yes, they have some hideous aspects for a SQL database The best solution to this problem that I have found is to use cubes and or pivot tables, instead of trying to hammer this round peg into the square hole of relational views.

Things that you can use that do fit: SSAS, Reporting Services pivot reports, even Excel pivot tables.