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

RBAR

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.

So, while the code for RBAR is often easier to write, even though it might be physically longer, it’s probably going to be slower too.

Edit: On twitter today I had it pointed out to me that my cursor may not be the fastest it could be. And in specific I was shown this post by Aaron Bertrand (b/t) that says a FAST_FORWARD cursor might very well be faster than what I had. (It didn’t test against KEYSET but even so.)

So in the interest of fairness to cursors (and honestly I don’t object to them when you actually need them) I ran some additional tests. The following are average times (I ran each test 10 times):

Batch: 48.7ms
KEYSET Cursor: 558.6ms
FAST_FORWARD Cursor: 595.4ms

So batch is still considerably faster (by 10+x) and KEYSET was actually faster (marginally) than FAST_FORWARD. At least in my test.

Filed under: Microsoft SQL Server, Performance, SQLServerPedia Syndication, T-SQL Tagged: microsoft sql server, Performance, T-SQL