Is this a RBAR?

Question

Is this a RBAR?

SSC Enthusiast

Points: 157

March 20, 2021 at 4:04 am

DECLARE @forms TABLE ([Id] int NOT NULL);
INSERT INTO @forms ([Id]) VALUES
(2481916),
(2481917),
(2481918),
(2481919),
(2481920),
(2481921),
(2481922),
(2481923),
(2482093),
(2482094),
(2482095),
(2482096),
(2482097),
(2482098),
(2482099),
(2482100)

DECLARE @versions TABLE ([FormVersion] int NOT NULL, [FormId] int NOT NULL, [CreatedDateTime] datetime);
INSERT INTO @versions ([FormVersion], [FormId], [CreatedDateTime]) VALUES
(1, 2481916, '2021-01-01'),
(2, 2481916, '2021-01-02'),
(1, 2481917, '2021-01-01'),
(1, 2481918, '2021-01-01'),
(1, 2481919, '2021-01-01'),
(1, 2481920, '2021-01-01'),
(1, 2481921, '2021-01-01'),
(1, 2481922, '2021-01-01'),
(1, 2481923, '2021-01-01'),
(1, 2482093, '2021-01-01'),
(1, 2482094, '2021-01-01'),
(1, 2482095, '2021-01-01'),
(1, 2482096, '2021-01-01'),
(1, 2482097, '2021-01-01'),
(1, 2482098, '2021-01-01'),
(1, 2482099, '2021-01-01'),
(1, 2482100, '2021-01-01')

SELECT DISTINCT f.[Id] AS [FormId], ver.[FormVersion]
FROM @forms f
OUTER APPLY (SELECT TOP 1 [FormVersion] FROM @versions WHERE [FormId] = f.[Id] ORDER BY [CreatedDateTime] DESC) ver

Is this a RBAR? how could I improve it?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16134 More actions · Answer 1

not a rowbar as such - but it gives a nested loop operator.

but as your code is not the real one why don't you post what you really have - the code above runs in a few milliseconds so has nothing to improve.

so do give us table DDL (including indexes), and an actual explain plan so we can see what your real code is doing and if/how it can be improved.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

I agree. Nested loops are not considered RBAR. There are alternative ways to code for getting only the latest row, but the best thing you can do for performance is to create an index on the versions table, on [FormID] and [CreatedDateTime] DESC, adding [FormVersion] as an included column.

IIf you are interested, I wrote up a comparison of performance of three techniques a long time ago. They perform differently at different volumes, but the use of a proper index made the most difference. See:

https://www.sqlservercentral.com/articles/t-sql-why-it-depends

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

ScottPletcher SSC Guru Points: 101042 More actions · Answer 3

The Windowing functions tend to be extraordinarily efficient, so I'd do the request this way:

SELECT DISTINCT f.[Id] AS [FormId], ver.[FormVersion]
FROM @forms f
LEFT OUTER JOIN (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY [FormId] ORDER BY [CreatedDateTime] DESC) AS row_num
    FROM @versions
) AS ver ON ver.[FormId] = f.[Id] AND ver.row_num = 1

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".