RBAR vs Batch

RBAR
RBAR
Many years ago Jeff Moden (of SQL Server Central fame) came up with the concept of RBAR. Row-By-Agonizing-Row. At it’s most basic it means you are inserting one row at a time. A more broad interpretation says it’s any type of loop even the type caused by a recursive CTE.

And the point? Loops are slower than batches.

Simple example:

Batch
USE AdventureWorks2014;
GO
SELECT TOP 0 * INTO vSWA FROM Sales.vStoreWithAddresses;
GO
DECLARE @StartTime datetime = getdate()
INSERT INTO vSWA
SELECT * FROM Sales.vStoreWithAddresses;
SELECT DATEDIFF(millisecond,@StartTime,getdate())
GO
DROP TABLE vSWA;
GO
-- 23 milliseconds
REBAR
USE AdventureWorks2014;
GO
SELECT TOP 0 * INTO vSWA FROM Sales.vStoreWithAddresses;
GO
 
DECLARE curSWA CURSOR KEYSET FOR
SELECT * FROM Sales.vStoreWithAddresses;
 
DECLARE @BusinessEntityID int
        ,@Name Name
        ,@AddressType Name
        ,@AddressLine1 nvarchar(60)
        ,@AddressLine2 nvarchar(60)
        ,@City nvarchar(30)
        ,@StateProvinceName Name
        ,@PostalCode nvarchar(15)
        ,@CountryRegionName Name;
 
OPEN curSWA
 
DECLARE @StartTime datetime = getdate();
FETCH NEXT FROM curSWA INTO @BusinessEntityID, @Name, @AddressType, @AddressLine1,
                    @AddressLine2, @City, @StateProvinceName, @PostalCode,
                    @CountryRegionName;
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        INSERT INTO vSWA (BusinessEntityID, Name, AddressType, AddressLine1,
                        AddressLine2, City, StateProvinceName, PostalCode,
                        CountryRegionName)
            VALUES (@BusinessEntityID, @Name, @AddressType, @AddressLine1,
                    @AddressLine2, @City, @StateProvinceName, @PostalCode,
                    @CountryRegionName)
    END
    FETCH NEXT FROM curSWA INTO @BusinessEntityID, @Name, @AddressType, @AddressLine1,
                    @AddressLine2, @City, @StateProvinceName, @PostalCode,
                    @CountryRegionName;
END
SELECT DATEDIFF(millisecond,@StartTime,getdate());
 
CLOSE curSWA;
DEALLOCATE curSWA;
GO
 
DROP TABLE vSWA;
GO
--850

So 23 milliseconds for the batch version and 850 milliseconds for RBAR. What a difference.

Now in this case the code for the RBAR is also a lot more complicated. But that isn’t always the case. It also isn’t always the case that RBAR is slower. But it’s almost always a lot slower than batch.

Continue reading on SQLStudies.com.

54321
(0 votes. Average 0 of 5)
Leave a reply

Your email address will not be published. Required fields are marked *