Join on Large Table

Just in case anyone wants to see the full picture (which I am not actually expecting) this is a script for all SQL objects involved.

The numbers of rows in the tables are.

Tags 5,000

NumericSamples millions (over time)

TagGroups 50

GroupTags 500

CREATE TABLE [dbo].[Tags](
        [ID] [int] NOT NULL,
        [TagName] [nvarchar](110) NOT NULL,
        [Address] [nvarchar](80) NULL,
        [DataTypeID] [smallint] NOT NULL,
        [DatasourceID] [smallint] NOT NULL,
        [Location] [nvarchar](4000) NULL,
        [Properties] [nvarchar](4000) NULL,
        [LastReadSampleTime] [bigint] NOT NULL,
        [Archived] [bit] NOT NULL,
 CONSTRAINT [Tags_ID_PK] PRIMARY KEY CLUSTERED 
(
        [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Tags]  WITH NOCHECK ADD  CONSTRAINT [Tags_DatasourceID_Datasources_ID_FK] FOREIGN KEY([DatasourceID])
REFERENCES [dbo].[Datasources] ([ID])
GO

ALTER TABLE [dbo].[Tags] CHECK CONSTRAINT [Tags_DatasourceID_Datasources_ID_FK]
GO

ALTER TABLE [dbo].[Tags]  WITH NOCHECK ADD  CONSTRAINT [Tags_DataTypeID_DataTypes_ID_FK] FOREIGN KEY([DataTypeID])
REFERENCES [dbo].[DataTypes] ([ID])
GO

ALTER TABLE [dbo].[Tags] CHECK CONSTRAINT [Tags_DataTypeID_DataTypes_ID_FK]
GO

ALTER TABLE [dbo].[Tags] ADD  CONSTRAINT [DF_Tags_LastReadSampleTime]  DEFAULT ((552877956000000000.)) FOR [LastReadSampleTime]
GO

ALTER TABLE [dbo].[Tags] ADD  DEFAULT ((0)) FOR [Archived]
GO



CREATE TABLE [dbo].[NumericSamples](
        [TagID] [int] NOT NULL,
        [SampleDateTime] [bigint] NOT NULL,
        [SampleValue] [float] NULL,
        [QualityID] [smallint] NOT NULL,
 CONSTRAINT [NumericSamples_TagIDSampleDateTime_PK] PRIMARY KEY CLUSTERED 
(
        [TagID] ASC,
        [SampleDateTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[NumericSamples]  WITH NOCHECK ADD  CONSTRAINT [NumericSamples_QualityID_Qualities_ID_FK] FOREIGN KEY([QualityID])
REFERENCES [dbo].[Qualities] ([ID])
GO

ALTER TABLE [dbo].[NumericSamples] CHECK CONSTRAINT [NumericSamples_QualityID_Qualities_ID_FK]
GO

ALTER TABLE [dbo].[NumericSamples]  WITH NOCHECK ADD  CONSTRAINT [NumericSamples_TagID_Tags_ID_FK] FOREIGN KEY([TagID])
REFERENCES [dbo].[Tags] ([ID])
GO

ALTER TABLE [dbo].[NumericSamples] CHECK CONSTRAINT [NumericSamples_TagID_Tags_ID_FK]
GO



CREATE TABLE [dbo].[TagGroups](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [TagGroup] [varchar](50) NULL,
        [Aggregates] [varchar](250) NULL,
        [NumericData] [bit] NULL,
 CONSTRAINT [PK_TagGroups] PRIMARY KEY CLUSTERED 
(
        [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TagGroups] ADD  CONSTRAINT [DF_Tag_Groups_Aggregates]  DEFAULT ('First') FOR [Aggregates]
GO

ALTER TABLE [dbo].[TagGroups] ADD  CONSTRAINT [DF_TagGroups_NumericData]  DEFAULT ((1)) FOR [NumericData]
GO



CREATE TABLE [dbo].[GroupTags](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [TagGroupID] [int] NULL,
        [TagName] [varchar](150) NULL,
        [ColumnName] [varchar](50) NULL,
        [SortOrder] [int] NULL,
        [TotalFactor] [float] NULL,
 CONSTRAINT [PK_GroupTags] PRIMARY KEY CLUSTERED 
(
        [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[GroupTags]  WITH CHECK ADD  CONSTRAINT [FK_GroupTags_TagGroups] FOREIGN KEY([TagGroupID])
REFERENCES [dbo].[TagGroups] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[GroupTags] CHECK CONSTRAINT [FK_GroupTags_TagGroups]
GO

ALTER TABLE [dbo].[GroupTags] ADD  CONSTRAINT [DF_GroupTags_TotalFactor]  DEFAULT ((1)) FOR [TotalFactor]
GO


CREATE VIEW [dbo].[vw_GroupTags]
AS
SELECT     TOP (10000) dbo.TagGroups.TagGroup AS TableName, dbo.TagGroups.Aggregates AS SortOrder, dbo.GroupTags.SortOrder AS TagIndex, dbo.GroupTags.TagName, 
                      dbo.Tags.ID AS TagId, dbo.TagGroups.NumericData, dbo.GroupTags.TotalFactor, dbo.GroupTags.ColumnName
FROM         dbo.TagGroups INNER JOIN
                      dbo.GroupTags ON dbo.TagGroups.ID = dbo.GroupTags.TagGroupID INNER JOIN
                      dbo.Tags ON dbo.GroupTags.TagName = dbo.Tags.TagName
ORDER BY SortOrder, TagIndex


CREATE procedure [dbo].[GetTagTableValues]
(
        @SampleDateTime bigint,
        @TableName varchar(50),
        @PadRows int = 0
)
as

BEGIN
        DECLARE @i int
        DECLARE @ResultSet table(TagName varchar(150), SampleValue float, ColumnName varchar(50), SortOrder int, TagIndex int)
        set @i = 0
        
        INSERT INTO @ResultSet
        SELECT vw_GroupTags.TagName, NumericSamples.SampleValue, vw_GroupTags.ColumnName, vw_GroupTags.SortOrder, vw_GroupTags.TagIndex
        FROM vw_GroupTags INNER JOIN NumericSamples ON vw_GroupTags.TagId = NumericSamples.TagID
        WHERE (vw_GroupTags.TableName = @TableName) AND (NumericSamples.SampleDateTime = @SampleDateTime)
        
        set @i = @@ROWCOUNT
        
        if @i < @PadRows
                BEGIN
                        WHILE @i < @PadRows
                                BEGIN
                                        INSERT @ResultSet (TagName, SampleValue, ColumnName, SortOrder, TagIndex) VALUES ('', NULL, '', 0, 0)
                                        set @i = @i + 1
                                END             
                END
                
                select TagName, SampleValue, ColumnName, SortOrder, TagIndex
                from @ResultSet

END

---

R Campbell