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.
Best Answer
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...