2

I have a table that has 5 references to a list table for normalization of the first table.

So in the table 1 a row looks line other columns..., reference_1, reference_2, reference_3, reference_4, reference_5.

All of those are as FK to a list table that is simply id, display

I know I can join the same list table multiple times as list_1 on id = reference_1 and then again as list_2 on id = reference_2 and then return my rows as list_1.display, list_1.display etc...

Can it however be done more efficiently by joining the table once, and referencing the display that corresponded to the reference_x (a stand-in for whichever reference is actually used) or is the impact of doing it the obvious way as efficient as it gets.

Edit01:

DECLARE @filter_date_start DATETIME
DECLARE @filter_date_end DATETIME
DECLARE @min_time_start DATETIME
DECLARE @max_time_start DATETIME

--set time filters
SET @filter_date_start = '2014/09/01'
SET @filter_date_end = '2014/09/30'

SELECT @min_time_start = @filter_date_start
                         + Min(CONVERT(VARCHAR, [time_start], 108)),
       @max_time_start = Dateadd(second, -1, Dateadd(day, 1, @filter_date_end)
                                             + Min(CONVERT(VARCHAR, [time_start], 108)))
FROM   [list_shift]
WHERE  [value] IN (SELECT DISTINCT [shift_value]
                   FROM   [data_timekeeping]
                   WHERE  [time_start] BETWEEN @filter_date_start AND Dateadd(day, 1, @filter_date_end))
       AND ( [value] <> -1
             AND [value] <> 0 );

WITH [T1]
     AS (SELECT ( CASE
                    WHEN CONVERT(VARCHAR, [time_start], 108) < CONVERT(VARCHAR, @min_time_start, 108) THEN CONVERT(VARCHAR, Dateadd(day, -1, [time_start]), 101)
                    ELSE CONVERT(VARCHAR, [time_start], 101)
                  END ) [shift_date],
                [timekeeping_index],
                [location_value],
                [collector_value],
                [trigger_value],
                [time_start],
                [action_value],
                [tech_value],
                [ycord_value],
                [xcord_value],
                [shift_value]
         FROM   [dbo].[data_timekeeping]
         WHERE  [time_start] BETWEEN @min_time_start AND @max_time_start)
