I'm talking with a coworker about a way to approach a problem.
We have a table that keeps track of a filling process, that has unique index columns (or should at least) on columns
Recently, we added logic so that when a
TestGrade of anything higher than 1 is created, all the lower levels. Some logic is done so the lower level rows are not necessarily identical to the top level.
For example, I INSERT something with
TestGrade=5 then in my
Fills table I see
id Lot TestGrade OtherColumns ====================================== 10 'ABCD' 1 blah3 9 'ABCD' 2 bar 8 'ABCD' 3 foo 7 'ABCD' 4 blah1 6 'ABCD' 5 blah
Now the issue is in many instances, I only want the top level records of of the Fills table, so in the example, I would only want the record with
We have two ideas on how to do this. I want to make a view that looks like
CREATE VIEW [dbo].[vwFills] AS SELECT t.* FROM [dbo].[Fills] t JOIN (SELECT [Lot], MAX(TestGrade) as TestGrade FROM dbo.Fills GROUP BY [Lot]) t2 ON t.[Lot] = t2.[Lot] AND t.[TestGrade] = t2.[TestGrade]
My coworker wants to do something similar but inside a function that will look like this
ALTER FUNCTION [dbo].[fnFills] ( @Fills tyFills READONLY ) RETURNS @returnTable TABLE( //Copy of the table definition of Fills, without id INT NOT NULL, Lot VARCHAR(10) NOT NULL, TestGrade INT NOT NULL, //rest of the columns.. ) AS Begin insert into @returnTable select t2.* from ( select [Lot], max([TestGrade]) as TestGrade from @Fills) t1 left join Fills t2 on t1.[Lot] = t2.[Lot] and t1.[TestGrade] = t2.[TestGrade] Return End
Then to call this function, it would look like
DECALARE @FillRecords tyFills; INSERT INTO @FillRecords SELECT * FROM db.Fills; SELECT * FROM dbo.fnFills(@FillRecords);
dbo.Fills does have indexes on
TestGrade. However, it's my intuition that with the function approach where we insert into a variable and then feed it to a function, we lose these indexes and so with this join we're looking at O(n^2) performance. Is that a fair assessment? I'm concerned about performance as the the server is not robust hardware wise, and so anything to help with query execution is preferred.
Generally speaking, in this sort of scenario, is there a best practice method - is a view or a function the "obvious" or better choice?