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
1 2 3 4 5 6 7 8 9 10 11 12 |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
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.