SELECT [timekeeping].[timekeeping_index]                              [timekeeping_index],
       [T2].[timekeeping_index]                                       [next_index],
       [timekeeping].[shift_date]                                     [shift_date],
       [timekeeping].[location_value]                                 [location_value],
       [timekeeping].[collector_value]                                [collector_value],
       [collector_list].[display]                                     [collector_display],
       [collector_class_list_1].[display]                             [collector_class_1],
       [collector_class_list_2].[display]                             [collector_class_2],
       [collector_class_list_3].[display]                             [collector_class_3],
       [collector_class_list_4].[display]                             [collector_class_4],
       [collector_class_list_5].[display]                             [collector_class_5],
       [timekeeping].[trigger_value]                                  [trigger_value],
       [timekeeping].[time_start]                                     [time_start],
       [T2].[time_start]                                              [time_end],
       Datediff(SECOND, [timekeeping].[time_start], [T2].[time_start])[elapsed],
       [timekeeping].[action_value]                                   [action_value],
       [action_list].[display]                                        [action_display],
       [action_class_list_1].[display]                                [action_class_1],
       [action_class_list_2].[display]                                [action_class_2],
       [action_class_list_3].[display]                                [action_class_3],
       [action_class_list_4].[display]                                [action_class_4],
       [action_class_list_5].[display]                                [action_class_5],
       [timekeeping].[tech_value]                                     [tech_value],
       [tech_list].[display]                                          [tech_display],
       [tech_class_list_1].[display]                                  [tech_class_1],
       [tech_class_list_2].[display]                                  [tech_class_2],
       [tech_class_list_3].[display]                                  [tech_class_3],
       [tech_class_list_4].[display]                                  [tech_class_4],
       [tech_class_list_5].[display]                                  [tech_class_5],
       [timekeeping].[ycord_value]                                    [ycord_value],
       [ycord_list].[display]                                         [ycord_display],
       [ycord_class_list_1].[display]                                 [ycord_class_1],
       [ycord_class_list_2].[display]                                 [ycord_class_2],
       [ycord_class_list_3].[display]                                 [ycord_class_3],
       [ycord_class_list_4].[display]                                 [ycord_class_4],
       [ycord_class_list_5].[display]                                 [ycord_class_5],
       [timekeeping].[xcord_value]                                    [xcord_value],
       [xcord_list].[display]                                         [xcord_display],
       [xcord_class_list_1].[display]                                 [xcord_class_1],
       [xcord_class_list_2].[display]                                 [xcord_class_2],
       [xcord_class_list_3].[display]                                 [xcord_class_3],
       [xcord_class_list_4].[display]                                 [xcord_class_4],
       [xcord_class_list_5].[display]                                 [xcord_class_5],
       [timekeeping].[shift_value]                                    [shift_value],
       [shift_list].[display]                                         [shift_display],
       [shift_class_list_1].[display]                                 [shift_class_1],
       [shift_class_list_2].[display]                                 [shift_class_2],
       [shift_class_list_3].[display]                                 [shift_class_3],
       [shift_class_list_4].[display]                                 [shift_class_4],
       [shift_class_list_5].[display]                                 [shift_class_5],
       Isnull([sub_times].[count_sub_time], 0)                        [count_sub_time],
       Isnull([sub_times].[value001], 0)                              [value001],
       Isnull([sub_times].[value002], 0)                              [value002],
       Isnull([sub_times].[value003], 0)                              [value003],
       Isnull([sub_times].[value004], 0)                              [value004],
       Isnull([sub_times].[value005], 0)                              [value005],
       Isnull([sub_times].[value006], 0)                              [value006],
       Isnull([sub_times].[value007], 0)                              [value007],
       Isnull([sub_times].[value008], 0)                              [value008],
       Isnull([sub_times].[value009], 0)                              [value009],
       Isnull([sub_times].[value010], 0)                              [value010],
       Isnull([sub_times].[value011], 0)                              [value011],
       Isnull([sub_times].[value012], 0)                              [value012],
       Isnull([sub_times].[value013], 0)                              [value013],
       Isnull([sub_times].[value014], 0)                              [value014],
       Isnull([sub_times].[value015], 0)                              [value015],
       Isnull([sub_times].[value016], 0)                              [value016],
       Isnull([sub_times].[value017], 0)                              [value017],
       Isnull([sub_times].[value018], 0)                              [value018],
       Isnull([sub_times].[value019], 0)                              [value019],
       Isnull([sub_times].[value020], 0)                              [value020],
       Isnull([sub_times].[value021], 0)                              [value021],
       Isnull([sub_times].[value022], 0)                              [value022],
       Isnull([sub_times].[value023], 0)                              [value023],
       Isnull([sub_times].[value024], 0)                              [value024],
       Isnull([sub_times].[value025], 0)                              [value025],
       Isnull([sub_times].[value025], 0)                              [value025],
       Isnull([sub_times].[value027], 0)                              [value027],
       Isnull([sub_times].[value028], 0)                              [value028],
       Isnull([sub_times].[value029], 0)                              [value029],
       Isnull([sub_times].[value030], 0)                              [value030],
       Isnull([sub_times].[value031], 0)                              [value031],
       Isnull([sub_times].[value032], 0)                              [value032],
       Isnull([motion].[count_motion], 0)                             [count_motion]
