Handling Cursor-Friendly Problems in T-SQL: Running Totals Example

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

Continues…

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |