Join on Large Table

Fewer stories and more code will be more efficient in my opinion :-)

Please post DDL+DML

Here's the code. I have listed the entire stored procedure for context but the key thing is the SELECT query.

The NumericSamples table will eventually have millions of rows in it and its primary key is TagID, SampleDateTime in that order. The view vw_GroupTags has only a few hundred rows and is static in size.

Ignore the padding of extra rows after the SELECT statement. This is just so that a number of datasets displayed side by side in SSRS have the same number of rows.

I will look at the CROSS JOIN option as suggested although performance is not an issue at the moment I am just trying to make sure that it doesn't become a problem in the future (in a few years time).

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
        
        print '@PadRows: ' + convert(varchar(5), @PadRows)
        print '@RowsReturned: ' + convert(varchar(5), @i)
        
        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