FROM   [T1][timekeeping]
       LEFT JOIN [T1][T2]
              ON [T2].[location_value] = [timekeeping].[location_value]
                 AND [T2].[collector_value] = [timekeeping].[collector_value]
                 AND [T2].[timekeeping_index] = (SELECT Min([timekeeping_index])
                                                 FROM   [T1]
                                                 WHERE  [location_value] = [timekeeping].[location_value]
                                                        AND [collector_value] = [timekeeping].[collector_value]
                                                        AND [timekeeping_index] > [timekeeping].[timekeeping_index])
       LEFT JOIN [dbo].[list_collector][collector_list]
              ON [collector_list].[value] = [timekeeping].[collector_value]
       LEFT JOIN [dbo].[list_collector_class][collector_class_list_1]
              ON [collector_class_list_1].[value] = [collector_list].[class_1]
       LEFT JOIN [dbo].[list_collector_class][collector_class_list_2]
              ON [collector_class_list_2].[value] = [collector_list].[class_2]
       LEFT JOIN [dbo].[list_collector_class][collector_class_list_3]
              ON [collector_class_list_3].[value] = [collector_list].[class_3]
       LEFT JOIN [dbo].[list_collector_class][collector_class_list_4]
              ON [collector_class_list_4].[value] = [collector_list].[class_4]
       LEFT JOIN [dbo].[list_collector_class][collector_class_list_5]
              ON [collector_class_list_5].[value] = [collector_list].[class_5]
       LEFT JOIN [dbo].[list_action][action_list]
              ON [action_list].[value] = [timekeeping].[action_value]
       LEFT JOIN [dbo].[list_action_class][action_class_list_1]
              ON [action_class_list_1].[value] = [action_list].[class_1]
       LEFT JOIN [dbo].[list_action_class][action_class_list_2]
              ON [action_class_list_2].[value] = [action_list].[class_2]
       LEFT JOIN [dbo].[list_action_class][action_class_list_3]
              ON [action_class_list_3].[value] = [action_list].[class_3]
       LEFT JOIN [dbo].[list_action_class][action_class_list_4]
              ON [action_class_list_4].[value] = [action_list].[class_4]
       LEFT JOIN [dbo].[list_action_class][action_class_list_5]
              ON [action_class_list_5].[value] = [action_list].[class_5]
       LEFT JOIN [dbo].[list_tech][tech_list]
              ON [tech_list].[value] = [timekeeping].[tech_value]
       LEFT JOIN [dbo].[list_tech_class][tech_class_list_1]
              ON [tech_class_list_1].[value] = [tech_list].[class_1]
       LEFT JOIN [dbo].[list_tech_class][tech_class_list_2]
              ON [tech_class_list_2].[value] = [tech_list].[class_2]
       LEFT JOIN [dbo].[list_tech_class][tech_class_list_3]
              ON [tech_class_list_3].[value] = [tech_list].[class_3]
       LEFT JOIN [dbo].[list_tech_class][tech_class_list_4]
              ON [tech_class_list_4].[value] = [tech_list].[class_4]
       LEFT JOIN [dbo].[list_tech_class][tech_class_list_5]
              ON [tech_class_list_5].[value] = [tech_list].[class_5]
       LEFT JOIN [dbo].[list_ycord][ycord_list]
              ON [ycord_list].[value] = [timekeeping].[ycord_value]
       LEFT JOIN [dbo].[list_ycord_class][ycord_class_list_1]
              ON [ycord_class_list_1].[value] = [ycord_list].[class_1]
       LEFT JOIN [dbo].[list_ycord_class][ycord_class_list_2]
              ON [ycord_class_list_2].[value] = [ycord_list].[class_2]
       LEFT JOIN [dbo].[list_ycord_class][ycord_class_list_3]
              ON [ycord_class_list_3].[value] = [ycord_list].[class_3]
       LEFT JOIN [dbo].[list_ycord_class][ycord_class_list_4]
              ON [ycord_class_list_4].[value] = [ycord_list].[class_4]
       LEFT JOIN [dbo].[list_ycord_class][ycord_class_list_5]
              ON [ycord_class_list_5].[value] = [ycord_list].[class_5]
       LEFT JOIN [dbo].[list_xcord][xcord_list]
              ON [xcord_list].[value] = [timekeeping].[xcord_value]
       LEFT JOIN [dbo].[list_xcord_class][xcord_class_list_1]
              ON [xcord_class_list_1].[value] = [xcord_list].[class_1]
       LEFT JOIN [dbo].[list_xcord_class][xcord_class_list_2]
              ON [xcord_class_list_2].[value] = [xcord_list].[class_2]
       LEFT JOIN [dbo].[list_xcord_class][xcord_class_list_3]
              ON [xcord_class_list_3].[value] = [xcord_list].[class_3]
       LEFT JOIN [dbo].[list_xcord_class][xcord_class_list_4]
              ON [xcord_class_list_4].[value] = [xcord_list].[class_4]
       LEFT JOIN [dbo].[list_xcord_class][xcord_class_list_5]
              ON [xcord_class_list_5].[value] = [xcord_list].[class_5]
       LEFT JOIN [dbo].[list_shift][shift_list]
              ON [shift_list].[value] = [timekeeping].[shift_value]
       LEFT JOIN [dbo].[list_shift_class][shift_class_list_1]
              ON [shift_class_list_1].[value] = [shift_list].[class_1]
       LEFT JOIN [dbo].[list_shift_class][shift_class_list_2]
              ON [shift_class_list_2].[value] = [shift_list].[class_2]
       LEFT JOIN [dbo].[list_shift_class][shift_class_list_3]
              ON [shift_class_list_3].[value] = [shift_list].[class_3]
       LEFT JOIN [dbo].[list_shift_class][shift_class_list_4]
              ON [shift_class_list_4].[value] = [shift_list].[class_4]
       LEFT JOIN [dbo].[list_shift_class][shift_class_list_5]
              ON [shift_class_list_5].[value] = [shift_list].[class_5]
       --End basic timekeeping, start sub_time data
       --commenting out any value not selected above will increase query efficiency
       LEFT JOIN (SELECT [timekeeping_index],
                         [location_value],
                         [collector_value],
                         CONVERT(FLOAT, Count(*))                                                                 [count_sub_time],
                         Sum(CONVERT(FLOAT, [value001]))                                                          [value001],
                         Sum(CONVERT(FLOAT, [value002]))                                                          [value002],
                         Sum(CONVERT(FLOAT, [value003]))                                                          [value003],
                         Sum(CONVERT(FLOAT, [value004]))                                                          [value004],
                         Sum(CONVERT(FLOAT, [value005]))                                                          [value005],
                         Sum(CONVERT(FLOAT, ( [value005] / [dbo].Getfactor([time_start], [collector_value]) )))   [value006],
                         Sum(CONVERT(FLOAT, [value007]))                                                          [value007],
                         Sum(CONVERT(FLOAT, CASE
                                              WHEN [height_loadbucket_start] > 0 THEN 0
                                              ELSE 1
                                            END))                                                                 [value008],
                         Sum(CONVERT(FLOAT, CASE
                                              WHEN [height_loadbucket_start] > 0 THEN 1
                                              ELSE 0
                                            END))                                                                 [value009],
                         Sum(CONVERT(FLOAT, [value010]))                                                          [value010],
                         Sum(CONVERT(FLOAT, [value011]))                                                          [value011],
                         Sum(CONVERT(FLOAT, [value012]))                                                          [value012],
                         Sum(CONVERT(FLOAT, [value013]))                                                          [value013],
                         Sum(CONVERT(FLOAT, [value014]))                                                          [value014],
                         Sum(CONVERT(FLOAT, CASE
                                              WHEN [height_loadbucket_start] > 0 THEN [height_loadbucket_start]
                                              ELSE 0
                                            END))                                                                 [value015],
                         Sum(CONVERT(FLOAT, CASE
                                              WHEN [height_loadbucket_start] > 0 THEN 0
                                              ELSE [height_loadbucket_start]
                                            END))                                                                 [value016],
                         Sum(CONVERT(FLOAT, Sqrt(Square([value022] - [value023])
                                                 + Square([height_loadbucket_start] - [height_loadbucket_end])))) [value017],
                         Sum(CONVERT(FLOAT, [value014] - [height_loadbucket_end]))                                [value018],
                         Sum(CONVERT(FLOAT, [value019]))                                                          [value019],
                         Sum(CONVERT(FLOAT, [value020]))                                                          [value020],
                         Sum(CONVERT(FLOAT, [value021]))                                                          [value021],
                         Sum(CONVERT(FLOAT, [value022]))                                                          [value022],
                         Sum(CONVERT(FLOAT, [value023]))                                                          [value023],
                         Sum(CONVERT(FLOAT, [value024]))                                                          [value024],
                         Sum(CONVERT(FLOAT, [value025]))                                                          [value025],
                         Sum(CONVERT(FLOAT, [value026]))                                                          [value026],
                         Sum(CONVERT(FLOAT, [value027]))                                                          [value027],
                         Sum(CONVERT(FLOAT, [value028]))                                                          [value028],
                         Sum(CONVERT(FLOAT, [value029]))                                                          [value029],
                         Sum(CONVERT(FLOAT, [value030]))                                                          [value030],
                         Sum(CONVERT(FLOAT, [value031]))                                                          [value031],
                         Sum(CONVERT(FLOAT, [value032]))                                                          [value032]
                  FROM   [dbo].[data_dragline_sub_time]
                  WHERE  [time_start] BETWEEN @min_time_start AND @max_time_start
                         AND [trigger_value] <> -13 -- (-13) "The data is invalid" produced in the database by attempting contiguous duplicate triggers.
                  GROUP  BY [location_value],
                            [collector_value],
                            [timekeeping_index])[sub_times]
              ON ( [sub_times].[location_value] = [timekeeping].[location_value]
                   AND [sub_times].[timekeeping_index] = [timekeeping].[timekeeping_index]
                   AND [sub_times].[collector_value] = [timekeeping].[collector_value] )
       LEFT JOIN (SELECT [location_value],
                         [collector_value],
                         [timekeeping_index],
                         Count(*)[count_motion]
                  FROM   [dbo].[data_motion]
                  WHERE  [time_start] BETWEEN @min_time_start AND @max_time_start
                         AND [trigger_value] <> -13 -- (-13) "The data is invalid" produced in the database by attempting contiguous duplicate triggers.
                  GROUP  BY [location_value],
                            [collector_value],
                            [timekeeping_index])[motion]
              ON [motion].[timekeeping_index] = [timekeeping].[timekeeping_index]
                 AND [motion].collector_value = [timekeeping].[collector_value]
                 AND [motion].[location_value] = [timekeeping].[location_value]
