By design, the SQL programming language works with data sets, so it is not surprising that for most problems, row by row processing is not nearly as efficient as data set processing. There is a class of problems, however, that is much easier to solve using cursors. A typical “cursor friendly” problem is one where the data set returned contains at least one column whose value depends on column values from one or more previous rows of the same row set. Even when a data set based solution exists, it is hard to build a query that is more efficient than a cursor based solution. Usually, advanced coding techniques have to be used, and sometimes a special index design is required for the tables involved. For that type of problem, it is usually better to send a simple row set to the calling application and let the client cycle through the rows and calculate the column value derived.
The Problem
Let’s define the problem as displaying cumulative sales for a specific store and product. This is a simplified example since I haven’t included the sales period, which would be more realistic. I don’t want to include a discussion about handling time periods as a part of the criteria in Transact-SQL. That topic deserves a separate article.
Let’s say there is a sales table with the structure below:
CREATE TABLE [dbo].[Sales] (
[TransactionID] [int] IDENTITY (1, 1) NOT NULL,
[StoreID] [int] NOT NULL,
[productID] [int] NOT NULL,
[transactionTime] [datetime] NOT NULL,
[amount] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Sales] WITH NOCHECK ADD
CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED
(
[TransactionID]
) ON [PRIMARY]
GO
To populate the table you can use this script:
set noCount on
declare @i int
set @i = 0
while @i < 100000 begin
insert into dbo.Sales(StoreID, productId, transactionTime, amount)
select 1,
p.productID,
dateAdd(minute, – @i, getDate()),
case (p.ProductID)
when 1 then 10
when 2 then 100
end
from (select 1 as productID union all select 2 as productID) as p
if @i % 1000 = 0 print @i
set @i = @i+1
end
set nocount off
go
set noCount on
declare @i smallint
set @i = 0
while @i < 1000 begin
insert into dbo.Sales(StoreID, productId, transactionTime, amount)
select 2,
p.productID,
dateAdd(minute, – @i, getDate()),
case (p.ProductID)
when 1 then 10
when 2 then 100
end
from (select 1 as productID union all select 2 as productID) as p
set @i = @i+1
end
set nocount off
go
Finally, we need an index on StoreID and ProductID:
CREATE INDEX [IX_Sales] ON [dbo].[Sales]([StoreID], [productID], [transactionID]) ON [PRIMARY] GO
We want the script below:
execute dbo.Sales_sel_by_StoreID_ProductID @storeID = 2, @productID = 1
To output this row set:
|
transactionID |
transactionTime |
amount |
total |
|---|---|---|---|
|
200001 |
29.12.2005 17:18 |
10.0000 |
10.0000 |
|
200003 |
29.12.2005 17:19 |
10.0000 |
20.0000 |
|
200005 |
29.12.2005 17:20 |
10.0000 |
30.0000 |
|
200007 |
29.12.2005 17:21 |
10.0000 |
40.0000 |
|
200009 |
29.12.2005 17:22 |
10.0000 |
50.0000 |
|
200011 |
29.12.2005 17:23 |
10.0000 |
60.0000 |
|
200013 |
29.12.2005 17:24 |
10.0000 |
70.0000 |
|
200015 |
29.12.2005 17:25 |
10.0000 |
80.0000 |
|
200017 |
29.12.2005 17:26 |
10.0000 |
90.0000 |
|
200019 |
29.12.2005 17:27 |
10.0000 |
100.0000 |
|
… |
… |
… |
… |