ORDER  BY [timekeeping].[collector_value],
          [timekeeping].[time_start] 

Along with an explanation of what is occurring.... (query has been sterilized of system identity, the actual query contains descriptive names)

The system keeps time by noting changes that are either provided by the technician by way of an HMI application, or generated internally in the system. It factors end times of all events as being the start of something new, this is where there is the self join for the time_end field. All additional data is FK'd to the existing timekeeping row at the time of insert internally in the database. So using this I may divide a query into time frames, and detail all things occurring in the range relating to that action. The system keeps accurate time to a +/- 1 second per 24 hours this way.

The 'class_x' extensions, and the large amount of join statements is because for per site customization without having to change core system code, for instance...

I can use they system configuration manager to add a class_x "Grouping" level to any of the levels of system input, so if a location wanted to group an input into any logical group not part of the system inherently such as "Technicians with 5+ years experience" or "actions of foo type" so through that process I can dimension data with more granularity, however starting with an a-la-carte style base query that delivers all of that information. (now we have edited out the ones where we KNOW that it will not be used where we can, but some locations have actually requested MORE be added!

The float conversions are to prevent integer overflows as some of these values get large, none of the individual values exceed type int, but a years worth of data looked at the wrong way easily can.

Short story made long, if there is a reasonable way of doing this more efficiently, then it could potentially reduce 30 joins to 6. I hope that explains it in more clarity.

3
  • 1
    I'm pretty sure the obvious method (multiple joins to the list table) is going to be the most efficient method. Oct 3, 2014 at 19:11
  • What version of SQL-Server do you have? Oct 7, 2014 at 14:28
  • It depends on the deployment, as does the OS although I do not believe OS affects this much. They range from 2000 to 2012 full to msde/express. I do TRY to enforce a 2012 express instance for all of these instances, but corporate IT people are a funny lot, and often insist on Freudian disputes to no end. So I work with what I have. Technically speaking though MSDE2k to current versions is my target, one unfortunately I do not have complete control over... In an industrial setting one often HAS to comply with 10+yo tech, it is just the nature of the beast.
    – Sabre
    Oct 7, 2014 at 14:40

3 Answers 3

2

Alternative - joining once on [list].[id] IN (reference_1, reference_2,...) and then pivoting results may give your better execution plan, but you need to check it. In some cases multiple joins worked faster for me, in others - one join outperformed multiple...

2

t is not always accurate to the business requirements to unpivot the table as you propose. For example, in a tournament scheduling database the table for Game will always have two distinct FK's to Team, one labelled HomeTeam and the other labelled VisitingTeam. Unpivoting this eliminates the business requirement that a Game is always between exactly two teams.

Another example is the case of a database for an online meeting scheduler, where each meeting typically has a single Host and any number of participants. It wold be appropriate to embed the Host FK to the Participant in the main meeting table, with all non-host participants listed in a detail table. If this were an appropriate model for your business requirements then all but one of your reference_n fields would be pivoted out to achieve proper normalization.

So, in the absence of the relevant business requirements, we are left wondering whether it is appropriate from a business perspective to unpivot as described.

However, let's assume unpivoting is accurate to the business requirements. Why are you worrying about performance so early in the design?

If it actually turns out that once you have a few billion rows this one table is at the core of a key performance criteria, there are a dozen or more techniques for addressing that only one of which is the denormalization you inquire about. There is absolutely no possible way that you can determine at this early stage which of those performance enhancing techniques will be an appropriate solution. Meanwhile, if you continue to denormalize your design in this way you will vastly complicate the writing, reading, testing, and development time of the codebase using your application. Are you sure that cost is worthwhile for a very, very, early guess at performance needs?

3
  • The system is already well developed, I do not understand the assumption otherwise? The issue is that as this system has collected data for years (Industrial performance data), and databases have grown into the GB sizes, I am looking for ways to improve efficiency of a functioning system through items such as this that would improve query performance without altering the code that consumed the output of those queries.
    – Sabre
    Oct 6, 2014 at 14:41
  • 1
    @Sabre: Then show your query plan demonstrating that there is a problem with this query worth spending time on. Oct 6, 2014 at 21:28
  • edited OP per request.
    – Sabre
    Oct 7, 2014 at 14:16
2

Perhaps creating some denormalized data in advance would speed up the process. That, where possible, could reduce the number of rows involved in your ultimate query.

This could mean a persistent table that you maintain. But it could also be implemented as a temp table (e.g. #criteria) that could then be joined more simply to get your final results.

There is nothing special about those; it just reduces the complexity of the queries, giving the optimizer easier job steps to work on.

Of course, if you are still suffering with the performance, you would need to analyze whether this is useful in your case.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.